Need Help on Filtering records using Combo or List Box (1 Viewer)

Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.

I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.

First Approach:
I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria.
This is the code I used:
_________________________________________________________________
Private Sub Preview_Click()

DoCmd.OpenForm "qrysumcountry subform", , "Country", "Country = [List4]"

End Sub
_________________________________________________________________
But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?

Second Approach:
On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.

This is the code I followed:
_________________________________________________________________
Private Sub btnClear_Click()
Dim intIndex As Integer
Me.cmbCountry = 0

End Sub

Private Sub btnsearch_Click()
Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter

Me.frmqrybyCountry1.Requery

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter


'Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function
_________________________________________________________________
After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.

Please help me, I would really want to figure this problem out. Thank you so much.

Have you seen the following method for filtering using Combo boxes? - I use it all the time and it works well.

CrystalSurfer said:

Have you seen the following method for filtering using Combo boxes? - I use it all the time and it works well.

Click to expand...

Hi CrystalSufer,

I just don't know how to thank to you the file which you have uploaded is awesome really you saved my time a lot thank you thank you so much n thanks a lot.

Please keep upload such a great prg.