Apply filter to a form

[Guest]

I would like to filter all my records using textboxes and command buttons,
instead of getting the user to right-click on the appropriate field.

I have:-
txtName = persons name relating to each record
txtFilter = filter criteria
cmdFilter = filter command button
Upon clicking the cmdFilter button, filter all records in the txtName field
with the criteria in the txtFilter field.

Eg.
If txtFilter = "mit" then all records where the name includes 'mit' are
shown.. Mitchell, Smith and Smit etc. Bearing in mind I also need to
incorporate a wildcard at each end too.

So far i have tried variations around this but cant quite seem to find the
right syntax.

Me.Filter = "[txtName]= ' * & txtsearch & *'"

Promptly followed by:
Me.FilterOn = True

Anyone got any pointers please ???

 

[Rick Brandt]

I would like to filter all my records using textboxes and command
buttons, instead of getting the user to right-click on the
appropriate field. [snip]

So far i have tried variations around this but cant quite seem to
find the right syntax.

Me.Filter = "[txtName]= ' * & txtsearch & *'"

Promptly followed by:
Me.FilterOn = True

Anyone got any pointers please ???

If you want to use wild card characters then you have to use Like instead of
=. Plus you were missing a quote.

Me.Filter = "[txtName] Like '*" & txtsearch & "*'"
Me.FilterOn = True

 

[Guest]

Use the Like operator, not the = operator. As a literal, it should look like
this:

Like "*mit*"

As an alternate to using a filter, you can just build the filter into the
form's RecordSource query by specifying a reference to the contents of the
control (that is used to filter the data) in the criteria of the various
fields. That way, all you need to do is requery the form when one of the
filter boxes is populated (i.e. you do not have to rebuild the filter in
VBA). I do not know if this is more or less efficient than using Filter, but
I use it more often than filters. Something like this would go into the
criteria section of, say, the CompanyName field:

Like [Forms]![Form1]![CompanyFilter]

This requires an entry in CompanyFilter, so I usually set it up so that if
CompanyFilter is Null, all records appear (no criteria specified if Null),
but that if CompanyFilter is not null, then CompanyName must = (or Like)
CompanyFilter.

 

[Bas Cost Budde]

me.filter = txtname & " Like '*" & txtsearch & "*'"

If you have any 'invalid' field names, use the brackets:

me.filter = "[" & txtname & "] like '*" & txtsearch & "*'"

I went as far as to have the user specify the comparator (Like) as well,
but you don't have to

 

[David C. Holley]

Try Me.Filter = "[txtName] Like '*' & txtSearch & "'"

The original filter string should have used the LIKE operator and the
criteria was a bit wacked in terms of the quotes.

 

[Guest]

OK. I have changed it to LIKE.. a pretty obvious starting flaw i suppose.

Now without even pressing my new filter button, all I get each time I move
to a different record is the msgbox:

"The expression On Current you entered as the event property setting
produces the following error: Member already exists in an object module from
which this module derives."

.... and even though I remove all the code from the On Current section, the
message still shows !!

Try Me.Filter = "[txtName] Like '*' & txtSearch & "'"

The original filter string should have used the LIKE operator and the
criteria was a bit wacked in terms of the quotes.

I would like to filter all my records using textboxes and command buttons,
instead of getting the user to right-click on the appropriate field.

I have:-
txtName = persons name relating to each record
txtFilter = filter criteria
cmdFilter = filter command button
Upon clicking the cmdFilter button, filter all records in the txtName field
with the criteria in the txtFilter field.

Eg.
If txtFilter = "mit" then all records where the name includes 'mit' are
shown.. Mitchell, Smith and Smit etc. Bearing in mind I also need to
incorporate a wildcard at each end too.

So far i have tried variations around this but cant quite seem to find the
right syntax.

Me.Filter = "[txtName]= ' * & txtsearch & *'"

Promptly followed by:
Me.FilterOn = True

Anyone got any pointers please ???