Searching forms using combo boxes, with wildcards

[jobdone]

So I've got a form set up, and it uses a combo box to find the name of a persons record to populate the form. Simple enough stuff, the wizard takes you through it. It works fine.

However the people that use the database have kinda thrown me a curveball by asking if the search function can search any part of the name. For example, you've got a John Smith. If you enter Smith into the combo box, it won't find the record because it's the second name, you have to type in John.

Is there a way to use wildcards in the combobox so you can type in first or last names and get the same normal combo box effect?

Thanks,
Joe.

 

[CJ_London]

You can try this.

Have an unbound textbox called QuickFind

in the QuickFind on change event put the following

Private Sub QuickFind_Change()
    Me.Filter = "[FirstName] & '|' & [Surname] like '*" & replace(QuickFind.Text,"'","''") & "*'"
    Me.FilterOn = True
End Sub

If your list is displayed in the subform, change Me. to SubFormCtrlName.Form. on both lines

This can be extended to include other fields such as address, phone number, social security code etc

The pipe (|) is included to ensure you don't get a cross over from firstname to surname

The replace is included to allow users to look for names like O'Neil

 

[jobdone]

Sorry, I should've specified that "Name" is a single field.

Would the approach be the same, just

Private Sub QuickFind_Change()
    Me.Filter = "[Name] like '*" & replace(QuickFind.Text,"'","''") & "*'"
    Me.FilterOn = True
End Sub

instead?

 

[CJ_London]

Yes, but Name is a reserved word and will produce unexpected results, even with the square brackets

here is a link which will help

http://office.microsoft.com/en-gb/a...7-reserved-words-and-symbols-HA010030643.aspx

I strongly advise you change the name.

The benefit of the quickfind is the user can find anything with a few key entries, if you are only wanting to look at one field it may be a bit overkill!

 

[jobdone]

Oh yeah, don't worry, I stay away from the reserved words.

The actual name for the field is "ReferralName", was just using Name as an example.

If you think quickfind is overkill, do you have any suggestions for alternate methods? I don't need code specifically, just something to go research myself.

 

[CJ_London]

basically if you go back to your combo box, make sure limit to list is set to false you can use the quickfind code in the combobox afterupdate event - changing the name accordingly as below

Me.Filter = "[Name] like '*" & replace (me.combo,"'","''") & "*'"


Another possibility is to instruct your users in the use of the * and remove the *'s from the line of code so they would enter S* to get everone starting with S for example

 

[jobdone]

Brilliant.

Thanks for all the help mate