Full Version: Filter Records Based On Combo Box Selection
UtterAccess Discussion Forums > Microsoft® Access > Access Searching + Data Mining
praveenvatsya
CODE
Private Sub MyCombo_AfterUpdate()

Dim 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
    Me.Client_Name.SetFocus
    '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....

Regards

Praveen
trapperalexander
'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?
praveenvatsya
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

Basically 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.
trapperalexander
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]

also, 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.
praveenvatsya
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....

Regards

Praveen
trapperalexander
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
praveenvatsya
Hi

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
Else
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......


thanx

Praveen
John Spencer
Try using the date delimiter (#) instead of the string delimiter.

Me.Filter = "ActivityDate = #" & Me.Combo20.Column(1) & "#"

That may give you problems since dates can be tricky. Check out
International Dates in Access at:
http://allenbrowne.com/ser-36.html
praveenvatsya
Hi john

thankyou so much for ur reply.

i hv 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

praveen
praveenvatsya
If IsNull(Me.Combo22) Then
Me.FilterOn = False
Else
Me.Filter = "Client_Name = " & Me.Combo22.Column(1) & ""
Me.FilterOn = True
End If

i 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) & ""

or 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.

regards

praveen
trapperalexander
delimit text with a single quote '

Me.Filter = "Client_Name = '" & Me.Combo22.Column(1) & "'"
praveenvatsya
Hi Trapper,

I 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.

i gt problem in client name and volunteer name filtration. codes i have used mentioned in above comments.

please help..... !!!!!
praveenvatsya
http://rapidshare.com/files/446797767/Screen_Shots.rar

Above link provide the screen shots of my form and a table.

please go through that images and help me.....!!!!!



Regards

Praveen
trapperalexander
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.