Open report using parameter passed by another procedure (1 Viewer)

Swampmeister

New member
Local time
Today, 16:53
Joined
May 2, 2022
Messages
8
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.

Code:
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

Code:
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

I’m here to help
Staff member
Local time
Today, 09:53
Joined
Oct 29, 2018
Messages
21,358
Have you tried using the WhereCondition argument of the OpenReport method?
 

HiTechCoach

Well-known member
Local time
Today, 11:53
Joined
Mar 6, 2006
Messages
4,357
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

Banishment Pending
Local time
Today, 11:53
Joined
May 11, 2011
Messages
11,611
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

Well-known member
Local time
Today, 11:53
Joined
Mar 6, 2006
Messages
4,357
Great point plog. You will need to doing something other than preview each report.
 

Swampmeister

New member
Local time
Today, 16:53
Joined
May 2, 2022
Messages
8
I tried this but it went straight to the error handler

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

Gasman

Enthusiastic Amateur
Local time
Today, 16:53
Joined
Sep 21, 2011
Messages
14,044
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

New member
Local time
Today, 16:53
Joined
May 2, 2022
Messages
8
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.

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

End Sub
 

Swampmeister

New member
Local time
Today, 16:53
Joined
May 2, 2022
Messages
8
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.
 

Swampmeister

New member
Local time
Today, 16:53
Joined
May 2, 2022
Messages
8
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

Well-known member
Local time
Today, 11:53
Joined
Mar 6, 2006
Messages
4,357
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

New member
Local time
Today, 16:53
Joined
May 2, 2022
Messages
8
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.
 

HiTechCoach

Well-known member
Local time
Today, 11:53
Joined
Mar 6, 2006
Messages
4,357
Last edited:

Users who are viewing this thread

Top Bottom