Creating PDF for each page of a report?

[g4tv4life]

Hey everyone, I have a access database that generates PDF form letters (or at least should if I wasn't crap at coding)
The code is supposed to take the query, and loop through creating a pdf of each entry and saves them by the officer name and date of the incident.here's the code I have so far:

Private Sub Command75_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
 
strRptName = "Report"
strSQL = "Select [Main Table].[ID] From [Police Departments Query];"

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

With MyRS

Do While Not MyRS.EOF
   DoCmd.OpenReport strRptName, acViewPreview, , "[ID]=" & ![ID]
   DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, [Application].[CurrentProject].[path] & "\Aed letters\" & "Aed Letter " & [Officer Name] & " " & Format([Date of Incident], "MMMM dd, yyyy") & ".pdf"
   DoCmd.Close acReport, strRptName, acSaveNo
   .MoveNext
Loop

End With
MyRS.Close
Set MyRS = Nothing
End Sub

Now it loops through, however it doesn't seem to change the query so it just creates the same pdf
Any suggestions on how to make this work or links to working code? thanks!

[pbaldy]

I don't see anything in the path that would change for each record in the loop. If it was supposed to be [Officer Name], it would have to be ![Officer Name].

[June7]

NOTE: Use / instead of \ in CODE tag.

Where are [Officer Name] and [Date of Incident] coming from? They won't come from the report RecordSource.

Are you sure it's the same data output or is it just the same file name being overwritten? Are multiple reports output?

[g4tv4life]

the officer name and date of incident fields are being pulled from the query, its overwriting the same report with the 1st person, and the report isn't changing each time. It is only 1 report. I'm extremely bad at coding so you have to bear with me here, The only problem I see is that when it loops through its not moving to the next record.

NOTE: Use / instead of \ in CODE tag.

Where are [Officer Name] and [Date of Incident] coming from? They won't come from the report RecordSource.

Are you sure it's the same data output or is it just the same file name being overwritten? Are multiple reports output?

[June7]

The query has only one field - ID, so how can it provide the name and date values?

Is this code behind a bound form? Are [Officer Name] and [Date of Incident] fields on the form? The code is just pulling the values from the record that has focus, not from the VBA recordset (which doesn't have them anyway).

I think the code is looping the recordset but not changing the officer name and date of incident values because of incorrect referencing.

[g4tv4life]

Heres how it works, I have a bound form that inputs each officer as a record, I have a report where each page is a form letter and the fields are referenced in the letter. I have a query that connects 2 tables, the one with the officer data, the other with the police department address data. The query has all the fields but I'm not sure how to reference them properly in my code as I'm mostly self taught. I believe my referencing is to blame, any suggestions how to properly reference the 2 fields? (Officer Name and Date of Incident are both fields, in the query)

The query has only one field - ID, so how can it provide the name and date values?

Is this code behind a bound form? Are [Officer Name] and [Date of Incident] fields on the form? The code is just pulling the values from the record that has focus, not from the VBA recordset (which doesn't have them anyway).

I think the code is looping the recordset but not changing the officer name and date of incident values because of incorrect referencing.

[ItsMe]

if MyRS.eof = true then
msgbox "No Records found. Exiting Procedure!"
MyRS.close
set MyRS = nothing
exit sub
end if

With MyRS

.movefirst    'It is important to move to the first record 
'so you go through all records while using .MoveNext

Do While Not .EOF   'You are using With so no need to type MyRS here


   DoCmd.OpenReport strRptName, acViewPreview, , "[ID]=" & ![ID]
   DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, [Application].[CurrentProject].[path] & "\Aed letters\" & "Aed Letter " & [Officer Name] & " " & Format([Date of Incident], "MMMM dd, yyyy") & ".pdf"
   DoCmd.Close acReport, strRptName, acSaveNo
   .MoveNext
Loop

End With

[June7]

The fields might be in [Police Departments Query] but they aren't in the VBA recordset. Can use a wildcard to select all fields.

SELECT * FROM [Police Departments Query];

Specify fields with comma separation.

SELECT ID, [Officer Name], [Date of Incident] FROM [Police Departments Query];

Then reference the recordset fields with the ! (bang)

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, [Application].[CurrentProject].[path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf"

[g4tv4life]

June, you are a SAINT!
but you've heard that enough times to be canonized for real.

I didn't realize I hadn't create a record-set properly, Now I know =D