Good Afternoon, hope everyone is well.....
I have an access database which currently runs a report "rptpctsummaries" based on information in a table "tblbasedata". What I would like it to do, is create each report individually and export it with the field "ClusGroupCode" as the file name, as type pdf.
I am still learning vba (although I can use access fine) and have managed to hijack the following code from the web. However I am unable to get it to work.... just says created although no files are there.
Please can anyone help tell me where I have gone wrong?
Thanks in advance!
I have an access database which currently runs a report "rptpctsummaries" based on information in a table "tblbasedata". What I would like it to do, is create each report individually and export it with the field "ClusGroupCode" as the file name, as type pdf.
I am still learning vba (although I can use access fine) and have managed to hijack the following code from the web. However I am unable to get it to work.... just says created although no files are there.
Please can anyone help tell me where I have gone wrong?
Thanks in advance!
Code:
Private Sub btnPrintReports_Click()
On Error GoTo Err
'-----------------------------------
'Variables
'-----------------------------------
Dim StrClusGroupCode As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQuery As String
Dim strReportName As String
Dim rs As DAO.Recordset 'shortens record set call
Dim strRecordSetSQL As String 'record set string to pass to SQL
Dim strPath As String
strPath = "C:\Temp\NCATEST\"
Set db = CurrentDb 'sets db[DAO.Dataset] to current data base
strRecordSetSQL = "SELECT tblbasedata.ClusGroupCode, tblbasedata.ClusGrpDesc, " & _
"tblbasedata.InvoiceValue, from tblbasedata"
Set rs = db.OpenRecordset(strRecordSetSQL) 'Shortens the recordset call
Do While Not rs.EOF 'Do While Records are there
StrClusGroupCode = rs!ClusGroupCode
'-----------------------------------
'Change Query
'-----------------------------------
'create Qry String
strQuery = "SELECT * FROM tblbasedata WHERE (((tblbasedata.ClusGroupCode)='" & StrClusGroupCode & "'));"
'Write String to Query
CurrentDb.QueryDefs("temp").SQL = strQuery
'-----------------------------------
'Output Report
'-----------------------------------
strReportName = strPath & rs!ClusGroupCode & ".pdf"
DoCmd.OutputTo acOutputReport, "rptPCTsummaries", acFormatPDF, strReportName, False
rs.MoveNext
Loop
Err:
'MsgBox Error$
DONE:
MsgBox ("Reports Created")
End Sub
Last edited: