Help needed for running batch report

[Freddie81]

Okay I changed the code again to :

rivate Sub CmdSubmit_Click()
Dim rst As dao.Recordset
Dim db As dao.Database


If Me.cboList.Column(0) = 1 Then
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT customerID from CustomersT WHERE ActiveCustomer")
While Not rst.EOF
DoCmd.OpenReport "StatementsReport", , "[CustomerID]=" & rst!CustomerID
DoCmd.Close acReport, "StatementsReport", acSaveYes
rst.MoveNext
Wend
'DoCmd.Close acReport, "StatementsReport", acSaveYes
DoCmd.Close acForm, "StatementsReportF", acSaveYes
Set rst = Nothing
Set db = Nothing
ElseIf Me.cboList.Column(0) = 2 Then
DoCmd.OpenReport "StatementsReport", acViewReport, , "[CustomerID]=" & Me.cboSupplier.Column(0)
DoCmd.Close acForm, "StatementsReportF", acSaveYes


End If

End Sub

When I run it it prints the 4 reports for the ative customers, but all 4 are exactly the same ( tthe first customer)

[CJ_London]

@Colin - I made the assumption (wrongly) that the OP wants individual pdf's so he can email them to each customer. This based on an earlier thread which covers the same subject - https://www.accessforums.net/showthr...130#post410130 - see post #11. On rereading I see this is not the case, my bad. So I agree with you, you just need to set the where parameter in the openreport.

[Freddie81]

To my mind there's a lot of redundant code which can be removed.
Whether it will solve the other issue I can't say

Private Sub CmdSubmit_Click()

 If Me.cboList.Column(0) = 1 Then
DoCmd.OpenReport "StatementsReport", acViewPreview, , "ActiveCustomer=True"
ElseIf Me.cboList.Column(0) = 2 Then
DoCmd.OpenReport "StatementsReport", acViewPreview, , "[CustomerID]=" & Me.cboSupplier.Column(0)
End If

DoCmd.Close acForm, Me.Name
 
End Sub

Thnks Ridders, I tried your last code and it wants me to enter the value for ActiveCustomer as it is not a field on the Query or on the form.

[Freddie81]

@Colin - I made the assumption (wrongly) that the OP wants individual pdf's so he can email them to each customer. This based on an earlier thread which covers the same subject - https://www.accessforums.net/showthr...130#post410130 - see post #11. On rereading I see this is not the case, my bad. So I agree with you, you just need to set the where parameter in the openreport.

Ajax, actually you are correct, I want the reports to print individually so it can either be mailed via postal or emailed as pdf.

[CJ_London]

I want the reports to print individually

if you print 4 customers as Colin is suggesting, then you get 4 bits of paper to put in 4 different envelopes. So it only matters if you need 4 separate (pdf) files.

[Freddie81]

true, but it won't always be just 4 customers. As the customer base grows so will the qty reports............

Everything in your code works except it doesn't pull CustomerID through to the Query to populate the report.

[isladogs]

I used Active Customer =True as the filter because you gave that as a Boolean field in post 9 (and elsewhere!)

[Freddie81]

I used Active Customer =True as the filter because you gave that as a Boolean field in post 9 (and elsewhere!)

Then perhaps there is a mix-up....the ActiveCustomer field is in the table CustomersT. The Query don't use it or contain it and neither does the form used for selection.

My apology if there was any misunderstanding.

[CJ_London]

so include it, you don't have to include it on your report.

[isladogs]

Yes I agree. Add the field t your query, form and report SQL.
You don't need to display the field in your report.

[Freddie81]

Okay but before I do that I have a question......

If I use your open report condition will that print the report for every active customer in the table or just one? For example, In the table CustomersT there is unknown qty of active customers( has the Active customer field marked as true). So if I use your statement it seems to me like it will only do one report and then exit the procedure, it won't go through the table and check every customer for the true value? Basically option 2 but with activecustomer as condition instead of CustomerID

[CJ_London]

If I use your open report condition will that print the report for every active customer in the table or just one?

Freddie - Seriously? you have to ask that question after 146 posts? What do you think the criteria is?

[Freddie81]

I've tried so many different things today that I'm really lost here. I assumed it would have something to do with the CustomerID as it works in option2 for the individual report.

So to get the activecustomers condition to work I need to include it in the Query?

[CJ_London]

So to get the activecustomers condition to work I need to include it in the Query?

yes - just include the field, the openreport where parameter can the reference it

[Freddie81]

I redid the Query and aded activeCustomer field as suggested.

The code now looks like this:

Private Sub CmdSubmit_Click()

If Me.cboList.Column(0) = 1 ThenDoCmd.OpenReport "StatementsReport", acViewPreview, , "ActiveCustomer=True"
ElseIf Me.cboList.Column(0) = 2 ThenDoCmd.OpenReport "StatementsReport", acViewPreview, , "[CustomerID]=" & Me.cboSupplier.Column(0)
End If

DoCmd.Close acForm, Me.Name

End Sub


When I run it, I get ONE report with two custmers transactions on it. Invoice 15 is for a different customer. See screnshot below.