V
vman92
' 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
Dim NumLoop As Integer
Dim sqlstr As String
NumLoop = 1
'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
'only print the customers we want
sqlstr = "SELECT * FROM Business WHERE CustomerNumber = '" &
NumLoop & "';"
Rst.Open sqlstr, ADOCon
DoCmd.SetWarnings WarningsOff
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.
'Rst.Filter = "CustomerNumber = " & CustomerID
'MsgBox CustomerNumber
'MsgBox BusinessName
'send report to file
DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP,
Filename, True
'DoCmd.OpenReport ReportName, acViewPreview, , CustomerID
Rst.MoveNext 'next customer
Loop
DoCmd.SetWarnings WarningsOn
Rst.Close
Set Rst = Nothing
End Sub
Public Sub PrintAllCustomers(ReportName As String)
Dim ADOCon As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Folder As String ' to hold directory information
Dim NumLoop As Integer
Dim sqlstr As String
NumLoop = 1
'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
'only print the customers we want
sqlstr = "SELECT * FROM Business WHERE CustomerNumber = '" &
NumLoop & "';"
Rst.Open sqlstr, ADOCon
DoCmd.SetWarnings WarningsOff
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.
'Rst.Filter = "CustomerNumber = " & CustomerID
'MsgBox CustomerNumber
'MsgBox BusinessName
'send report to file
DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP,
Filename, True
'DoCmd.OpenReport ReportName, acViewPreview, , CustomerID
Rst.MoveNext 'next customer
Loop
DoCmd.SetWarnings WarningsOn
Rst.Close
Set Rst = Nothing
End Sub