Using a filter on a form

[JoeFRB]

I have read a bunch of different posts about this subject, but I don't know how to get my solution started.

I have a Request_Info table which stores information different requests, such as date, name, type, etc. I have made a form with all of the fields added.

I would like to have a combo box with all of the different fields from the table as values for the list, then a text box where someone can enter the filter criteria. This would mean if someone would want to see all records on a certain date, they would select "date" field from the combo box, then enter the date in the text box. Then, a button click would apply a filter so the user can only see the records with that specific date. I know this can be done, but I'm not sure how. Any help would be greatly appreciated. Thank you.

-Joe

 

[PHV]

Something like this ?
You have to know the type of the field:
1) If text
sCrit = Me!theCombo & "='" & Me!theTextBox & "'"
2) If date
sCrit = Me!theCombo & "=#" & Format(Me!theTextBox, "m/d/yyyy") & "#"
3) If numeric
sCrit = Me!theCombo & "=" & Me!theTextBox

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JoeFRB]

I'm sorry to ask but where would I type all this?

 

[JoeFRB]

And what does sCrit mean?

 

[PHV]

but where would I type all this
In the Click event procedure of your button
And what does sCrit mean
This is a string variable holding the criteria you want to apply:
Me.Filter = sCrit
Me.FilterOn = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JoeFRB]

Ok, here's what I did.
I made a combo box and did the "Type the values that I want" option in the wizard. I named the combo box cboCrit. I then put all the different types of fields into the list. Next,I added a text box named txtSearch and a command button. I put this code into the event procedure:

Dim sCrit As String

sCrit = Me!cboCrit & "=" & Me!txtSearch & ""

Me.Filter = sCrit
Me.FilterOn = True

This is the text format. I'm using this to test if it will work initially. I select a field from the combo box, type the criteria into the text box, and press the button.

Essentially I would like it to automatically filter the records by the field that I selected and the criteria that i typed in.

But it doesn't work correctly. A prompt will come up asking for more criteria, and the label on the prompt will be the text that I initially typed into the txtSearch text box. It then filters out the right records if I type the criteria into the new prompt, but I shouldn't have to do that.

I don't know what is going on. Please help.

 

[PHV]

Does cboCrit return the name of a field in the form's underlaying query/table (ie RecordSource) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JoeFRB]

Yes it does. The values of the combo box are "date", "request type", "description", "name", etc. These are all fields in the request table.

 

[PHV]

As you use reserved keywords as fields name, try this:
1) If text
sCrit = "[" & Me!theCombo & "]='" & Me!theTextBox & "'"
2) If date
sCrit = "[" & Me!theCombo & "]=#" & Format(Me!theTextBox, "m/d/yyyy") & "#"
3) If numeric
sCrit = "[" & Me!theCombo & "]=" & Me!theTextBox

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244