Help needed for running batch report

[Freddie81]

Hi Guys,



Could somebody please help me with the vba code to do the following:

I have a form that is used to select criteria for running a report. On the form we have:
Submit command button
1x combo box named cboList

In the combo box, the first option is "All Customers" This is the one I need help with

So if a user selects "All Customers" and click submit I need the same report to run for each and every customer that is active.

My thoughts are that there should be a way to count to all the customers in the table and use this total in a loop. This loop will then check every customerID to see if the IsActive field is true if it is then it should run the report for that customer and send it to the printer. ( So, in theory, it would print for example 20 reports if there were 20 customers active in the table.)

thats the theory part....doing this I hav no idea how to put it into code - sigh

[CJ_London]

basic principle is to create a recordset of the required customers then loop through the recordset - something like

dim rst as dao.recordset
dim db as dao.database

set db=currentdb
set rst=db.openrecordset("SELECT customerID from tblCustomers WHERE isActive")
while not rst.eof
     docmd.openreport "reportname",  , "customerID=" & rst!customerID
     rst.movenext

wend
set rst=nothing
set db=nothing

[Freddie81]

Hi Ajax, thank you very much for the code, I appreciate your help!

I have copied the code to my event and changed the required fields. When it runs it creates 4x pdf files which are correct(there are 4 active customers) but it is the same report 4 times. It doesn't for some reason move on to the next customer.

[CJ_London]

without know what code you have actually used, I can't suggest anything

[Freddie81]

without know what code you have actually used, I can't suggest anything

Sorry Ajax, my bad...Her is the code I have got

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

If Me.cboList.Column(0) = 1 Then
DoCmd.OpenReport "StatementsReport", acViewReport
DoCmd.Close acForm, "StatementsReportF", acSaveYes

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT customerID from CustomersT WHERE ActiveCustomer")
While Not rst.EOF
DoCmd.OpenReport "StatementsReport", , "[customerID]=" & rst!CustomerID
rst.MoveNext
Wend
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

[Freddie81]

Sorry, saw a problem so here is the correct code....

Private Sub CmdClose_Click()
DoCmd.Close acForm, "StatementsReportF", acSaveYes
End Sub


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

If Me.cboList.Column(0) = 1 Then
'DoCmd.OpenReport "StatementsReport", acViewReport

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT customerID from CustomersT WHERE ActiveCustomer")
While Not rst.EOF
DoCmd.OpenReport "StatementsReport", , "[customerID]=" & rst!CustomerID
rst.MoveNext
Wend
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

[isladogs]

The WHERE part of your rst line is incorrect.
You probably mean WHERE CustomerID =" & me.ActiveCustomer

BUT if you only want one report, you don't need the recordset code at all.
If so, scrap that and change the report code to
DoCmd.OpenReport "StatementsReport", , "CustomerID=" & Me.ActiveCustomer

[CJ_London]

You probably mean WHERE CustomerID =" & me.ActiveCustomer

I assumed isActive, now changed to activecustomer is a Boolean, - so could say

isActive=True

but will return the same value

[Freddie81]

I assumed isActive, now changed to activecustomer is a Boolean, - so could say

isActive=True

but will return the same value

Yes, I have changed IsActive to ActiveCustomer and it is Boolean indeed. That is how I was running the code and got the problem where it is not moving on to the next customer.

[Freddie81]

The WHERE part of your rst line is incorrect.
You probably mean WHERE CustomerID =" & me.ActiveCustomer

BUT if you only want one report, you don't need the recordset code at all.
If so, scrap that and change the report code to
DoCmd.OpenReport "StatementsReport", , "CustomerID=" & Me.ActiveCustomer

Hi Ridders, I actually want the same report to run for every active customer. So if there are 4 active customers I want 4 reports, each with the details of that customer.

Just a question...in your WHERE statement, is it possible to compare a number value to a Boolean?? CustomerID is numeric and ActiveCustomer is True or False....

[Freddie81]

Okay so I tested a bit and found this:

rst!CustomerId works fine, it changes values as it should. Used a msg box to display it after every open report.
CustomerID in the first part of the condition in open report stays empty...no vales at all.....so it looks like it is trying to match blank to a value......

[Freddie81]

I played around with the code a bit and got it to print the 4 reports, but it only prints the first one with details, the other 3 is empty.

Private 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", acViewReport, , "[CustomerID]=" & rst!CustomerID, acHidden
DoCmd.SelectObject acReport, "StatementsReport"
DoCmd.RunCommand acCmdPrint
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

[pbaldy]

Within your loop you need to close the report after printing, otherwise it remains open with the first customer. You can print with just the OpenReport line by deleting "acViewReport", as printing is the default.

[CJ_London]

think you need to close the report then open it again - and please learn to use the code tags (the # button) to preserve indentation

While Not rst.EOF
     on error resume next 'in case report is not open
     DoCmd.Close acReport, "StatementsReport"
     on error goto 0 'to reinstate error checking
     DoCmd.OpenReport "StatementsReport", acViewReport, , "[CustomerID]=" & rst!CustomerID, acHidden
     DoCmd.SelectObject acReport, "StatementsReport"
     DoCmd.RunCommand acCmdPrint
     rst.MoveNext
Wend

[isladogs]

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