How to use reports with comboxes Access 2002

[mickey_lin_uk]

I'm trying to get 3 combos on a form to select the records to be shown in a report. have used some VB code on a command button, which should run the code & bring up the report.
code is:
Private Sub cmdapplyfilter_Click()
Dim stryourname As String
Dim Strcustomer As String
Dim strstatus As String
Dim strFilter As String

'Code to open report automatically
If SysCmd(acSysCmdGetObjectState, acReport, "rptRFQ receipt to Tende Sent") <> acObjStateOpen Then
DoCmd.OpenReport "rptRFQ receipt to Tende Sent", acViewPreview
End If

'Build Criteria string for your name field
If IsNull(Me.cboyourname.Value) Then
stryourname = "Like '*'"
Else
stryourname = "='" & Me.cboyourname.Value & "'"
End If

'Build Criteria string for customer field
If IsNull(Me.cbocustomer.Value) Then
Strcustomer = "Like '*'"
Else
Strcustomer = "='" & Me.cbocustomer.Value & "'"
End If

'Build Criteria string for status field
If IsNull(Me.cbostatus.Value) Then
strstatus = "Like '*'"
Else
strstatus = "='" & Me.cbostatus.Value & "'"
End If

'Combine criteria strings into WHERE clause for the filter
strFilter = "[yourname] " & stryourname & " AND [customer] " & Strcustomer & " AND [status] " & strstatus

'Apply the filter and switch it on
With Reports![rptRFQ receipt to Tende Sent]
.Filter = strFilter
.FilterOn = True
End With

End Sub

Once the criteria is selected (name, customer, status) & the command button is press the report is opened but it brings up 2 parameter value boxes-name & status. No-matter what data has been selected in the combos or put into parameters the report is blank. I also don't know why only 2 parameter value boxes appear.

Thanks
Michelle

 

[dcx693]

What type of data is contained in the bound column of the 3 combo boxes? Are they all text, or numeric possibly?

 

[mickey_lin_uk]

they are text.

M

 

[mickey_lin_uk]

i've just realised im in the queries section should have posted this in the report section!! oh well, any help would be appreciated.

 

[dcx693]

And you're sure the report's source query (or table) contains the fields: [yourname], [customer] and [status] ?

 

[Bat17]

open up your query and make sure there are no critria built into it, also rather than controlling the filers after opening the report up pas the criteria in the openreport command
DoCmd.OpenReport "rptRFQ receipt to Tende Sent", acViewPreview, , strFilter


HTH

Peter

 

[mickey_lin_uk]

Thanks managed to get it working.

M