Author: myyorku2002

Objective: Print Access report by defined query results and generate pdf report for each selected record.

Reference: http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

Reference: http://www.utteraccess.com/wiki/index.php/RunSQL_vs_Execute

Reference:https://msdn.microsoft.com/en-us/library/office/ff192065.aspx

Basic coding:

Dim rst As DAO.Recordset

If you run into an error of “User-defined type not defined, you need to add “Microsoft DAO 3.6 object Library” to the reference library under “Tools” menu and then “Reference” item.  Refer https://support.microsoft.com/en-us/kb/289664 for details.

DAO (Data Access Object) is different from ADO (Active Data Object). Both are types of recordsets.  If the VBA is just used in Access only, it is good to use DAO.

Set rst currentDb. OpenRecordset (“Select ID from ids”)

To set a recordset object, using the OpenRecordset method of a database object.

The “… ” referred to above can be one of three things, passed as a literal string or a variable:

• a Table Name
• a Query Name
• an SQL string that returns records (not an Action Query)

rst.close

Set rst= Nothing

“Clean up a recordset object.

These closing lines are very important and MUST be run (we include them in an exit procedure in case we have an error to ensure that the recordset is always closed and the object is dereferenced). Failure to do so may induce some very hard to track bugs.

With a recordset, we need to explicitly open it, and therefore it needs to be explicitly closed before the object pointer is destroyed.

If you used an object variable for the Database, you should also set this to Nothing (but, as it did not need to be “opened”, we need not “close” it).

VBA is supposed to automatically do this for us in case we don’t get it ourselves, but it doesn’t always catch it, and wierd things start happening when they are not caught.”– www.utteraccess.com

EOF

Do While rst.EOF = False

“Cursors and Position

BOF (beginning of file) and EOF (end of file) are always there. Records may not be. So, the first thing to do when opening a recordset is generally to check and make sure there are records in it. IF there are records, the RecordCount property of the recordset will return a nonzero value (not always the number of records though). Or, you can determine this by checking the BOF and EOF properties… if they are both true, there are no records.” –– www.utteraccess.com

Do loop until the end of the file. If the cursor reaches the end the file, the loop will be ended.

DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, PdfFileNameToStore, False

Syntax: DoCmd.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding, OutputQuality)

“You can use the OutputTo method to output the data in the specified Access database object (a datasheet, form, report, module, data access page) to several output formats.” -msdn.microsoft

acOutputReport is an ObjectType Parameter.  The types of object to output also includes acOutputForm, acOutputFunction, acOutputModule, acOutputQuery, acOutputserverView, acOutputStoredProcedure and acOutputTable.

stDocName is an ObjectName Parameter. “A string expression that’s the valid name of an object of the type selected by the ObjectType argument.”-msdn.sicrosoft

acFormatPDF is an OutputFormat Parameter.   “An AcFormat constant that specifies the output format. If you omit this argument, Access prompts you for the output format. It can be one of the following AcFormat constants: acFormatASP, acFormatDAP, acFormatHTML, acFormatIIS, acFormatRTF, acFormatSNP, acFormatTXT, acFormatXLS” -msdn.microsoft

PdfFileNameToStore is an OutputFile Parameter.  “A string expression that’s the full name, including the path, of the file you want to output the object to. If you leave this argument blank, Access prompts you for an output file name.” -msdn.microsoft

False is an AutoStart Parameter. “Use True (–1) to start the appropriate Microsoft Windows–based application immediately, with the file specified by the OutputFile argument loaded. Use False (0) if you don’t want to start the application. ” -msdn.microsoft

rst.MoveNext

This is the code to move the cursor.  Similar codes like rst.MoveFirst, rst. MoveLast, rst.MoveNext, rst.MoveRrevious, rst.Move can move the cursor between the BOF and EOF.  “By default, a recordset is opened with the cursor on the first record if there are records in the set. It is a good idea to check the BOF or EOF properties before navigating towards either one. “-– www.utteraccess.com

Here is a complete example:

Private Sub print_cpr()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strinsert As String
Dim mypath As String
Dim mypdf As String
Dim temp As String
Dim myReport As String

mypath = “X:\Work\CPR\1415\”
myReport = “rep_undergrad”
Set db = DBEngine.OpenDatabase(“X:\Work\AAP\Database\1415\1415 AAPR Undergraduate.mdb”)
Set rs = db.OpenRecordset(“Select * from tblCPR1415”, dbOpenSnapshot)

Do While Not rs.EOF

temp = rs(“Program_short”)
Debug.Print “The value of varialble temp is ” & temp
mypdf = rs(“Program_short”) & “.pdf”
DoCmd.OpenReport myReport, acViewPreview, , “[program_short]='” & temp & “‘”
DoCmd.OutputTo acOutputReport, “”, acFormatPDF, mypath & mypdf
DoCmd.Close acReport, myReport
DoEvents

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Description of the code:

The code is used to open a access database, then open a query with unique program_short value that can be passed on to the report.  The result of the query is saved temporarily in the rs variable.  The cursor starts with the first record in the rs recordset.  The value of the first record is saved as the name of the pdf file, eg. AP-UG-EN which stands for a specific Faculty undergraduate English program.  Open the undergraduate program report with the where condition of program short equal to “AP-UG-EN”.  Output the pdf report to the defined the path with AP-UG-EN.pdf as file name. Cursor then move to the next record on the rs recordset until the end of the recordset to generate individual program report and output to pdf document.  Once the cursor reaches the end of the recordset, the loop is finished and the program closes the rs recordset and  clear the memory for both the recordset and database.

Application in Work:

I have designed standardized academic program reporting in Access with 2 layouts, one for Undergraduate and one for Graduate Programs.  Use the above code and generate individual data sheets for hundreds for undergraduate and graduate programs.  Then the datasheets can be distributed to the corresponding Faculty and program Directors.

 

Y. I. An