469,344 Members | 6,305 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Use Filter string AND OutputTo together to create multiple reports

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
2 6339
ADezii
8,800 Expert 8TB
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
jpcyr
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.

Similar topics

8 posts views Thread by Kelly Sellers | last post: by
4 posts views Thread by John Galt | last post: by
2 posts views Thread by Ima Lostsoul | last post: by
6 posts views Thread by Steve Jorgensen | last post: by
4 posts views Thread by midlothian | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.