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
resource@pcdatasheet.com www.pcdatasheet.com
"Robert Davis" <rl_davis@charter.net> wrote in message
news:1105476832.530037.48670@z14g2000cwz.googlegro ups.com...[color=blue]
> 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.
>[/color]
