Filtering a Form

[JohnG]

Hi

I have a form that displays the relevant info about the
Customers, it is based on a query (QryCustomers).

I want my users to be able to filter the form based on
characters typed into a Textbox for instance:

if the user types: "Carl" in the Textbox I would like to
filter all the records with "Carl" anywhere in the
Customer_Name field to be displayed

When I use the formula:

Like "*[Carl]*"

in the Criteria column of the query it returns the
required records, but when I try to reproduce this in a
VBA module. i.e.

Dim Strfilter as string

Strfilter = "[Customer_Name] Like *[Me.searchName]*"
Me.filter "Strfilter"

I have tried various alternatives using the Ampersand &
and the Sigle quote ' , but each time I seem to get an
error(usaully a syntax error)

can anyone help?
thanks


ps I have also tried to create a Recordset but I run into
the same problem the compiler doesn't seem to want to
recognise the wildcard * character. In any case i'm still
trying to figure out how to asign the new recordset to an
opoen form?

 

[Terry]

In the AfterUpdate event of the textbox use:

Me.Filter= "'*" & Me.textBox & "*'" notice the single quotes to
delimit the text
Me.FilterOn=True

regards

 

[Guest]

Thanks for that Terry

altough this code is accepted by the from, as I can see
from the filter property of the form the from still
deplays all the records for the form and does not filter
the records on based on the filter criteria is there
something else I need to do?

Thanks

-----Original Message-----
In the AfterUpdate event of the textbox use:

Me.Filter= "'*" & Me.textBox & "*'" notice the single quotes to
delimit the text
Me.FilterOn=True

regards

Hi

I have a form that displays the relevant info about the
Customers, it is based on a query (QryCustomers).

I want my users to be able to filter the form based on
characters typed into a Textbox for instance:

if the user types: "Carl" in the Textbox I would like to
filter all the records with "Carl" anywhere in the
Customer_Name field to be displayed

When I use the formula:

Like "*[Carl]*"

in the Criteria column of the query it returns the
required records, but when I try to reproduce this in a
VBA module. i.e.

Dim Strfilter as string

Strfilter = "[Customer_Name] Like *[Me.searchName]*"
Me.filter "Strfilter"

I have tried various alternatives using the Ampersand &
and the Sigle quote ' , but each time I seem to get an
error(usaully a syntax error)

can anyone help?
thanks


ps I have also tried to create a Recordset but I run into
the same problem the compiler doesn't seem to want to
recognise the wildcard * character. In any case i'm still
trying to figure out how to asign the new recordset to an
opoen form?

.

 

[Devlin]

Note your examples:
Like "*[Carl]*"
Strfilter = "[Customer_Name] Like *[Me.searchName]*"

First, in each example you have enclosed your criteria in brackets. This
tells the engine that you are looking for any one of the characters within
the brackets. So eliminate the brackets.

Second, in your second example you have your form control written within a
string. So you are actually searching your recordset for any of the
characters contained within "Me.searchname."

Third, your search criteria appears to be a string so it must be enclosed in
quotes. To do this you must do string concantination and use either quote
literals (i.e. """)or the character code (i.e. Chr(34)).

Here is the syntax I believe you are looking for:

sFilter = "[FieldName] Like " & Chr(34) & "*" & Me.ControlName & "*" & Chr(34)

Me.Filter = sFilter
Me.FilterOn = True


Best of luck!