Erick,
Try...
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourControl]
'If Your Data Type is Numeric
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport "YourReport", acViewPreview, , "[YourFieldControlSource]
= " & cboCode.ItemData(intCounter)
DoEvents
DoCmd.OutputTo acReport, "YourReport", acFormatRTF,
DoCmd.Close acReport, "YourReport"
Next
'If Your Data Type is a String
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport "YourReport", acViewPreview, , "[YourFieldControlSource]
= '" &
cboCode.ItemData(intCounter) & "'"
DoEvents
DoCmd.OutputTo acReport, "YourReport", acFormatRTF,
DoCmd.Close acReport, "YourReport"
Next
--
Gina Whipp
2010 Microsoft MVP (Access)
II
http://www.regina-whipp.com/index_files/TipList.htm
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