One File Output per Access Recordset Record - Part1

[Hemi426]

Greetings all, I am what would be considered an annual VB code developer, In other words, I just dont do it enough to be any good at it. So, I am hoping that one of the experts out there can help me accomodate the Winds of Change.
For many moons I have produced a daily report for our local fire department that provided incident information for the previous days events. This was accomplished with an Access report (myReport) and a select query (myQuery). The select query pulls about 50 fields from a few SQL tables via ODBC. It normally has about 80 records for any given day. The select query is the record source for the report. This daily process is automated and generates one file (.RTF) that is about 80 pages long & it does not print to a printer. The powers that be have asked for a change with the following requirements.
#1 - Each incident must now be printed to its own file (80 one page files a day instead of one 80 page)
#2 - The output filename has to be formatted in a certain fashion (this will require one value from a field in the data)
These changes are to accomodate an automatic import to a new document management system.


I have looked around and come up with this code that I have leashed to a form load
------------------------
Private Sub Form_Load()
Dim db As DAO.Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("myQuery", dbOpenDynaset)
Do While Not rec.EOF
DoCmd.OutputTo acOutputReport, "myReport", acFormatRTF, "C:\Test\INC" & rec!Master_Incident_Number & ".rtf"
rec.MoveNext
Loop
------------------------
It seems to loop properly and even produces one file per record, My problem is that each output file contains the entire recordset (probably the contents of myQuery). I need it to only contain the current record the recordset pointer is pointing to. I dont know how to tell the report (myReport) to use the current recordset record instead of the myQuery record source
Thanks for any help :-)

[June7]

Need to apply filter to the report, open it, then output will use the open filtered report.

1. open a recordset that has field for the criteria to filter the report
Set rec = db.OpenRecordset("SELECT Master_Incident_Number FROM myQuery;", dbOpenDynaset)

2. in loop code reference the criteria from recordset to open report and output
DoCmd.OpenReport "reportname", , , "Master_Incident_Number=" & rec!Master_Incident_Number
DoCmd.OutputTo acOutputReport, , acFormatRTF, "C:\Test\INC" & rec!Master_Incident_Number & ".rtf"
DoCmd.Close acReport, "ReportName", acSaveNo
rec.MoveNext

[Hemi426]

Greetings, With the reference provided by June7, My new code looks like this

Private Sub Form_Load()
Dim db As DAO.Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("SELECT Master_Incident_Number FROM myQuery;", dbOpenDynaset)
Do While Not rec.EOF
DoCmd.OpenReport "myReport", , , "Master_Incident_Number=" & rec!Master_Incident_Number
DoCmd.OutputTo acOutputReport, , acFormatRTF, "C:\Test\INC" & rec!Master_Incident_Number & ".rtf"
DoCmd.Close acReport, "myReport", acSaveNo
rec.MoveNext
Loop

Result is Access blows up and must restart Access - I tried changing

DoCmd.OpenReport "myReport", , , "Master_Incident_Number=" & rec!Master_Incident_Number
to
DoCmd.OpenReport "myReport", , , "Master_Incident_Number" = rec!Master_Incident_Number

And it doesnt blow up anymore but it hangs on the next step and fires a Runtime error 2487 - Object Type Argument for the action is blank or method is invalid. Debug takes me to the line below

DoCmd.OutputTo acOutputReport, , acFormatRTF, "C:\Test\INC" & rec!Master_Incident_Number & ".rtf"

Thanks

[June7]

The first DoCmd syntax is correct. The = must be within quotes.

Sorry, need acViewPreview parameter, otherwise report goes straight to printer (default parameter).

I made assumption that Master_Incident_Number is a number value. If it is text then use apostrophe delimiters.

DoCmd.OpenReport "myReport", acViewPreview, , "Master_Incident_Number='" & rec!Master_Incident_Number & "'"

[Hemi426]

Oh Yeah!! That works very nicely!! I have some other requirements but will need to run with this for a little while. Thank you so much June7!!!