Need Help On Filtering Records Using Combo Or List Box

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.

• Combo Boxes Filtering Or Populting Same Records
• Filtering A Subform Using A List Box
• Filtering A Subform Using A List Box
• Text Box Filtering A List Box //?
• List Box Of Reports - Filtering Result
• Filtering A Form Based Off A List Box?
• Filtering On Combo Text
• Filtering On Combo Boxes
• Filtering Employee Name From Combo Box.
• Complex Combo Box Filtering
• Filtering Out Items In Combo Boxes
• Combo Box Subform Filtering Problem
• Filtering Combo Boxes Within A Form
• Help Needed Filtering A Form Using Combo Boxes
• Filtering On A Combo Box Based Off A Autopopulate Field
• Preventing Combo List Opening On Not In List
• Filtering Records To Then Add A New One
• Filtering Records
• Filtering With 2 Combo Boxes And Then Populating Text/memo Fields
• Please Help. Question About Filtering On Reports With Combo Boxes, Many Linked Tables
• Filtering One In Thrity Records
• ------->>> Please Help On Filtering Out Duplicate Records.
• Security Logon And Filtering Records