Filter Using a Wildcard

[mswilson16]

I have 2 forms, Appointment and Customer. When a customer name is entered in the Appointment form I want the Customer form to open and find the appropriate customers information.

At the moment I have an Afterupdate statement that opens the form and filters it to what is in the surname field. This works fine, but, it only filters on full surnames. I need the filter to work if the user only enters part of the name.

e.g. if I enter “wil” I want the filter to find names such as Wilson, Wilkinson, etc,

Thanks in advance.

 

[Zeroanarchy]

you have asked the same question as I have asked. anyway this is where I am up to but unfortunetly I can not work out what the error is in the code.

The idea is to add it to your field on change.

Me.Combo224.RowSource = "SELECT DISTINCTROW names([Surnames_Name], " & Len(Combo224.Text) & &quot Like '" & Combo224.Text & "*'"
Me.Combo224.Requery

Anyway if someone comes up with the solution we will both have the answer.

Zero

 

[mswilson16]

zero

It seems that you are trying to filter a combo box. I dont want to do that I want to open the customer form and then filter the surname field within that form (with the filter being based on the surname field in the appointments form).

Wilson

 

[Zeroanarchy]

THis example allowes you to filter a field by a choice of a combo box.

eg. if say you had a list of customers and you wanted to filter the form on customers. make the combo box a list of customers which are stored in the field.
the field = controlsouce. customers from the customers table.

'After Update Event of the Filter for Customer Combo Box
DoCmd ApplyFilter , "[field] = Forms![combobox]![combobox]"

Hope it is of some help to u.

Zero

 

[jebry]

Hi!

Try this:

DoCmd.OpenForm "YourCustomerForm", , "Surname Like '*" & YourTextBox.Value & "*'"

I'm not sure if the single quotes are needed but I think they are. If you want to get a little fancier you can put an option group on the form and let them choose to look at any part of the surname field, where they type in what the name begins with or let them match the whole field:

Dim strCriteria As String

Select Case OptionGroup.Value
Case 1
"Surname Like '*" & YourTextBox.Value & "*'"
Case 2
"Surname Like '" & YourTextBox.Value & "*'"
Case 3
"Surname = '" & YourTextBox.Value & "'"
Case Else
Call MsgBox("You must choose a search method&quot
End Select

DoCmd.OpenForm "YourCustomerForm", , strCriteria

hth Jeff Bridgham
bridgham@purdue.edu

 

[mswilson16]

cheers jeff,

I didn't use the exact code that you gave me but you put me on the right tracks.

Wilson.

p.s. if you want to know the code I used it is
Dim stLinkCriteria2 As String
stLinkCriteria2 = "[Surname] like '" & Me![Surname]& "*" & "'"
DoCmd.OpenForm "customer/medical", , , stLinkCriteria2

 

[mswilson16]

cheers jeff,

I didn't use the exact code that you gave me but you put me on the right tracks.

Wilson.

p.s. if you want to know the code I used it is
Dim stLinkCriteria2 As String
stLinkCriteria2 = "[Surname] like '" & Me![Surname]& "*" & "'"
DoCmd.OpenForm "customer", , , stLinkCriteria2