Multiple .SNP reports using table data

[ChrisWest99]

Hi,

I'm trying to do the following:
Append a table with the data needed for a report
Run a report based on the first row of data in the table
Print that report to a file
Go to the next row of data
Run the report, print the report, and so on until the end of table is reached
Then give user a popup message
Cleanup and end.

Currently, I have an Append query that adds the data to a table, then I use a DoCmd.OutputTo command to start printing the report. When I did get it tp run partially, it created one report with the data of all 20 people in it. How do I get seperate reports for each person?

Thanks,
Chris

----- Code Starts -------
Private Sub btnBRptOK_Click()
Dim MyDB As Database
Dim MySet As Recordset
Dim strFile As String
Dim strName As String
Dim stDocName As String
Dim Msg, Style, Title, Response, MyString

On Error GoTo Err_btnBRptOK_Click
Application.SetOption "Confirm Action Queries", False

stDocName = "qryBatchRptData2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Msg = "Table Updated. Do you want to continue ?" ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "MsgBox Batch Report Table" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
'===========================================================
Set MyDB = CurrentDb()
Set MySet = MyDB.OpenRecordset("tbl_CLEBatchReports2&quot

DoCmd.Echo False
With MySet
.MoveSecond
Do While Not MySet.EOF
strName = "MySet.UserLName" & "MySet.UserFName" & "mmddyy"
strFile = "C:\ReportsTesting\" & "strName"

'Run report, name report, output report to file
DoCmd.OutputTo acOutputReport, "rptIndividualBatch2", acFormatSNP, "strFile", False

.MoveNext
Loop
End With
DoCmd.Echo True
'=====================================================
'Error handling
'Display complete message.
MsgBox vbCrLf & "The Reports have been exported", _
vbInformation + vbOKOnly, "Batch Finished"

DoCmd.Close acForm, "frmBatchReports2"
MySet.Close
Set MySet = Nothing
'===========================================================

Exit_btnBRptOK_Click:
Exit Sub

Err_btnBRptOK_Click:
MsgBox Err.Description
Application.SetOption "Confirm Action Queries", True
Resume Exit_btnBRptOK_Click

End Sub
------- Code Ends---------------

 

[kenm11]

this will require VBA including Loop statement. Here is a general overview of the process.
first create the recordsets, etc.

1. open recordsetname (query)
2. open recordsetname (table)
3. recordsetquery.MoveFirst
4. add new record to recordsettable
5. run the report
6. run a delete command to delete the record from recordsettable
7. recordsetquery.MoveNext
8. loop back to #4 to repeat process

KenM11

 

[ChrisWest99]

Thanks. I'll rework the code and see how it goes.

Chris