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

Run multiple reports using same set of criteria

rcollins
100+
P: 234
I have several reports that I want to run at the same time. I have them all set up to run one at a time using a start and end date for the criteria. I would like for there to be one button to run all five reports, since they will all have the same start and end date. Is this possible?
May 29 '07 #1
Share this Question
Share on Google+
6 Replies

NeoPa
Expert Mod 15k+
P: 31,770
You have posted this question in the Access Articles section in error.
I have moved it to the Access Forum for you.

MODERATOR.

*Edit* Mary beat me to it - Was I confused!
May 30 '07 #2

NeoPa
Expert Mod 15k+
P: 31,770
I have several reports that I want to run at the same time. I have them all set up to run one at a time using a start and end date for the criteria. I would like for there to be one button to run all five reports, since they will all have the same start and end date. Is this possible?
Yes it is. Simply write the code to open all five reports. Once a report has been opened, the code will continue on to the next step of code.
May 30 '07 #3

rcollins
100+
P: 234
Thank you for moving me.
Here is the code that Access gives as an example for filtering a form.
Expand|Select|Wrap|Line Numbers
  1. Sub SetFilter()
  2.     Dim frm As Form, strMsg As String
  3.     Dim strInput As String, strFilter As String
  4.  
  5.     DoCmd.OpenForm "Products"
  6.     Set frm = Forms!Products
  7.     strMsg = "Enter one or more letters of product name " _
  8.         & "followed by an asterisk."
  9.     strInput = InputBox(strMsg)
  10.     strFilter = BuildCriteria("ProductName", dbText, strInput)
  11.     frm.Filter = strFilter
  12.     frm.FilterOn = True
  13. End Sub
  14.  
Here, I modified it to filter on a report but get runtime error.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2.  
  3.     Dim rpt As Report, strMsg As String
  4.     Dim strInput As String, strFilter As String
  5.  
  6.     Set rpt = Report!rptDetailContact (this is where it errors)
  7.     strMsg = "Start Date"
  8.     strInput = InputBox(strMsg)
  9.     strFilter = BuildCriteria("Date", dbDate, strInput)
  10.     rpt.Filter = strFilter
  11.     rpt.FilterOn = True
  12.     DoCmd.OpenReport "rptDetailContact"
  13. End Sub
  14.  
What am I doing wrong? Also, how can I do the date filter to be greater than srnInput?

I had to put the report at the end or it would print the whole thing then prompt for a date.
Jun 1 '07 #4

rcollins
100+
P: 234
So, this worked for the filter
Expand|Select|Wrap|Line Numbers
  1.     Dim rpt As Report, strMsg As String
  2.     Dim strInput As String, strFilter As String
  3.  
  4.     ' Open Products form in Form view.
  5.         stDocName = "rptDetailContact"
  6.     DoCmd.OpenReport stDocName, acPreview
  7.  
  8.     Set rpt = Reports!rptDetailContact
  9.     strMsg = "Start Date"
  10.     strInput = InputBox(strMsg)
  11.     strFilter = BuildCriteria("Date", dbDate, strInput)
  12.     rpt.Filter = strFilter
  13.     rpt.FilterOn = True
  14.  
but I still need to be able to go by dates greater than the strInput value
Jun 1 '07 #5

NeoPa
Expert Mod 15k+
P: 31,770
So, this worked for the filter
Expand|Select|Wrap|Line Numbers
  1.     Dim rpt As Report, strMsg As String
  2.     Dim strInput As String, strFilter As String
  3.  
  4.     ' Open Products form in Form view.
  5.         stDocName = "rptDetailContact"
  6.     DoCmd.OpenReport stDocName, acPreview
  7.  
  8.     Set rpt = Reports!rptDetailContact
  9.     strMsg = "Start Date"
  10.     strInput = InputBox(strMsg)
  11.     strFilter = BuildCriteria("Date", dbDate, strInput)
  12.     rpt.Filter = strFilter
  13.     rpt.FilterOn = True
  14.  
but I still need to be able to go by dates greater than the strInput value
There is a filter parameter in the OpenReport function.
Try it this way :
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. strFilter = BuildCriteria("Date", dbDate, InputBox("Start Date"))
  4. ' Open "Detail Contact" report in Preview mode.
  5. Call DoCmd.OpenReport("rptDetailContact", acPreview, , strFilter)
You don't explain what 'BuildCriteria' does (I can guess roughly), so I used it as was. dbDate doesn't seem to be declared anywhere though, so you may still have problems with that.
Jun 2 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
BTW - If your field is called [Date] then you may have trouble with the criteria anyway. You may want (unless this is handled intelligently in BuildCriteria()) to change that (slightly) to :
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. strFilter = BuildCriteria("[Date]", dbDate, InputBox("Start Date"))
  4. ' Open "Detail Contact" report in Preview mode.
  5. Call DoCmd.OpenReport("rptDetailContact", acPreview, , strFilter)
The change is the []s in line 3.
Jun 2 '07 #7

Post your reply

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