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:
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