Open report using parameter passed by another procedure

[Swampmeister]

To start this process I have a procedure to generate a set of Customer IDs within a date range; this correctly retrieves the ones needed. I want it then to pass each Customer ID to another Test procedure to generate a report.

Private Sub cmdEmailRequested_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

On Error GoTo ErrorHandler

strSQL = "Select DISTINCT CustomerID From qryDebtorsStatement WHERE (ShipDate BETWEEN #" & [Forms]![frmSales]![Start] & "# AND #" & [Forms]![frmSales]![End] & "#)"

    Set rst = dbs.OpenRecordset(strSQL)
    
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        Do
            Call Test(rst!CustomerID)
            rst.MoveNext
        Loop Until rst.EOF
    Else
        MsgBox "None"
    End If
 
ErrorHandler:
    Resume Next
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
  
End Sub

The Test procedure needs to open a report for the current CustomerID

Private Sub Test(ByRef lngCustID As Long)
    'Use lngCustID as parameter for report's source qryStatement

End Sub

How can I do this? I've tried using lngCustID as a criterion for an SQL statement but it fails.

There are posts on the web to say you can't use a recordset parameter as a report's Record Source but there must be a way.

 

[theDBguy]

Have you tried using the WhereCondition argument of the OpenReport method?

 

[HiTechCoach]

Have you tried storing the rst!CustomerID in a TempVar?

TempVars are great for passing criteria to a query.


Do
TempVars.Add "CustomerID, rst!CustomerID
' open report here
rst.MoveNext
Loop Until rst.EOF
Else
MsgBox "None"
End If


In the query for the report use:

Where CustomerID = Tempvars!CustomerID

 

[plog]

The Test procedure needs to open a report for the current CustomerID

Then what? What is to be done with the report? If you use DoCmd.OpenReport to open a report inside Test() I am pretty sure you will not end up with an instance of the report for every ID, instead you would end up with just the report opened to the last ID you passed Test().

Big picture this--what is the ultimate aim of all this. What is the end goal?

 

[HiTechCoach]

Great point plog. You will need to doing something other than preview each report.

 

[Swampmeister]

I tried this but it went straight to the error handler

    DoCmd.OpenReport "rptDebtorStatement", acViewPreview, , "[CustomerID] = '" & lngCustID & "'"

 

[Gasman]

That will likely be because lngCustID is numeric (going by the naming, but you can never tell )
So no quotes needed around the variable.

Would help if you said what the error was as well?

 

[Swampmeister]

Thanks all, things to try.

The bigger picture is that the Statements report currently includes every customer. Their owings appear on individual pages and my client prints them all then sends individual ones by post. Some people now prefer emailed copies of their reports.

My first procedure selects customers who have requested emails. The intention is to loop through them, call another procedure to create an individual report for the first one and send it to an Outlook preview, then repeat for all others.

Private Sub Test(ByRef lngCustID As Long)
    'Correctly receives lngCustID
    'Want to use it as criterion for statement report's source

End Sub

 

[Swampmeister]

Thanks for latest reply which appeared as I was typing. Unfortunately I have to leave this for a few hours but will return after trying your suggestions.

 

[HiTechCoach]

I created this example that might help you get started. It creates a separate PDF for each customer.

Batch Printing and Save As PDF

 

[Swampmeister]

Thank you so much HiTechCoach, this is excellent and gives me everything I need for adapting to my database question. I can include code to send each report in turn to Outlook, picking up the recipient email address from the form.
I'd happily give you a star rating if there's a way of doing it!

 

[HiTechCoach]

It is very easy to add the code to send the PDF as an email attachment after the PDF is saved. I use the exact same code to send emails. I just left out that part to allow you to use whatever method you want to use to send the emails.

I generally send directly via a SMTP server because it is faster, no user interaction, and avoids the need to have an email client installed.

 

[Swampmeister]

Great thanks. I'll need to send emails via Outlook, and if you have the code available for that then even better. Really appreciate your help.

 

[Uncle Gizmo]

I'll need to send emails via Outlook

See Gina Whipp's website:-

Access Diva Send eMail to Multiple Recipients

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[HiTechCoach]

Great thanks. I'll need to send emails via Outlook, and if you have the code available for that then even better. Really appreciate your help.

I would create a function to send the email. Use parameters to pass all the requires info.

Here is the code

Access/VBA Tutorials - Outlook Send E-mail Without Security Warning (everythingaccess.com)