Limit Report by combo box selection

[DeeMing]

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!

[NeoPa]

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 :

Private Sub cmdReport1_Click()
    Call DoReport("[FirstReport]")
End Sub
 
Private Sub cmdReport2_Click()
    Call DoReport("[SecondReport]")
End Sub
 
Private Sub DoReport(strReport As String)
    Dim strWhere As String
 
    strWhere = "[Status]='" & cboStatus & "'"
    Call DoCmd.OpenReport(strReport, acViewPreview, ,strWhere)
End Sub

[MMcCarthy]

"The title of this thread has been changed to make the problem clearer to experts and others searching for similar threads."

MODERATOR

[DeeMing]

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!

Private Sub cmdReport1_Click()                            'Name in Properties Box'
 
    Call DoReport("[Dashboard Reports]")               'Actual Name of Report'
 
End Sub
 
Private Sub cmdReport2_Click()                            'Name in Properties Box'
 
    Call DoReport("[Weekly Status Update]")           'Actual Name of Report'
 
End Sub
 
Private Sub DoReport(strReport As String)
    Dim strWhere As String
 
    strWhere = "[Project Status]='" & cboProjectStatus & "'"    'Field in Table'
    Call DoCmd.OpenReport(ReportName:=strReport, _
                          View:=acViewPreview, _
                          WhereCondition:=strWhere)
End Sub

[NeoPa]

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.

[MMcCarthy]

Remove 'Call' from before DoCmd.OpenReport as follows:

Private Sub cmdReport1_Click()                            'Name in Properties Box'
 
   Call DoReport("[Dashboard Reports]")               'Actual Name of Report'
 
End Sub
 
Private Sub cmdReport2_Click()                            'Name in Properties Box'
 
   Call DoReport("[Weekly Status Update]")           'Actual Name of Report'
 
End Sub
 
Private Sub DoReport(strReport As String)
    Dim strWhere As String
 
   strWhere = "[Project Status]='" & Me.cboProjectStatus & "'"    'Field in Table'
   DoCmd.OpenReport(ReportName:=strReport, View:=acViewPreview, _
   WhereCondition:=strWhere)
 
End Sub
 

Mary

[NeoPa]

Remove 'Call' from before DoCmd.OpenReport as follows:

Private Sub cmdReport1_Click()                            'Name in Properties Box'
 
   Call DoReport("[Dashboard Reports]")               'Actual Name of Report'
 
End Sub
 
Private Sub cmdReport2_Click()                            'Name in Properties Box'
 
   Call DoReport("[Weekly Status Update]")           'Actual Name of Report'
 
End Sub
 
Private Sub DoReport(strReport As String)
    Dim strWhere As String
 
   strWhere = "[Project Status]='" & Me.cboProjectStatus & "'"    'Field in Table'
   DoCmd.OpenReport(ReportName:=strReport, View:=acViewPreview, _
   WhereCondition:=strWhere)
 
End Sub
 

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.

[MMcCarthy]

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

[NeoPa]

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.

[NeoPa]

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