filter for text within field in Combo Box

[swenger]

I have a Table which I am using as the data for a Combo Box on a form. Is there a way to be able to type text in the combo box to filter the records by but for anywhere in the field and not just the starting. Similar to in excel how if you have the filter on, you can type characters and it automatically filters for rows that have those characters anywhere in the record.

Thanks,
Sam

[Micron]

I have a Table which I am using as the data for a Combo Box on a form.
I think you mean the table is the combo row source. If it's bound, you could mean values entered into the combo go into the table, but I presume the former.
You could do what you want by using the OnChange event and actively filter the list by running a sql statement in code which uses wildcards around the text being typed in. However, I think you will take a noticeable performance hit if the records being filtered are on the large side, plus the event will run on each key press. It might start to look clunky and not respond fast enough to the user's input.

IMHO, a combo is usually used to supply a list of choices that are not so large or complicated as to need wildcard filtering.

[swenger]

Would you have a suggestion that would accomplish this with efficient resources.

I have a table of Inventory parts. Approximately 600 items which have a part number and a description.

I have another Table which has order details which records the qty and part number.

I have a form used to complete the order information. Sometimes the person doing the data entry doesn't know the part number or the exact description but might know that it has the word "Table" as part of the description. How can I make it that on the row of entering the part, they can find it with a wildcard search?

Thanks,

Sam

[Micron]

Had a similar situation once, trying to find a past PO that could have been created but the department, PO number or item number was not properly identified or known. I searched on the work description field based on user input (search words) in a separate search form. I provided basic instruction with examples in a popup modal form if required. After validating dates, removing any beginning or ending commas and ensuring the search term text box was not Null, I looped through the input words (separated by commas) passing each to a function that concatenated these into a LIKE statement, surrounding each with the asterisk wildcard. I appended the LIKE statement to the WHERE part of the sql and ran the sql. The records returned were for PO's that the work field contained the search word(s) or term. If the user used a semicolon for a delimeter, too bad - read the help form as the search form label suggests. In your case, you could return something that tells the user what the part number is based on the description, or automatically filter the combo based on the result. I think no result would be confusing, so allowing them to say, select a checkbox in a list of parts on a continuous form and clicking a button would be better way of passing the part number to your main form. Or have the list returned to a list box on the search form and allow the user to scroll/pick from the list. However, I might be over complicating this. I might try the first suggestion on 600 records and see how it performs, knowing that if the list will grow, performance will decline. I

[Micron]

Had a similar situation once, trying to find a past PO that could have been created but the department, PO number or item number was not properly identified or known. I searched on the work description field based on user input (search words) in a separate search form. I provided basic instruction with examples in a popup modal form if required. After validating dates, removing any beginning or ending commas and ensuring the search term text box was not Null, I looped through the input words (separated by commas) passing each to a function that concatenated these into a LIKE statement, surrounding each with the asterisk wildcard. I appended the LIKE statement to the WHERE part of the sql and ran the sql. The records returned were for PO's that the work field contained the search word(s) or term. If the user used a semicolon for a delimeter, too bad - read the help form as the search form label suggests. In your case, you could return something that tells the user what the part number is based on the description, or automatically filter the combo based on the result. I think no result would be confusing, so allowing them to say, select a checkbox in a list of parts on a continuous form and clicking a button would be better way of passing the part number to your main form. Or have the list returned to a list box on the search form and allow the user to scroll/pick from the list. However, I might be over complicating this. I might try the first suggestion on 600 records and see how it performs, knowing that if the list will grow, performance will decline. In my case the number of records to search was more than 100,000 for sure. Can't remember exactly how many.
What form looked like:

[swenger]

You are correct that that would be a bit over complicated for my scenario. That makes sense and I have done that in the past when using it as a search feature like you have. However in my case where I am trying to incorporate it into a regular data entry it might be too much. See my screen below. I think what I may do is make a search button which would open a new screen where they can search with a wild card and then just copy the part number back to the order form.

[swenger]

Attachment didn't work in previous post

[Micron]

You are correct that that would be a bit over complicated for my scenario... I think what I may do is make a search button which would open a new screen where they can search with a wild card and then just copy the part number back to the order form.

Umm, I think that's what my suggestion was "...passing the part number to your main form."

Why do you show 2 combos for part number, yet one has a header label captioned Description?

[swenger]

The reason I have two combo boxes is that they use the same query, but one is sorted by the part number and the other by the description. This way if they know either the part number of the description they can look it up either way. The reason I can't use the pass option is that I am not that great at vba and wouldn't expect anyone to write the code I would need to pass it from one form to the other and save it in that field.

[swenger]

Found a solution

Probably not the best method.

I created a button on the order form which opened a new form with a search box to enter the criteria and then a search button which runs the query on the table to match the wildcard.

The form then displays all the matching record.

I created an event to doubleclick on the correct part number which saves the part number to a variable and closes the form

I created an event back on the order form for doubleclick to have the part number of the combo = to the variable.

I tested and it works.

Thanks,

[Micron]

They look like they are both bound to the part number field - not one to part and the other to description. Glad you solved it anyway.

[swenger]

They are both bound to the Part number, I just wanted two different sorts in case they knew the description or another knew the part number