Run a report for each record in a field & output results in seper.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have 30 Groups. I want to run a report for each group and output the
results as a .rtf file. The end result I want is 30 seperate documents.
 
Couple of issues to solve.

First, you need to loop through each of groups to handle each one
separately. OpenRecordset into your table of groups to get the list.

Secondly, you need to pass a filter to the report so it outputs only the
records for that group. OutputTo does not have a filter, so set a public
string variable, and apply it in the Open event of the port.

The actual code will depend on your field names, data types, folder names,
etc, but use this as a rough guide:

1. This code goes into a standard module (Modules tab of Database window):
Dim gstrReportFilter As String
Function ExportGroups()
Dim strSql as String
Dim strPath As String
Dim strFile As String
Dim rs As DAO.Recordset

strPath = "C:\MyFolder\"
strSQL = "SELECT GroupID, GroupName FROM tblGroup;"
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
strFile = strPath & rs!GroupName & ".rtf"
gstrReportFilter = "[GroupID] = " & rs!GroupID
DoCmd.OutputTo acOutputReport, "MyReport", _
acFormatRTF, strFile, False
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

2. This code goes into the Open event procedure of your report:
Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
gstrReportFilter = vbNullString
End If
End Sub

3. To run the export, you could put this into the On Click property of a
command button:
=ExportGroups()
 
Back
Top