Looping Through a RecordSet to Generate a report -- DAO / ADDO ??

[Robert Davis]

I would like to be able to create a recordset in MS Access 2000 that
allows a macro to run and create a report for each ID [Bill of Lading
Number] that appears in the recordset. So I thought that I would use a
scheduling program to start the database and then have an AutoExec
Macro start the process.

My issue is that I can not seem to get any results to generate a
report. I am somewhat confused as to what the difference is in DAO and
ADDO.

Here is the code that does not seem to be working.

Public Sub PrintReport()

Dim rs As Object ' When I tried as a Recordset or a ADDO.Recordset
I recieved a runtime error.
Dim SQLText As String

SQLText = "SELECT tblStopInfo.[Bill of Lading Number],
tblStopInfo.[Stop Number], " & _
"tblSCAC.[CARRIER SCAC], tblSCAC.[CARRIER NAME],
tblLoadInformation.[Trailer Number], " & _
"tblLoadInformation.[Equipment Type], tblLoadInformation.[Load
Number]," & _
"tblLoadInformation.[BILLS RECEIVED DATE], tblLoadInformation.[Bills
Recieved Time], " & _
"tblLoadInformation.[Equipment Length], tblLoadInformation.[IB -
OB]," & _
"tblStopInfo.[Consignee Name], tblStopInfo.[Consignee Name 2], " & _
"tblStopInfo.[Consignee Address], tblStopInfo.[Consignee Address 2],
" & _
"tblStopInfo.[Consignee City], tblStopInfo.[Consignee State], " & _
"tblStopInfo.[Consignee Zip], tblStopInfo.[Order Number],
tblStopInfo.Pieces," & _
"tblStopInfo.Weight, tblStopInfo.[Contact Name],
tblStopInfo.[Original Appt Date]," & _
"tblStopInfo.[Original Appt Time], tblStopInfo.[Contact Phone],
tblStopInfo.Comments, " & _
"tblLoadInformation.[Email Sent]" & _
"FROM tblSCAC INNER JOIN (tblLoadInformation INNER JOIN tblStopInfo
ON " & _
"tblLoadInformation.[Bill of Lading Number] = tblStopInfo.[Bill of
Lading Number]) " & _
"ON tblSCAC.ID = tblLoadInformation.[Carrier SCAC Code]" & _
"WHERE (((tblLoadInformation.[Email Sent]) Is Null))" & _
"ORDER BY tblStopInfo.[Bill of Lading Number], tblStopInfo.[Stop
Number];"
Set rs = CurrentDb.OpenRecordset(SQLText)
If Not rs.BOF Then
rs.MoveFirst

With rs
Do While Not rs.EOF

DoCmd.OpenReport "qryCreate_BOL_Report", acViewPreview

Loop

rs.MoveNext
End With
End If

End Sub

Please Help. Thanks in Advance.

[PC Datasheet]

You don't need the recordset at all for the report. If what you show in SQL
is a stored query, just set the recordsource of the report to the query and
open the report in preview mode. If the query is not stored, create the
query and save it. Also realize that your Where clause limits the records
returned to only those where there was no email sent. If emails were sent to
all carriers, you will get no records in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Robert Davis" <rl******@charter.net> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I would like to be able to create a recordset in MS Access 2000 that
allows a macro to run and create a report for each ID [Bill of Lading
Number] that appears in the recordset. So I thought that I would use a
scheduling program to start the database and then have an AutoExec
Macro start the process.

My issue is that I can not seem to get any results to generate a
report. I am somewhat confused as to what the difference is in DAO and
ADDO.

Here is the code that does not seem to be working.

Public Sub PrintReport()

Dim rs As Object ' When I tried as a Recordset or a ADDO.Recordset
I recieved a runtime error.
Dim SQLText As String

SQLText = "SELECT tblStopInfo.[Bill of Lading Number],
tblStopInfo.[Stop Number], " & _
"tblSCAC.[CARRIER SCAC], tblSCAC.[CARRIER NAME],
tblLoadInformation.[Trailer Number], " & _
"tblLoadInformation.[Equipment Type], tblLoadInformation.[Load
Number]," & _
"tblLoadInformation.[BILLS RECEIVED DATE], tblLoadInformation.[Bills
Recieved Time], " & _
"tblLoadInformation.[Equipment Length], tblLoadInformation.[IB -
OB]," & _
"tblStopInfo.[Consignee Name], tblStopInfo.[Consignee Name 2], " & _
"tblStopInfo.[Consignee Address], tblStopInfo.[Consignee Address 2],
" & _
"tblStopInfo.[Consignee City], tblStopInfo.[Consignee State], " & _
"tblStopInfo.[Consignee Zip], tblStopInfo.[Order Number],
tblStopInfo.Pieces," & _
"tblStopInfo.Weight, tblStopInfo.[Contact Name],
tblStopInfo.[Original Appt Date]," & _
"tblStopInfo.[Original Appt Time], tblStopInfo.[Contact Phone],
tblStopInfo.Comments, " & _
"tblLoadInformation.[Email Sent]" & _
"FROM tblSCAC INNER JOIN (tblLoadInformation INNER JOIN tblStopInfo
ON " & _
"tblLoadInformation.[Bill of Lading Number] = tblStopInfo.[Bill of
Lading Number]) " & _
"ON tblSCAC.ID = tblLoadInformation.[Carrier SCAC Code]" & _
"WHERE (((tblLoadInformation.[Email Sent]) Is Null))" & _
"ORDER BY tblStopInfo.[Bill of Lading Number], tblStopInfo.[Stop
Number];"
Set rs = CurrentDb.OpenRecordset(SQLText)
If Not rs.BOF Then
rs.MoveFirst

With rs
Do While Not rs.EOF

DoCmd.OpenReport "qryCreate_BOL_Report", acViewPreview

Loop

rs.MoveNext
End With
End If

End Sub

Please Help. Thanks in Advance.