Re: Using For...Each loops when referencing reports.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Right, I solved the problem in a round about kind of a way. I discovered that
the problem was where I was trying to refer to the "Transmit Site" field, but
that if I tried any other field it worked fine. So I did a few debug.prints
to find out the numerical reference to the transmit site field, and just used
that instead. Thanks for your help anyway,

Sam

"RoyVidar" wrote:

Sam Hayler wrote in message
<39BB057F-D4C3-4825-8A24-77BD571E8F04@xxxxxxxxxxxxx> :
Still just prints the string in the immediate window:

SELECT tblTx.*, tblTestTx.* FROM tblTx INNER JOIN tblTestTx ON
tblTx.[Transmit Site] = tblTestTx.[Transmit Site] WHERE
tblTestTx.TestID=210

I'm beginning to be convinced that I've done something really stupid
like spelt something wrong, but every time I check, or change
something just to be on the safe side, I get an "Item not found in
this collection" error. I even tried taking out the tblTx.* syntax
and listing every field that I need to use, but still no luck...

Thanks,

Sam

"Stuart At Work" wrote:

Sam,

You'll need to replace the line of code that sets the strSQL
variable to the following:

strSQL = "SELECT tblTx.*, " & _
"tblTestTx.* " & _
"FROM tblTx INNER JOIN " & _
"tblTestTx ON tblTx.[Transmit Site] =
tblTestTx.[Transmit Site] " & _
"WHERE tblTestTx.TestID=" & intTestID

That should get the code up to the same as the query.

Stuart

"Sam Hayler" wrote:

Right, it works when I create a query in SQL view fine, but I've
stuck it into my code and it still says this in the immediate
window:

SELECT tblTx.*, tblTestTx.* FROM tblTx INNER JOIN tblTestTx ON
tblTx.[Transmit Site] = tblTestTx.[Transmit Site] WHERE
tblTestTx.TestID=203

So I don't know. I'm going to go home and not think about it for
the weekend.

Thanks,

Sam

"Stuart At Work" wrote:

Looking at the SQL statement, I think you're trying to return the
records from tblTx and tblTestTx where [Transmit Site] is equal
in both tables. If this is the case then the SQL should be:

SELECT tblTx.*, tblTestTx.*
FROM tblTx INNER JOIN tblTestTx ON tblTx.[Transmit Site] =
tblTestTx.[Transmit Site]
WHERE tblTestTx.TestID=199;

Pop that into a query via design view and the SQL button and check
what it returns. n.b. 199 is just the ID in your last SQL
statement.

Cheers,

Stuart
"Sam Hayler" wrote:

Right, this is what came up in the immediate window:

SELECT * FROM tblTx, tblTestTx WHERE tblTx.[Transmit Site] =
tblTestTx.[Transmit Site] AND tblTestTx.TestID =199

So I'm kind of assuming that's not right. Also, the lack of join
is unintentional, I just don't really have much experince with
SQL. I've just been playing around trying to put one in, but I
still can't get it to work.

Thanks,

Sam

"Stuart At Work" wrote:

OK - let's have a look at the SQL and make sure that it is
valid. I always find the best way to do this is store it in a
variable, and then pass the variable itself to the
"OpenRecordset" method - it makes things a bit neater too. Add
the following line to the declarations section of your
procedure.

'[Declare a variable to store the SQL]
Dim strSQL As String

Add the following lines above your "OpenRecordset" line of code.

'[Build the SQL statement]
strSQL = "SELECT * " & _
"FROM tblTx, tblTestTx " & _
"WHERE tblTx.[Transmit Site] = tblTestTx.[Transmit
Site] AND " & _
"tblTestTx.TestID =" & intTestID
'[Print the SQL statement to the immediate window]
Debug.Print strSQL

This will now write the exact SQL statement used to open the
recordset to the immediate window. Copy the SQL statement from
there and go to the database window and create a new query.
Click the "Cancel" button when prompted to "Show Tables". In
the top-left corner of the query designer window, there should
be a button marked "SQL" - click here to go to the SQL view.
Paste the SQL statement you copied from the immediate window
here, and either click the Run button or change to Data***
view. If you see records your query is fine...

I should point out tho' that you don't have any join between the
two tables in your SQL statement. This could be intentional,
but may be an oversight - if it is an oversight you'll get
completely unexpected results!

Cheers,

Stuart



("SELECT * FROM tblTx, tblTestTx WHERE
tblTx.[Transmit Site] = tblTestTx.[Transmit Site] AND
tblTestTx.TestID =" & intTestID



"Sam Hayler" wrote:

Right, I've tried the whole text streaming with just my
"intrst" variable, and that works fine, so the problem is
something to do with how I'm using the recordset. Nothing is
being written to the immediate window. I'm guessing I've got
something wrong either in setting the recordset:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblTx,
tblTestTx WHERE tblTx.[Transmit Site] = tblTestTx.[Transmit
Site] AND tblTestTx.TestID =" & intTestID)

Or where I reference it in the writeline statements. Everything
is exactly how I've just copied and pasted it from your post.

Thanks,

Sam

"Stuart At Work" wrote:

Hi Sam,

Sounds like the problem is with your TextStream object you're
using WriteLine against. Just to make sure, it would be a
good idea to check you recordset first. If you overwrite the
code which forms your Do-Loop section, it will print the
values of the fields/records to the Immediate Window
(Pressing Ctrl-G will make it appear).

Do Until rst.EOF
Debug.Print "Transmit Site " & intrst & " : " &
rst.Fields("[Transmit Site]")
Debug.Print "X Coordinate Site " & intrst & " : " &
rst.Fields("[X Coordinate]")
Debug.Print "Y Coordinate Site " & intrst & " : " &
rst.Fields("[Y Coordinate]")
Debug.Print "Tx Antenna Height " & intrst & " : " &
rst.Fields("[Antenna Height (m)]")
Debug.Print "Tx Antenna " & intrst & " : " &
rst.Fields("[PLT File]") Debug.Print "Tx Antenns " &
intrst & " : " & rst.Fields("[Offset for PLT file]")
Debug.Print "ERP dBW (isotropic) " & intrst & " : " &
rst.Fields("[ERP dBW (isotropic)]")

' NewsFile.writeline ("Transmit Site " & intrst & ": "
& rst.Fields.[Transmit Site])
' NewsFile.writeline ("X Coordinate Site " & intrst &
": " & rst.Fields.[X Coordinate])
' NewsFile.writeline ("Y Coordinate Site " & intrst &
": " & rst.Fields.[Y Coordinate])
' NewsFile.writeline ("Tx Antenna Height " & intrst &
": " & rst.Fields.[Antenna Height (m)])
' NewsFile.writeline ("Tx Antenna " & intrst & " .PLT
File: " & rst.Fields.[PLT File])
' NewsFile.writeline ("Tx Antenns " & intrst & " offset
for .PLT file: " & rst.Fields.[Offset for PLT file])
' NewsFile.writeline ("ERP dBW (isotropic): " &
rst.Fields.[ERP dBW (isotropic)])
intrst = intrst + 1
rst.MoveNext
Loop

It should be easier once you know where the problem definitely
lies. If this works fine without error, then we need to look
at the TextStream object you're writing to.

Cheers,

Stuart


"Sam Hayler" wrote:

Thanks for your input, I've just got round to testing all
this, and my current code is this:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblTx,
tblTestTx WHERE tblTx.[Transmit Site] = tblTestTx.[Transmit
Site] AND tblTestTx.TestID =" & intTestID)
intrst = 1
Do Until rst.EOF
NewsFile.writeline ("Transmit Site " & intrst & ": " &
rst.Fields.[Transmit Site])
NewsFile.writeline ("X Coordinate Site " & intrst & ": "
& rst.Fields.[X Coordinate])
NewsFile.writeline ("Y Coordinate Site " & intrst & ": "
& rst.Fields.[Y Coordinate])
NewsFile.writeline ("Tx Antenna Height " & intrst & ": "
& rst.Fields.[Antenna Height (m)])
NewsFile.writeline ("Tx Antenna " & intrst & " .PLT File:
" & rst.Fields.[PLT File])
NewsFile.writeline ("Tx Antenns " & intrst & " offset for
.PLT file: " & rst.Fields.[Offset for PLT file])
NewsFile.writeline ("ERP dBW (isotropic): " &
rst.Fields.[ERP dBW (isotropic)])
intrst = intrst + 1
rst.MoveNext
Loop

However, this gives the errormessage "Operation not supported
for this type of object." Unfortunately, that's all it says,
and doesn't have so much as a debug button. So what have I
done wrong?

Sam

"Alex Dybenko" wrote:

Hi,
you need to open recordset, based on second table and then
loop through it:

set rst=currentdb.openrecordset("select * from Table2 Where
Table1Key=" & someKey)
do until rst.eof
'do something
rst.movenext
loop

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


"Sam Hayler" <SamHayler@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:3C1BF175-504D-4656-A91D-95A5320BC007@xxxxxxxxxxxxxxxx
I have a report, which essentially has one record, and I'm
writing the data to a text file within a specific format.
However, although it's only one record it has a one-to-many
relationship with records in another table, and
the report lists these. What I want is a for-each loop
which displays the data for each record in the second table
that relates to the record in the first. I hope this makes
sense. I'm just not sure of how to reference the records
within the loop. Here is basically what I'm looking for:

For Each {tbl2 Record} In {rpt1}
newsfile.writeline etc. etc.
next {tbl2 Record}

So I'm looking for what to write in the curly brackets
essentially. Any help
would be much-appreciated.

Ta!

Sam



Are you still referring to the fields with this syntax

rst.Fields.[Some Field Name With Spaces]

If so, try either with a bang

rst.Fields![Some Field Name With Spaces]

.

Quantcast