troubleshooting Question

Filtering Access Report and Sending via Email

Avatar of JJENSEN3
JJENSEN3 asked on
Programming Languages-OtherMicrosoft AccessVisual Basic Classic
9 Comments2 Solutions2262 ViewsLast Modified:
I have a report that I created that is customer specific. I have a table with each record having the customer id and fields with additional information (order #, purchase order, order date, etc...)
I want to select the table and then filter, record by record, and email the report to the customer with the report being in the body of the email. (we use Outlook)
I have created the following code, however I am getting a run-time error of 3021 - No Current Record
I am not sure why. There are 52 records in the recordset:

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Order Confirmation", dbOpenTable)

While Not rs.EOF

    Reports(0).Filter = "[customer_id] = " & rs!customer_id
    Reports(0).FilterOn = True

    DoCmd.SendObject acSendReport, "Order Confirmation Message", acFormatPDF, _
        "username@domain.com", , , "Daily Report", "Here is your Daily Report", False

    rs.MoveNext
Wend

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros