E
Erick C
Hi everybody.
I am hoping someone may be able to help me out. I have a database
report that I want to export into PDF for each individual customer.
Basically, I want to set up a button on my form that will allow the
user to pick a location to save the files and then create a PDF based
on a report that I have saved in Access for each customer
individually. I have been looking around online for a while and I
have been trying a few different things with not much success.
I had some code from Ken Snell that worked great for looping through
customer numbers and exporting an excel file for each individual
customer. I tried to do a bit of modification to this code and add in
the OutputTo command with no luck. Since the code uses a "dummy"
query to grab the information that is changing with each customer, I
do not know if I can have the properties of the report change every
time this source data changes.
I am trying to work with Stephen Leban's A2000ReportToPDF database
right now, but it is far more advanced than my puny range of code
knowledge will allow me to comprehend.
Is there anyone who has created something like this before? Can it be
done? Can someone help me out or point me in the right direction?
Thanks!
Here is the code that I am working with right now:
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = " SELECT DISTINCT BNY Acct# FROM [Customers_To_Email];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("BNY Acct#", "Customers_To_Email", _
"BNY Acct# = '" & rstMgr!BNY Acct#.value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Customers_To_Email] WHERE " & _
"BNY Acct# = '" & rstMgr!BNY Acct#.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.OutputTo acOutputReport, [Sweep_Report_For_Email], _
acFormatPDF, strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
MsgBox "Individual Files Created.", vbOK, "Status"
End Sub
I am hoping someone may be able to help me out. I have a database
report that I want to export into PDF for each individual customer.
Basically, I want to set up a button on my form that will allow the
user to pick a location to save the files and then create a PDF based
on a report that I have saved in Access for each customer
individually. I have been looking around online for a while and I
have been trying a few different things with not much success.
I had some code from Ken Snell that worked great for looping through
customer numbers and exporting an excel file for each individual
customer. I tried to do a bit of modification to this code and add in
the OutputTo command with no luck. Since the code uses a "dummy"
query to grab the information that is changing with each customer, I
do not know if I can have the properties of the report change every
time this source data changes.
I am trying to work with Stephen Leban's A2000ReportToPDF database
right now, but it is far more advanced than my puny range of code
knowledge will allow me to comprehend.
Is there anyone who has created something like this before? Can it be
done? Can someone help me out or point me in the right direction?
Thanks!
Here is the code that I am working with right now:
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = " SELECT DISTINCT BNY Acct# FROM [Customers_To_Email];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("BNY Acct#", "Customers_To_Email", _
"BNY Acct# = '" & rstMgr!BNY Acct#.value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Customers_To_Email] WHERE " & _
"BNY Acct# = '" & rstMgr!BNY Acct#.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.OutputTo acOutputReport, [Sweep_Report_For_Email], _
acFormatPDF, strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
MsgBox "Individual Files Created.", vbOK, "Status"
End Sub