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
:
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
:
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
:
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
:
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 Datasheet
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
:
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
:
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
:
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
:
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
message
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
