By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,396 Members | 2,501 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,396 IT Pros & Developers. It's quick & easy.

How to separate PDFs in one report

P: 27
Hi after my last question I am trying to separate PDFs in one report based on their Employee ID and have them saved to a folder and named based on their EmployeeID. I have made a query to make a report
I have two codes that I am trying but both keep coming back with an error. This one at
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "Balance", acViewPreview, , "EmployeeID = " & myrs.Fields("EmployeeID").Value
comes back with the error "run time error 3464. data type mismatch in criteria expression" and even adding `& "'"` just keeps creating extra errors...

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.     Option Explicit
  3.  
  4.     Private Sub cmdprintsep_Click()
  5.         Dim myrs As DAO.Recordset
  6.         Dim myPDF, myStmt As String
  7.  
  8.         ' Open a record set with a list of invoice number to print
  9.         myStmt = "SELECT distinct EmployeeID from queBalance"
  10.         Set myrs = CurrentDb.OpenRecordset(myStmt)
  11.  
  12.         ' For each invoice, print in a .pdf
  13.         Do Until myrs.EOF
  14.  
  15.             ' Set the output path of your PDF file invoice
  16.             myPDF = "C:\Users\user\Desktop\reports\" & Format(myrs.Fields("EmployeeID"), "000000") & ".pdf"
  17.  
  18.             ' Open the report with the proper where condition
  19.             DoCmd.OpenReport "Balance", acViewPreview, , "EmployeeID = " & myrs!EmployeeID & " "
  20.         strFilter = “EmployeeID = “ & myrs!EmplyeeID
  21.         ' Generate the output in pdf
  22.         DoCmd.OutputTo objectType:=acOutputReport, objectName:="Balance", outputformat:=acFormatPDF, outputfile:=myPDF, outputquality:=acExportQualityPrint
  23.  
  24.         DoCmd.Close ' Close the report
  25.         rs.MoveNext ' read next
  26.  
  27.         Loop
  28.  
  29.         ' some cleanup
  30.         myrs.Close
  31.         Set myrs = Nothing
  32.  
  33.     End Sub
The other code has an error that pulls up "compile error: variable required - can't assign to this expression" at the
Expand|Select|Wrap|Line Numbers
  1. For Each [EmployeeID] In [EmployeeID]
line. The other code is as follows


Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2.     Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [EmployeeID] FROM queBalance")
  3.  
  4.     Dim MyPath As String
  5.     Dim MyFilename As String
  6.     MyPath = "C:\Users\user\Desktop\reports\"
  7.     'Loop structure may vary depending on how you obtain values
  8.     For Each fGetID In EmployeeID
  9.         MyFilename = "FI" & EmployeeID & ".pdf"
  10.     'Open report preview and auto-save it as a PDF
  11.         DoCmd.OpenReport "Balance", acViewPreview, , "EmployeeID = " & EmployeeID & "'"
  12.         DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
  13.         DoCmd.Close acReport, "Balance"
  14.     Next [EmployeeID]
  15.  
  16.     End Sub
Any help will be much appreciated! Thank you all in advance.
Feb 22 '19 #1

✓ answered by twinnyfo

In a separate module:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrReportFilter As String
Delete your Line 4
Delete your Line 13 (you never need to "open" a report to do this)
Delete your line 16

Thus:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdprintsep_Click()
  2.     Dim db     As DAO.Database
  3.     Dim rs     As DAO.Recordset
  4.     Dim myPDF  As String
  5.     Dim myStmt As String
  6.  
  7.     myStmt = _
  8.         "SELECT DISTINCT EmployeeID " & _
  9.         "FROM queBalance;"
  10.     Set db = CurrentDB()
  11.     Set rs = db.OpenRecordset(myStmt)
  12.     gstrReportFilter = ""
  13.     With rs
  14.         If Not (.BOF And .EOF) Then
  15.             .MoveFirst
  16.             Do While Not .EOF
  17.                 myPDF = _
  18.                     "C:\Users\user\Desktop\reports\" & _
  19.                     Format(!EmployeeID, "000000") & ".pdf"
  20.                 gstrReportFilter = "EmployeeID = " & !EmployeeID
  21.                 DoCmd.OutputTo _
  22.                     ObjectType:=acOutputReport, _
  23.                     ObjectName:="Balance", _
  24.                     OutputFormat:=acFormatPDF, _
  25.                     OutputFile:=myPDF, _
  26.                     OutputQuality:=acExportQualityPrint
  27.                 .MoveNext
  28.             Loop
  29.         End If
  30.         .Close
  31.     End With
  32.     db.Close
  33.     Set rs = Nothing
  34.     Set db = Nothing
  35.  
  36.     Exit Sub
  37. End Sub
You may notice I made a few other minor adjustments. In the olden days, you could declare mutliple variables on one line without confusion; apparently this now only results in Variants being declared, except for the one followed by "As ...". (If I remember correctly). It is always better to be more explicit than less explicit when it comes to Access.

Although your method of setting the Recordset works absolutely fine, if you set several recordsets using this method, it creates more engine overhead. By declaring a separate variable as a Database, you can assign as many recordsets as you want and only use this one instance of the DB.

Note Line 11 especially. This is an absolute must every time you open any recordset. This method positively confirms that there are records to use (and if not, it just exits the process). Otherwise, your code may break or hang or cause a major rift in the intergalactic cosmic continuum vortex matrix. It's just a good habit to get into.

I made a few formatting changes to make things a bit easier to read, especially on the Forum.

I also highly recommend that you begin inserting some type of Error Handling code into each and every one of your procedures (even if it is just to tell the code to continue). This will prevent your DB from breaking on your users (and prevent more of those wormholes from forming).

Also, another recommendation. If you are saving your reports--especially over time, for historical purposes--you may want to add a descriptive name to the Report, e.g.:
Expand|Select|Wrap|Line Numbers
  1. myPDF = _
  2.     "C:\Users\user\Desktop\reports\" & _
  3.     "Current Balance Report - " & _
  4.     Format(!EmployeeID, "000000") & _
  5.     " - " & _
  6.     Format(Date, "yyyy-mm-dd") & _
  7.     ".pdf"
This way, you know what the report is about, who it belongs to, and when it was created.

Hope these little tidbits hepp!

Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
As stated before, if you open a report, you can filter it but not save it. If you export it, you can’t filter it. This is a shortfall in Access. In order to do what you are trying to do, you must build a filter into the query or the report that is managed outside the report. That is a very esoteric description but the basics is this: you establish a public string that you use as a filter. You set its value to "" then as you cycle through records you set its value to the filter strFilter = "EmployeeID = " & myrs!EmplyeeID. Then in the OnOpen event of the report, you check the value of that public string. If it is "" then do nothing but if not, then apply that filter string to the report’s filter. You do not OPEN the report, you must export it and that should work.

By the way, you may notice the simplicity I have shown for using a recordset’s value. Your method of referring to the Fields and value of that field is quite clumsy.

Please note that I have not analyzed your code in depth as I wanted to get you on the right track first.
Feb 23 '19 #2

P: 27
Thanks twinnyfo for the info. I tried putting a filter into the query itself by putting
Expand|Select|Wrap|Line Numbers
  1. fGetID()
into the criteria, then making it a public function in a separate module. Then calling upon fGetID to equal EmployeeID. When that failed, I have tried to put a filter called strFilter upon opening the public sub report_load then adding the code you gave in above to my code. And now I'm totally confused... Are either methods I used what you are referring to?

Which code would be better to continue attempting this task?
Feb 25 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
You are 100% on the right track.

If you are using fGetID(), how are you setting the value of fGetID()? That would be my first question, because, in theory, that should work as well.

If you use my descsribed method, again, in a separate module, declare a public String Variable (in my case I use gstrReportFilter). Your loop would look similar to this:
Expand|Select|Wrap|Line Numbers
  1. gstrReportFilter = ""
  2. Do While Not myrs.EOF
  3.     gstrReportFilter = = "EmployeeID = " & myrs!EmplyeeID
  4.     DoCmd.OutputTo etc., etc., etc.
  5.     myrs.MoveNext
  6. Loop
In the Report's VBA Module:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. On Error GoTo EH
  3.  
  4.     If Not gstrReportFilter = "" Then
  5.         With Me
  6.             .Filter = gstrReportFilter
  7.             .FilterOn = True
  8.         End With
  9.     End If
  10.  
  11.     Exit Sub
  12. End Sub
I prefer to set the filter at the Report level, rather than the Query level, because there may be cases in which you want to see all the records in teh Query. This just makes it easier (IMO).

Hope this hepps!
Feb 25 '19 #4

P: 27
Thanks Twinnyfo

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdprintsep_Click()
  2.     Dim rs As DAO.Recordset
  3.     Dim myPDF, myStmt As String
  4.     Dim gstrReportFilter As String
  5.  
  6.     myStmt = "SELECT distinct EmployeeID from queBalance"
  7.     Set rs = CurrentDb.OpenRecordset(myStmt)
  8.     gstrReportFilter = ""
  9.     Do While Not rs.EOF
  10.         myPDF = "C:\Users\user\Desktop\reports\" & Format(rs!EmployeeID, "000000") & ".pdf"
  11.  
  12.         gstrReportFilter = "EmployeeID = " & rs!EmployeeID
  13.         DoCmd.OpenReport "Balance", acViewPreview, gstrReportFilter, "[EmployeeID] = '" & rs!EmployeeID & "'", acWindowNormal
  14.         DoCmd.OutputTo objectType:=acOutputReport, objectName:="Balance", outputformat:=acFormatPDF, outputfile:=myPDF, outputquality:=acExportQualityPrint
  15.  
  16.         DoCmd.Close
  17.         rs.MoveNext
  18.  
  19.     Loop
  20.     rs.Close
  21.     Set rs = Nothing
  22.  
  23.  
  24. End Sub
NVM I GOT IT!!! THANK YOU SO MUCH TWINNYFO!!!!!!!
Feb 25 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
In a separate module:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrReportFilter As String
Delete your Line 4
Delete your Line 13 (you never need to "open" a report to do this)
Delete your line 16

Thus:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdprintsep_Click()
  2.     Dim db     As DAO.Database
  3.     Dim rs     As DAO.Recordset
  4.     Dim myPDF  As String
  5.     Dim myStmt As String
  6.  
  7.     myStmt = _
  8.         "SELECT DISTINCT EmployeeID " & _
  9.         "FROM queBalance;"
  10.     Set db = CurrentDB()
  11.     Set rs = db.OpenRecordset(myStmt)
  12.     gstrReportFilter = ""
  13.     With rs
  14.         If Not (.BOF And .EOF) Then
  15.             .MoveFirst
  16.             Do While Not .EOF
  17.                 myPDF = _
  18.                     "C:\Users\user\Desktop\reports\" & _
  19.                     Format(!EmployeeID, "000000") & ".pdf"
  20.                 gstrReportFilter = "EmployeeID = " & !EmployeeID
  21.                 DoCmd.OutputTo _
  22.                     ObjectType:=acOutputReport, _
  23.                     ObjectName:="Balance", _
  24.                     OutputFormat:=acFormatPDF, _
  25.                     OutputFile:=myPDF, _
  26.                     OutputQuality:=acExportQualityPrint
  27.                 .MoveNext
  28.             Loop
  29.         End If
  30.         .Close
  31.     End With
  32.     db.Close
  33.     Set rs = Nothing
  34.     Set db = Nothing
  35.  
  36.     Exit Sub
  37. End Sub
You may notice I made a few other minor adjustments. In the olden days, you could declare mutliple variables on one line without confusion; apparently this now only results in Variants being declared, except for the one followed by "As ...". (If I remember correctly). It is always better to be more explicit than less explicit when it comes to Access.

Although your method of setting the Recordset works absolutely fine, if you set several recordsets using this method, it creates more engine overhead. By declaring a separate variable as a Database, you can assign as many recordsets as you want and only use this one instance of the DB.

Note Line 11 especially. This is an absolute must every time you open any recordset. This method positively confirms that there are records to use (and if not, it just exits the process). Otherwise, your code may break or hang or cause a major rift in the intergalactic cosmic continuum vortex matrix. It's just a good habit to get into.

I made a few formatting changes to make things a bit easier to read, especially on the Forum.

I also highly recommend that you begin inserting some type of Error Handling code into each and every one of your procedures (even if it is just to tell the code to continue). This will prevent your DB from breaking on your users (and prevent more of those wormholes from forming).

Also, another recommendation. If you are saving your reports--especially over time, for historical purposes--you may want to add a descriptive name to the Report, e.g.:
Expand|Select|Wrap|Line Numbers
  1. myPDF = _
  2.     "C:\Users\user\Desktop\reports\" & _
  3.     "Current Balance Report - " & _
  4.     Format(!EmployeeID, "000000") & _
  5.     " - " & _
  6.     Format(Date, "yyyy-mm-dd") & _
  7.     ".pdf"
This way, you know what the report is about, who it belongs to, and when it was created.

Hope these little tidbits hepp!
Feb 25 '19 #6

Post your reply

Sign in to post your reply or Sign up for a free account.