I don't know if this can be done or not.
I have a report that is filtered by certain criteria.
One of the criteria is customer number. Instead creating a new report for
each customer can I program Access just to loop through the customer file
and set the criteria of the report dynamically? This is what I have so far.
Any help would be greatly appreciated.
Public CustomerID, Filename As Variant 'setting up necessary global variables
' Usage: PrintAllCustomers("Reportnamehere")
Public Sub PrintAllCustomers(ReportName As String)
Dim ADOCon As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Folder As String ' to hold directory information
'now lets ask directory you want to save
Folder = InputBox("Where do you want to save files?", "Destination Folder", Application.CurrentProject.Path)
'if canceled leave the sub without processing.
If Folder = "" Then Exit Sub
Set ADOCon = Application.CurrentProject.Connection 'get current connection
Set Rst = New ADODB.Recordset 'to query current customers
'open recordset and load all data we need into recordset
'I added "WHERE Print=True" to SQL statement which will
'only print the customers we want
Rst.Open "SELECT Business.BusinessName, ImportAddresses.Name, ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State, ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber FROM (Business INNER JOIN Zips ON Business.CustomerNumber=Zips.CustomerNumber) INNER JOIN ImportAddresses ON Zips.ZipCode=ImportAddresses.Zip GROUP BY Business.BusinessName, ImportAddresses.Name, ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State, ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber, ImportAddresses.Type HAVING (((ImportAddresses.Type)=Zips.Type)) ORDER BY Business.BusinessName, ImportAddresses.Name", ADOCon, adOpenForwardOnly
Do While Not Rst.EOF 'start sending all reports to files
CustomerID = Rst!CustomerNumber 'set necessary global variables before print to file
Filename = Folder & "\" & Rst!BusinessName & ".SNP" 'set folder and filename.
'send report to file
DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP, Filename, True
Rst.MoveNext 'next customer
Set Rst = Nothing
just an idea, I never tried this; but if you could set up a global variant variable and set it to customerid and use it in your report as filter. which I see in your code snippet you do it. is it not working?