Unanswered: Printing a report into multiple pdf files using a record as part of the file name
I have report created in Access 2010 originated from a query based on a table.
My table is called: tblFiledReconn
In order to manipulate what data I want to see in the report I created a list query called: RptQry_List_Table_For_Entech_Use
Finally I have a report based on this query called: FieldReconnFormReport
There is a field on my table called FacilityID.
What I need is to print a report for each FacilityID into an individual PDF file containing the FacilityID as part of the file name. At the moment this would be 817 individual reports.
I also wish if I can add a date included on the report which is on another field called: Date. But again this is a wish and not a must. Since I am not an avid code writer I decided to get the need accomplished before attempting this. Since the Date field is a date, I guess I need to convert it into string or numbers to the format I desire before adding it to the name which would made the code more complex.
Searching and reading forums I was able to develop the following code. In this attempt I was trying to accomplish the following.
For each facility ID print a pdf individual report with the facility id as part of the name followed by "_FRECON.pdf". For example for facility ID wwMH84303 the report file name would be wwMH84303_FRECON.pdf
Here is the code:
Private Sub Command0_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
strRptName = "FieldReconnFormReport"
strSQL = "Select tblFieldReconn.[FacilityID] From RptQry_List_Table_For_Entech_Use;"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do While Not MyRS.EOF
DoCmd.OpenReport strRptName, acViewPreview, , "[FacilityID]=" & ![FacilityID]
DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Temporary FR Forms\" & ![FacilityID] & "_FRECON.pdf", False
DoCmd.Close acReport, strRptName, acSaveNo
Set MyRS = Nothing
The problem is when I run the code there seems to be a problem with the openreport comand line. A window comes out requesting a parameter value. The Enter Parameter Value window shows the Current Facility ID and a text box. If I hit OK it creates a one page report with blank fields but with the correct file name. If I type the current Facility Id on the text box it creates the one page report correctly. So I my guess is that it is not understanding the instruction that matches the Facility ID in the report with the corresponding Facility ID from the recordset ( "[FacilityID]=" & ![FacilityID]). Since it does creates the correct file name I assume it is creating the desired recordset. I have spent several hours trying to get it work but no success. Please help!
Now to the wish part:
As I explained there is also a Date field in the table (,query and report). It would be great if the created file name could be a combination of both. For example for FacilityID: wwMH98765 visited of Date:11/20/2012 it would be great is the file name could be wwMH98765_FR112012.pdf or better if wwMH98765_FR121120.pdf (year,month,day)
Thanks in advance