By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 767 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Use Filter string AND OutputTo together to create multiple reports

P: 7
Hey Gurus - I've seen a few solutions to this problem, but none of which I can do without a little help. Here's the situation

The following code loops thru a sales report, using the sales rep ID as a filter so that multiple reports are created. This is now generating a PDF report, but I need to change it to RTF documents.

My question is, since I can't create an RTF using open report, is there a way to use the strFilter with the OutputTo method?

Any ideas appreciated!!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreateBHPDFs_Click()
  2.  
  3. On Error GoTo Err_cmdCreateBHPDFs_Click
  4.  
  5.  
  6.     ' Create Variables for Subroutine
  7.  
  8.     Dim strDocName As String    'Report Name
  9.     Dim db         As Database  'Database to retrieve data from
  10.     Dim strSql     As String    'SQL Statement
  11.     Dim rst        As Recordset 'Data Recordset
  12.     Dim strFilter  As String    'Report Filter
  13.  
  14.  
  15.     ' Set Values for Variables
  16.     strDocName = "ReportBHRepTotDetailPDF"
  17.     strSql = "SELECT SALESREP_ID FROM qryCurrentReps ORDER BY SALESREP_ID"
  18.     Set db = CurrentDb
  19.  
  20.     ' Open recordset containing list of Sales Reps
  21.     Set rst = db.OpenRecordset(strSql)
  22.  
  23.     ' Test recordset and act accordingly
  24.     If Not rst.EOF Then
  25.     ' A list of Sales Reps Exists, print the reports
  26.  
  27.         'Advance through the Sales Rep list, one at a time
  28.  
  29.         'Go to first record of recordset
  30.         rst.MoveFirst
  31.  
  32.         'Setup loop to loop through Sales Rep List
  33.         Do Until rst.EOF
  34.  
  35.             'Set the report filter property to the SaleRep in the recordset, zero base array
  36.             strFilter = "SALESREP_ID = '" & rst(0) & "'"
  37.  
  38.             'Print the report using the report name and filter variables
  39.             DoCmd.OpenReport strDocName, , , strFilter
  40.  
  41.  
  42.         'Move to next Sales Rep
  43.         rst.MoveNext
  44.  
  45.         'Loop back to top of Do Until
  46.         Loop
  47.  
  48.     Else
  49.     ' No Sales Reps Exist, inform the user and exit subroutine
  50.         MsgBox "No Sales Data for selected Rep", vbOKOnly, "No Reps"
  51.         Exit Sub
  52.     End If
  53.  
  54. Exit_cmdCreateBHPDFs_Click:
  55.     Exit Sub
  56.  
  57. Err_cmdCreateBHPDFs_Click:
  58.     MsgBox Err.Description
  59.     Resume Exit_cmdCreateBHPDFs_Click
  60.  
  61. End Sub
Oct 18 '06 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,627
Hey Gurus - I've seen a few solutions to this problem, but none of which I can do without a little help. Here's the situation

The following code loops thru a sales report, using the sales rep ID as a filter so that multiple reports are created. This is now generating a PDF report, but I need to change it to RTF documents.

My question is, since I can't create an RTF using open report, is there a way to use the strFilter with the OutputTo method?

Any ideas appreciated!!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreateBHPDFs_Click()
  2.  
  3. On Error GoTo Err_cmdCreateBHPDFs_Click
  4.  
  5.  
  6.     ' Create Variables for Subroutine
  7.  
  8.     Dim strDocName As String    'Report Name
  9.     Dim db         As Database  'Database to retrieve data from
  10.     Dim strSql     As String    'SQL Statement
  11.     Dim rst        As Recordset 'Data Recordset
  12.     Dim strFilter  As String    'Report Filter
  13.  
  14.  
  15.     ' Set Values for Variables
  16.     strDocName = "ReportBHRepTotDetailPDF"
  17.     strSql = "SELECT SALESREP_ID FROM qryCurrentReps ORDER BY SALESREP_ID"
  18.     Set db = CurrentDb
  19.  
  20.     ' Open recordset containing list of Sales Reps
  21.     Set rst = db.OpenRecordset(strSql)
  22.  
  23.     ' Test recordset and act accordingly
  24.     If Not rst.EOF Then
  25.     ' A list of Sales Reps Exists, print the reports
  26.  
  27.         'Advance through the Sales Rep list, one at a time
  28.  
  29.         'Go to first record of recordset
  30.         rst.MoveFirst
  31.  
  32.         'Setup loop to loop through Sales Rep List
  33.         Do Until rst.EOF
  34.  
  35.             'Set the report filter property to the SaleRep in the recordset, zero base array
  36.             strFilter = "SALESREP_ID = '" & rst(0) & "'"
  37.  
  38.             'Print the report using the report name and filter variables
  39.             DoCmd.OpenReport strDocName, , , strFilter
  40.  
  41.  
  42.         'Move to next Sales Rep
  43.         rst.MoveNext
  44.  
  45.         'Loop back to top of Do Until
  46.         Loop
  47.  
  48.     Else
  49.     ' No Sales Reps Exist, inform the user and exit subroutine
  50.         MsgBox "No Sales Data for selected Rep", vbOKOnly, "No Reps"
  51.         Exit Sub
  52.     End If
  53.  
  54. Exit_cmdCreateBHPDFs_Click:
  55.     Exit Sub
  56.  
  57. Err_cmdCreateBHPDFs_Click:
  58.     MsgBox Err.Description
  59.     Resume Exit_cmdCreateBHPDFs_Click
  60.  
  61. End Sub
I haven't actually tried all of it but this should work:
1) Open the Base report in Design View/Hidden
DoCmd.OpenReport "Report1", acViewDesign, , , acHidden

2) Modify the RecordSource property of the Report to reflect the
appropriate Filter
Reports![Report1].RecordSource = "YourFilter"

3) Close the Report
DoCmd.Close acReport, "Report1", acSaveYes

4) Now you can use the OutputTo Method to generate the Report in RTF
format. I'm not sure about repeating this process within a Loop
DoCmd.OutputTo .......

5) Repeat Filtering process within the Loop

Hope this somehow helps.
Oct 18 '06 #2

P: 1
This worked for me but I had to slight mods:

- Instead of using RecordSource, I used Filter.
- Report Definition defaults to 'Apply Filter On Load'=No. Once I changed this to Yes, it worked like a charm.
Dec 7 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.