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

Access VBA generate individual PDF for each row

P: 8
Hello,

I don't really know anything about VBA. I need to generate a pdf for each groupid named with the groupid. I found some code and repurposed it. Someone was doing the same thing as I but it is not working. The groupids do not match the groupid and that groupid's associated data in the pdf. So for example groupid 555.pdf file does not contain groupid 555 associated data. Each generated pdf contains the same groupid data while the name of the pdf is created as I desire it to. There are not any error messages.

Form1 contains three fields. The groupid, and two associated data fields. Query3 pulls the data from form1 for whatever row the form is open to. Query3 data goes to a report that displays one row at a time. I run the code in a module.


This is the code for the query3.
Expand|Select|Wrap|Line Numbers
  1. SELECT [groupidquery].groupid, [groupidquery].Expr1, [groupidquery].Expr2
  2. FROM [groupidquery]
  3. WHERE ((([groupidquery].groupid)=[Forms]![Form1]![groupid]));
This is the module code

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3.  
  4. Private Sub FFS()
  5.  
  6.  Dim db As DAO.Database
  7.     Dim rs As DAO.Recordset
  8.     Dim MyFileName As String
  9.     Dim mypath As String
  10.     Dim temp As String
  11.  
  12.     mypath = "C:\Users\myfolder\folder\"
  13.  
  14.     Set db = CurrentDb()
  15.  
  16.     Set rs = db.OpenRecordset("SELECT DISTINCT [groupid] FROM [groupidquerymain]", dbOpenSnapshot)
  17.  
  18.     Do While Not rs.EOF
  19.  
  20.         Forms![Form1].groupid.Value = 0
  21.  
  22.         temp = rs("groupid")
  23.  
  24.         MyFileName = rs("groupid") & ".pdf"
  25.  
  26.         Forms![Form1].groupid.Value = rs!groupid
  27.  
  28.       DoCmd.OpenForm "Form1"
  29.       DoCmd.GoToRecord , acDataForm, acGoTo, "temp"
  30.       DoCmd.OpenQuery "query3"
  31.       DoCmd.Close acQuery, "query3", acSaveYes
  32.       DoCmd.OpenReport "Report1", acViewPreview
  33.       DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
  34.       DoCmd.Close acReport, "groupidquery", acSaveYes
  35.  
2 Weeks Ago #1

✓ answered by Gilley178

One of the queries was not pulling data from the right table. At least that is my theory but it works now. I would now consider it to be working.

Share this Question
Share on Google+
1 Reply


P: 8
One of the queries was not pulling data from the right table. At least that is my theory but it works now. I would now consider it to be working.
1 Week Ago #2

Post your reply

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