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

Limit Report by combo box selection

P: 2
I have created a form in Access, similar to a switchboard in which I want to print reports, and I can do this. However, I want to print based on particular parameters; i.e. I have 2 combo boxes on the form and two comand buttons. Each combo box contains the same information; i.e. active, closed, cancelled, etc. but for a different report. I want to be able to run a report based on the parameter you click on within the combo box; i.e. if I select "Active", I want the report to only show "Active" projects. Can you help me? Thanks!
Dec 8 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,615
You don't need two ComboBoxes. One should do - just use it for each report.
Assuming :-
ComboBox = cboStatus
First command button = cmdReport1
Second command button = cmdReport2
Field in record to select by = Status
First report name = FirstReport
Second report name = SecondReport
Your code should be something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport1_Click()
  2.     Call DoReport("[FirstReport]")
  3. End Sub
  4.  
  5. Private Sub cmdReport2_Click()
  6.     Call DoReport("[SecondReport]")
  7. End Sub
  8.  
  9. Private Sub DoReport(strReport As String)
  10.     Dim strWhere As String
  11.  
  12.     strWhere = "[Status]='" & cboStatus & "'"
  13.     Call DoCmd.OpenReport(strReport, acViewPreview, ,strWhere)
  14. End Sub
Dec 8 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
"The title of this thread has been changed to make the problem clearer to experts and others searching for similar threads."

MODERATOR
Dec 9 '06 #3

P: 2
Please let me know if you see anything wrong with my code. I am unable to view the reports when selecting an action from the "combo box"; i.e. active, closed and clicking on the cmd button for reports 1 or 2. Thank you for your help!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport1_Click()                            'Name in Properties Box'
  2.  
  3.     Call DoReport("[Dashboard Reports]")               'Actual Name of Report'
  4.  
  5. End Sub
  6.  
  7. Private Sub cmdReport2_Click()                            'Name in Properties Box'
  8.  
  9.     Call DoReport("[Weekly Status Update]")           'Actual Name of Report'
  10.  
  11. End Sub
  12.  
  13. Private Sub DoReport(strReport As String)
  14.     Dim strWhere As String
  15.  
  16.     strWhere = "[Project Status]='" & cboProjectStatus & "'"    'Field in Table'
  17.     Call DoCmd.OpenReport(ReportName:=strReport, _
  18.                           View:=acViewPreview, _
  19.                           WhereCondition:=strWhere)
  20. End Sub
Dec 12 '06 #4

NeoPa
Expert Mod 15k+
P: 31,615
I see no problems with your code.
What does happen (you've told us it doesn't work but nothing else)?
Any error message?
Does it go into debug mode?
Do you get an empty report?
Add the line
[code]Debug.Print strWhere[code]
into your code just after you set it up in the DoReport procedure then post the results in here please.
Dec 12 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Remove 'Call' from before DoCmd.OpenReport as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport1_Click()                            'Name in Properties Box'
  2.  
  3.    Call DoReport("[Dashboard Reports]")               'Actual Name of Report'
  4.  
  5. End Sub
  6.  
  7. Private Sub cmdReport2_Click()                            'Name in Properties Box'
  8.  
  9.    Call DoReport("[Weekly Status Update]")           'Actual Name of Report'
  10.  
  11. End Sub
  12.  
  13. Private Sub DoReport(strReport As String)
  14.     Dim strWhere As String
  15.  
  16.    strWhere = "[Project Status]='" & Me.cboProjectStatus & "'"    'Field in Table'
  17.    DoCmd.OpenReport(ReportName:=strReport, View:=acViewPreview, _
  18.    WhereCondition:=strWhere)
  19.  
  20. End Sub
  21.  
Mary
Dec 12 '06 #6

NeoPa
Expert Mod 15k+
P: 31,615
Remove 'Call' from before DoCmd.OpenReport as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport1_Click()                            'Name in Properties Box'
  2.  
  3.    Call DoReport("[Dashboard Reports]")               'Actual Name of Report'
  4.  
  5. End Sub
  6.  
  7. Private Sub cmdReport2_Click()                            'Name in Properties Box'
  8.  
  9.    Call DoReport("[Weekly Status Update]")           'Actual Name of Report'
  10.  
  11. End Sub
  12.  
  13. Private Sub DoReport(strReport As String)
  14.     Dim strWhere As String
  15.  
  16.    strWhere = "[Project Status]='" & Me.cboProjectStatus & "'"    'Field in Table'
  17.    DoCmd.OpenReport(ReportName:=strReport, View:=acViewPreview, _
  18.    WhereCondition:=strWhere)
  19.  
  20. End Sub
  21.  
Mary
If you want to remove the 'Call' from a procedure call (I would certainly not recommend that) then you must remove the parentheses () from the parameter list.
Dec 12 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
If you want to remove the 'Call' from a procedure call (I would certainly not recommend that) then you must remove the parentheses () from the parameter list.
Sorry Ade

Misread the format and didn't look at the parameters, sorry.

Mary
Dec 12 '06 #8

NeoPa
Expert Mod 15k+
P: 31,615
Sorry Ade

Misread the format and didn't look at the parameters, sorry.

Mary
No call for the Mea Culpa Mary. We're all trying to help here.
You of all people haven't anything to be sorry for.

-Ade.
Dec 12 '06 #9

NeoPa
Expert Mod 15k+
P: 31,615
It's possible that OpenReport is one of those painful (MS provided) procedures that can't accept named parameters.
Well, what do you know, it is (Just tested it out).
Please revisit the updated post #2
Dec 12 '06 #10

Post your reply

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