Combo box multiple column search help

[bumbolicious]

Hello all,


I've got a Combo Box that I want to type any value from the columns called NAME and SURNAME that are in the table called CONTACTS and get the results without having to create separate Combo Boxes for NAME and SURNAME.


How can I do that? I've been searching the internet with no avail though.


Thank you in advance!

 

[theDBguy]

Hi. Welcome to the forum. When you say "results," are you referring to the records on the form or just the combobox?

 

[bumbolicious]

Hello there!

Thank you very much for the welcome!


Well, my bad, with "results" I meant the records.

 

[theDBguy]

Hello there!

Thank you very much for the welcome!


Well, my bad, with "results" I meant the records.

So, if this means you're using the combo to filter the form, then you really don't need to use a combo. You could use a textbox and enter either names in it. In the AfterUpdate event, or whichever event you're using, just include both fields in your search criteria.

 

[bumbolicious]

Well, I'd like to do it with combo box if there's a way of course.

 

[theDBguy]

Well, I'd like to do it with combo box if there's a way of course.

Something about the right tools for the right jobs...

 

[sxschech]

Search the forum for Find as You Type or FAYT, a function that allows you to type letters in the combo box so if you know the last name you can begin typing the letters for that and it will find it as well as if you entered the letters for the first name.

 

[Bullschmidt]

How about something like this:

Assuming that you already have the combo box working the way you want (perhaps using UNION and/or DISTINCT etc.) then for the SQL to produce the proper records...

SELECT * FROM Contacts WHERE (Contacts.Name = Forms!MyForm!MyCombo) OR (Contacts.Surname = Forms!MyForm!MyCombo)

 

[Deleted member 147267]

If combo isn't bound, you can't type in it to search without code. Even then, 2 table fields are involved, so which one would you search based on "find as you type"?

If combo IS bound (which wouldn't make sense if it's to be used as a search tool), again, which field would you search on? It could only be the bound field. It makes no sense to allow last name search on a first name field, and 2 field search is the goal.

Regardless if combo or textbox, one isn't going to work.

 

[MajP]

Use my FAYT combobox. Turns any combo into a find as you type
https://www.access-programmers.co.uk/forums/showthread.php?t=300198

You want to concatenate in the query first and last name. Use that the concatenated field as your search field.

That way if you type "Jo" or "Sm" it will include John Smith.