Combo box multiple column search help (1 Viewer)

bumbolicious · May 8, 2019

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 · May 8, 2019

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

bumbolicious · May 8, 2019

Hello there!

Thank you very much for the welcome!

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

theDBguy · May 8, 2019

bumbolicious said:
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 · May 8, 2019

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

theDBguy · May 8, 2019

bumbolicious said:
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 · May 9, 2019

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 · May 9, 2019

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...

Code:

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

Micron · May 9, 2019

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 · May 10, 2019

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.

Combo box multiple column search help (1 Viewer)

bumbolicious

New member

theDBguy

I’m here to help

bumbolicious

New member

theDBguy

I’m here to help

bumbolicious

New member

theDBguy

I’m here to help

sxschech

Registered User.

Bullschmidt

Freelance DB Developer

Micron

AWF VIP

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread