Full Version: Filter Records Based On Combo Box Selection
UtterAccess Discussion Forums > Microsoft® Access > Access Searching + Data Mining
Private Sub MyCombo_AfterUpdate()
im ClientSelected As String
'Temporary variable
    ClientSelected = Me.MyCombo
    'Gets the client name from the combo box
    Me.MyCombo = Null
    'Resets the combo box
    Me.Filter = "Client_Name = '" & ClientSelected & "'"
    'Set the filter
    Me.FilterOn = True
    'Turn the filter on
    'Position the cursor onto the client name field
End Sub

I have this code to filter records among thousand of record.
I have a combo box containing name of all the client. i need to filter records based on combo box selection, i.e., when i select any name from combo box...i need to have all records related to that selected name.
Problem: when i execute this code...i didn't get any error but i get a input box written "Enter Parameter for Client_Name" instead of filtered records. Still when i write any name on that box and press ok i gt nothing but a single blank row.
plzzz help....
'client_name' should be a field name in your table. is it spelled correctly? does your table really have an underscore in the field name?
Actually Client_name is the name of the control. In table Client name is divided into two part first name and last name. I have concatenated first and last name and then add data to combo box for filtering
asically Client_Name is the name of the field on the form. All the records are present on the form. i need to filter record based on combo box selection.
if you use a filter, you have to reference a field or expression in the recordsource. the simplest way for you to do this would be to add an expression to your form's recordsource query: client_name: [firstname] & " " & [last name]
lso, when you reference a combo box you need to be aware of the combobox's columns. if the 'client_name' concatenation expression is the second column of your combo box's row source, you will need to reference that in your filter, like this: me.MyCombo.Column(1) - column property starts at 0, so the second column is actually referenced as column 1, the first column is column 0.
Hi Trapper
Thank you so much for your reply.....i will try what u hv mentioned.
By the way when i debugged my above code....i got the error on the line Me.FilterOn = True
is this make any sense??????.....
plz comment on this.....mean while i will try....what you have suggest to do....
you gotta take it one step at a time...you are probably getting this error because your filter string has an error, fix that error first and then you will probably see this error go away as well
Sorry for late reply......i was trying hard to solve my problem....but was not able to fix it.
I am going to explain you about the actual scenario.
i have a form in which i have three unbound combo boxes. 1. Date 2. Client Name 3. Volunteer Name i header section. den i have all the details in the main area of the form. i need to filter data according to these three combo boxes. suppose if i select one date, all data corresponding to selected date should be come on the screen. similarly for other two combo boxes.
now what i did.
1. i have used ' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TaskId] = " & Str(Nz(Me![Combo20], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
this code and it runs successfully....bt the problem is it only indicate the first occurrence of selected data as well as others data too. bt i need to have only that data that are related to selected item on the page.
den i used the code i hv mentioned earlier. bt couldn't fix my problem.
i have also used code
If IsNull(Me.Combo20) Then
Me.FilterOn = False
Me.Filter = "ActivityDate = """ & Me.Combo20.Column(1) & """"
Me.FilterOn = True
End If
but gt same problem....it will ask for enter parameter....if i press cancel then i gt debug option....nd if i press debug....focus goes to line Me.FilterOn = True
please help me......
John Spencer
Try using the date delimiter (#) instead of the string delimiter.
e.Filter = "ActivityDate = #" & Me.Combo20.Column(1) & "#"
That may give you problems since dates can be tricky. Check out
International Dates in Access at:
Hi john
hankyou so much for ur reply.
Ohv tried, wt u hv suggested and it worked perfectly fine.
thankyou so much....u saved my lyf <
can u plz suggest me what would i do..if i hv to filter data according to name...which has the datatype text in database.
thnx and regards
If IsNull(Me.Combo22) Then
Me.FilterOn = False
Me.Filter = "Client_Name = " & Me.Combo22.Column(1) & ""
Me.FilterOn = True
End If
hv used above code to filter data based on client name.
wen i run this code....i get an input box asking please enter the parameter for Client_Name.
if i write the name...i gt a blank page of data.
if i cancel that input box...i gt the option to debug....and gt an error on the line
Me.Filter = "Client_Name = " & Me.Combo22.Column(1) & ""
For sometime gt an error on the line
Me.FilterOn = true
please help me on this.......
P.S. Client_Name is the concatenated form of firstname and lastname of the client fetched by query from contact table in database.
delimit text with a single quote '
e.Filter = "Client_Name = '" & Me.Combo22.Column(1) & "'"
Hi Trapper,
have delimited text with '
but it was again asking for enter value parameter for Client_Name. wen i canceled the input box...and press debug....Me.FilterOn = True line get highlighted.
Header contains three unbound combo box (Activity Date, Client Name and Volunteer Name) for filter data present in detail section.
Activity Date is working fine.
Ogt problem in client name and volunteer name filtration. codes i have used mentioned in above comments.
please help..... !!!!!
bove link provide the screen shots of my form and a table.
please go through that images and help me.....!!!!!
try renaming your comboboxes so that their names do NOT have any spaces (ie ClientName)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.