Export/Print report to multiple PDF files using field as part of file name

[enzokevin]

Help needed!!
I have report created in Access 2010 originated from a querybased on a table.
My table is called: tblFiledReconn
In order to manipulate what data I want to see in the reportI 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 anindividual PDF file containing the FacilityID as part of the file name. At themoment this would be 817 individual reports.

I also wish if I can add a date included on the report whichis 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 accomplishedbefore attempting this. Since the Date field is a date, I guess I need toconvert it into string or numbers to the format I desire before adding it tothe name which would made the code more complex.


Searching and reading forums I was able to develop thefollowing code. In this attempt I was trying to accomplish the following.
For each facility ID print a pdf individual report with thefacility id as part of the name followed by "_FRECON.pdf". Forexample for facility ID wwMH84303 the report file name would bewwMH84303_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] FromRptQry_List_Table_For_Entech_Use;"

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

With MyRS

Do While Not MyRS.EOF
DoCmd.OpenReport strRptName, acViewPreview, ,"[FacilityID]=" & ![FacilityID]
DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF,"C:\Temporary FR Forms\" & ![FacilityID] &"_FRECON.pdf"
DoCmd.Close acReport, strRptName, acSaveNo
.MoveNext
Loop
End With
MyRS.Close
Set MyRS = Nothing
End Sub

The problem is when I run the code there seems to be aproblem with the openreport comand line. A window comes out requesting aparameter value. The Enter Parameter Value window shows the Current Facility IDand a text box. If I hit OK it creates a one page report with blank fields butwith the correct file name. If I type the current Facility Id on the text boxit creates the one page report correctly. So I my guess is that it is notunderstanding the instruction that matches the Facility ID in the report withthe corresponding Facility ID from the recordset ( "[FacilityID]="& ![FacilityID]). Since it does creates the correct file name I assume itis creating the desired recordset. I have spent several hours trying to get itwork 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 acombination of both. For example for FacilityID: wwMH98765 visited ofDate:11/20/2012 it would be great is the file name could bewwMH98765_FR112012.pdf or better if wwMH98765_FR121120.pdf (year,month,day)
Thanks in advance

[June7]

The SQL statement needs a space after FROM. But should be a different error message for that syntax flaw.

The query does not have any filter criteria?

Retrieve the date field in the query as well as the FacilityID. Use that field to concatenate into the PDF name.

Use Format function to set the YYYYMMDD order.

Need a space after the second & in the name concatenation but VBA should fix that for you.

[enzokevin]

June7, thanks for the reply
The space are not the problem. This happened when I copy and paste the code.
No, the I tested the code with and without query filters. It does the same.
For now my main concern now is to get the reports to print. Getting the date in the file name is secondary but thanks for the tips. I don't see the point of coding that before knowing that the reports will print with the populated records.
For some reason the code does not recognize the string from the recordset to relate it to the report FacilityID although the string appears as the requested parameter in the "Enter parameter" window.
Any ideas why this is happening?

[June7]

No idea, all code looks good. If you want to provide db for analysis, follow instructions at bottom of my post.

I have a similar procedure but with difference that I use ADO instead of DAO recordset.

[enzokevin]

Ok thanks, the database is quite large. Give me some minutes to trim it down and send it.
I have played with the code making small changes and for what I can see. It is viewing the corresponding recordset (the individual record for FacilityID like wwMH84188) as the name of the field and not as the value of it so when the code instruction part "[FacilityID] =" & ![FacilityID] runs, it asks me for the parameter value of wwMH84188. If I type the value wwMH84188 it prints correctly. If I hit ok it prints a blank report with the correct file name.

[enzokevin]

June7
Here is the database. I had a hard time reducing its size, so I had to delete sub reports from the report but I don't think they were related to the problem since it is still happening. The table is tblFieldReconn, and the report is based on the query. I created a command button in a form (switchboard) to run the code. Thanks for the help.

[June7]

Wish I'd thought to ask if FacilityID is number and your description of the value should have been a slap in the face. Would have saved a lot of time.

FacilityID field is a text data type. The criteria needs apostrophe delimiters:

"[FacilityID]='" & ![FacilityID] & "'"

[enzokevin]

Great. Now it working as it should. Thank you very very very much. As you have notice I have very little experience in coding.
Now I can concentrate in adding the date into the file name.
The date is already in the query in which the report is based. How do I integrate the change format function into the code in order to add it to the end of the filename?

[June7]

Try:

"C:\Temporary FR Forms\" & ![FacilityID] &"_FR" & Format(!datefield,"YYYYMMDD") & ".pdf"

Date is a reserved word, I hope your field is not named Date.

[enzokevin]

Unfortunately it is, but I guess is something I can easily change. I'll give it a try.
Thanks again for all your help. I will mark the thread as Solved since the important part was accomplished.
I been pulling my hair off for day and a half for this issue, so thank you!!!!!

[JrMontgom]

Ok thanks, the database is quite large. Give me some minutes to trim it down and send it.
I have played with the code making small changes and for what I can see. It is viewing the corresponding recordset (the individual record for FacilityID like wwMH84188) as the name of the field and not as the value of it so when the code instruction part "[FacilityID] =" & ![FacilityID] runs, it asks me for the parameter value of wwMH84188. If I type the value wwMH84188 it prints correctly. If I hit ok it prints a blank report with the correct file name.

Usually when I get that error it is the query that I created. I usually find my error bu using a debug.print query string and then examine the immediate window and see what comes out. Usually I have spelled something wrong. Good luck