Microsoft Access - Store a table value as a Variable

Asked By Jason on 03-Jan-12 04:14 PM
I'm creating a macro that cycle's through a list of names and emails out a report of each of the names.   The tables with the names will have 2 columns.  1 for name and 1 for email.  My code looks like this so far

Sub RunReport()
Dim dbs As Database
Dim rst As Recordset
Dim saecount As Integer
Dim i As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM [SAE]", dbOpenDynaset)

saecount = DCount("[Field1]", "SAE")
i = 1
MyPath = "G:\Jason\Blueline\"

Do Until i = saecount
saename = rst.Fields(1).Value
MyFilename = "Blueline Report - " & saename
myfilter = "Field1 = saename"
DoCmd.OpenReport "Copy of WIP", acViewPreview, , myfilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, True
DoCmd.Close acReport, "Copy of WIP"
i = i + 1

End Sub

I realize this is incomplete but I think I can figure everything out except for one part.  How do i retrieve the value of the current record selected and enter that as the parameter for the report?  I'm just not sure how to enter the saename as the parameter for the query that the report uses.  Currently it's setup so that myfilter = "Field1 = saename" but this just creates more parameters to enter when I run the code.

Pat Hartman replied to Jason on 03-Jan-12 04:14 PM
Let me correct your description first.  What you have posted is a "procedure" NOT a "macro".  Macros in Access are quite different from Macros in other Office products and are not written in VBA.

I changed a number of things in your code.

  1. I disambiguated the references for the DAO objects.  This is important because the ADO library contains objects of the same name and if that library gets added to your references for some reason, there will be confusion so the DAO. prefix will eliminate that as a posibility.
  2. I removed the other 2 variable declarations since they are not needed.  There was a third variable that was not declared.  So make sure that you have "Option Explicit" as the second line in all of your code modules and in the VBA options set the option that requires variables to be declared.
  3. I changed the loop to use rst.EOF as its control rather than an integer.
  4. I corrected the constant name from acNormal to acViewNormal which is the current name of the option.
  5. I added a "where" clause to the OpenReport method.  This will pass the recordID to the report as it opens and limit the data it shows.
  6. Since I don't know the the actual name of the unique identifier, I used "RecID".  Please change it to the correct name.
  7. And finally, I assumed that the unique identifier is defined as a long integer.  If it is defined as text, you will need to modify the line slightly.  -- DoCmd.OpenReport "Blueline Report", acViewNormal,,"RecID = """ & rst!RecID & """" -- to embed RecID in quotes.
Sub RunReport()
 Dim dbs As DAO.Database
 Dim rst As DAO.Recordset
 Set dbs = CurrentDb
 Set rst = dbs.OpenRecordset("SELECT RecID FROM [SAE]", dbOpenDynaset)
 Do Until rst.EOF
   DoCmd.OpenReport "Blueline Report", acViewNormal,,"RecID = " & rst!RecID
 End Sub

Pat Hartman replied to Pat Hartman on 03-Jan-12 04:16 PM
I changed your SQL to include only the RecID but I see now that it also needs the email address so you can change it back to Select *. Sorry
Jason replied to Pat Hartman on 03-Jan-12 04:25 PM
Thank you this looks very good.  I think I edited my first post not realizing that you had replied to mine.  The only other part I would need to add would be the naming of the file. I was using a slightly different method so I was wondering if you would be able to explain how to retrieve the name and use that for the filename output and also retrieve the email so I can store that as a variable and use that to send out the report.  I would have used rst.Fields(1).Value but that is no longer working.  Thanks for your help.
Jason replied to Pat Hartman on 03-Jan-12 04:28 PM
Thanks thats what i needed.  Very helpful
Pat Hartman replied to Pat Hartman on 04-Jan-12 09:29 PM

To output a .pdf file you need to use the OutPutTo Method (please read the help entry so you understand the possible arguments):
DoCmd.OutputTo , acOutputReport, acFormatPDF, "C:\Data\Rptxyz" & Me.RecID & "_" & Format(Date(), "yyyymmdd") & ".pdf" - this will create a name like C:\Data\Rptxyz12345_20120104.pdf  -
Change the red part to the path and reportname you want to use. The green part is variable and should probably be the unique identifier for the record.

You could also make the path more variable so it includes the reportname and date for example - "C:\ReportOutputs\" & Me.ReportName & Format(Date(),"yyyymmdd") & "\" & Me.RecID & ".pdf" - this will put the file in a folder named something like "C:\ReportOutputs\ExecutiveSummary20120104\12345.pdf  - you need to decide on a naming scheme that will be meaningful and allow you to run the same report multiple times.  Or, each time you run a set of reports, add code to delete the previous pdf's.  That way you can keep reusing the same names.

The tricky part here is filtering the report since the OutputTo Method does not give you a "where" option like the OpenReport Method does. 
1.Create a form to use to run this process.
2.Add a hidden field to hold the RecID
3.Add a button to start the process.
4.In the loop, instead of passing the RecID as an argument, save it to the hidden form field - Me.HiddenRecID = rst!RecID
5.Modify the query used as the RecordSource for the report to reference this form field.  The where clause will be something like  - Where RecID = Forms!yourformname!HiddenRecID