Print report for all customers with changed revenue

[tagteam]

I would like to loop through a list of customers that have revenue changed from last month. I have the query that gives me the name of the customers but I am not sure how to create the loop as I am not very good at programming. I think you do a loop through a recordset.



I was thinking of trying something like this


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("q_Invoicing_ATest") 'This is my query
With rs
Do While Not .EOF
DoCmd.OpenReport "BOMI_Report", acViewPreview, , "CustomerId = " & Me.CustomerID - not sure what to do with this part
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing

The problem is I am not sure if that where clause will work since it will just be a query and not coming from a form. I am kind of shooting in the dark here and any help would be appreciated.

[ranman256]

that should work, but you probably don't want to preview each report,
you can either print to printer:

DoCmd.OpenReport "BOMI_Report", , , "CustomerId = " Me.CustomerID

or print the report to a PDF:

vFile = "c:\temp\Bomi_cust" & Me.CustomerID & ".pdf

docmd.OutputTo acOutputReport ,"BOMI_Report",acformatPDF, vFile

(but you need a form for the report query to see the customer id )

[June7]

Need to reference recordset field for dynamic parameter, not form field/control.

DoCmd.OpenReport "BOMI_Report", , , "CustomerId = " & rs!CustomerID

[tagteam]

ok, here is what I have so far

Dim PreviousMonth As Double
PreviousMonth = Me.PreviousMonth
Dim CurrentMonth As Double
CurrentMonth = Me.CurrentMonth
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("A_Invoicinv_Print")
With rs
Do While Not .EOF

DoCmd.OpenReport "r_Invoicing_Header_Details", acViewReport, , "CustomerID = " & rs!CustomerIDNumber & " And BillingPeriod=" & PreviousMonth
DoCmd.OpenReport "r_Invoicing_Header_Details-FirstRunCompare", acViewReport, , "CustomerID = " & rs!CustomerIDNumber & " And BillingPeriod=" & CurrentMonth

.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub



Currently I am getting an error - Runtime error 3061 - Too few parameters. Expected 2 . If I manually enter the customerId and billing period in the report it does pull up correctly so somehow it is not getting the correct CustomerID and Billingperiod. When I debug and put my mouse over Previous month it is correct. However, when I put my mouse over rs!CustomerIDNumber i get rs!CustomerIDNumber: <object variable or With block variable not set>

[June7]

What is A_Invoicinv_Print - a query? What fields are retrieved?

If you want to provide db for analysis, follow instructions at bottom of my post.