Filter Report by Combo Value

[brsawvel]

Below is a code I'm trying to use to filter a report by the value in Combo14.

The value in Combo 14 is determined by the value in Combo 8 (cascading)

The code I'm using causes an "object required" error.

On Error GoTo Err_cmdReport_Click

     Dim stDocName as String

If Me.Combo8.Value = "Functional Area" Then

     stDocName = "rptReport"
     DoCmd.OpenReport stDocName, acPreview, , rptReport.fldFuncArea = Me.Combo14.Value

End If

If Me.Combo8.Value = "Title" Then

     stDocName = "rptReport"
     DoCmd.OpenReport stDocName, acPreview, , rptReport.fldTitle = Me.Combo14.Value

End If

Exit_cmdReport_Click:
     Exit Sub

Err_cmdReport_Click:
     MsgBox Err.Description
     Resume Exit_cmdReport_Click

End Sub

 

[Trevor G]

Why not have the source for the report to set to a query that uses the criteria of the combo box thats on the form.

Trevor

 

[SOS]

Actually your syntax is off.

This:

DoCmd.OpenReport stDocName, acPreview, , rptReport.fldFuncArea = Me.Combo14.Value


should be this:

DoCmd.OpenReport stDocName, acPreview, , "[fldFuncArea] =" & Chr(34) & Me.Combo14 & Chr(34)

and if fldFuncArea is numeric instead of text:

DoCmd.OpenReport stDocName, acPreview, , "[fldFuncArea] =" & Me.Combo14

 

[SOS]

Why not have the source for the report to set to a query that uses the criteria of the combo box thats on the form.

Trevor

Trevor:

Because using the Where Clause in the open of the report is way more powerful and you can have a SINGLE report and open it in MANY, MANY different ways.

 

[brsawvel]

Thanks SOS!

 

[SOS]

Thanks SOS!

No problem