Need Help with VBA Code to Export Unique Record In Form to PDF (1 Viewer)

NewbieVBA

New member
Local time
Today, 04:16
Joined
Feb 23, 2015
Messages
2
Calling all Access/VBA Wiz....

I am very new to this and need help as soon as possible. Any assistant provided will greatly appreciated.

I have this code below which export my report about 30 records to unique PDF file format as "Date Received" in the form and EmployeeName eg. "12-20-2014_NewbieVBA.PDF". However, all 30 PDF Files created with the correct name format but each pdf contained all 30 EmployeeName and Information related.

I need to produce 30 PDF Files with each record being unique for each employee. Please help me!

Many thanks,

Option Compare Database
Private Sub PDF_Records_Click()
Dim rpt As Report
Dim qd As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFilename As String
'This opens the report in preview mode.
DoCmd.OpenReport "TblEmployee Report", acViewPreview
Set rpt = Reports("TblEmployee Report")
'This creates a recordset to loop thru
Set rs = CurrentDb.QueryDefs("TblEmployee Query").OpenRecordset

'Loop through the records
rpt.FilterOn = True
With rs
rs.MoveFirst
Do While Not rs.EOF

rpt.Filter = rs!Employeename
'strFilename = "C:\Temp\Reports\" & Format(Date, "mm-dd-yyyy") & "_" & (rs!Employeename) & ".pdf"
strFilename = "C:\Temp\Reports\" & (rs![Date Received]) & "_" & (rs!Employeename) & ".pdf"
DoCmd.OutputTo acOutputReport, "TblEmployee Report", acFormatPDF, strFilename, False

rs.MoveNext
Loop
End With
'Wend
rs.Close
Set rs = Nothing
DoCmd.Close acReport, "TblEmployee Report"
End Sub
:banghead:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:16
Joined
Aug 30, 2003
Messages
36,118
The report is already open and static at the point you try to filter it. Try inside the loop to open filtered, export and close. You can use this method to filter:

http://www.baldyweb.com/wherecondition.htm

so the code would be

DoCmd.OpenReport...
DoCmd.OutputTo...
DoCmd.Close...
 

NewbieVBA

New member
Local time
Today, 04:16
Joined
Feb 23, 2015
Messages
2
Thank you pbaldy but I am not sure that I follow your suggestion. Can you point it out or rewrite to code that I had earlier? Thank you so much for the prompt response.
 

Users who are viewing this thread

Top Bottom