"Search" either column in combo box?

[Amanda003]

Hello all!



I have a combo box in my form that has 3 columns, ID (hidden), Item# & Item Description. Right now I have to start typing the Item # for it to auto fill the field. I was wondering if there is a way that if I don't know the Item# off hand that I can "search" ie "Mailer?" I would like to be able to have it auto fill whether I type the number or text. I'm not sure if I am explaining this correctly so I have included a screen shot to help.

Thank you in advance!
Amanda

[June7]

Could have two comboboxes bound to the same field. One has Item# as the first visible field and other has ItemDescription as first. Use one or other for input.

[Amanda003]

I'm sorry, I am very new to Access and I'm not really understanding.

[June7]

What exactly is not clear? Having multiple controls bound to the same field is unusual but the only way I can see to easily accomplish what you want.

Keep the one combobox you already have.

Create another combobox next to it but switch the two fields in the RowSource sql.

[Amanda003]

To me, it doesn't make sense to have 2 combo boxes when all the information I want is in 1 box. I would like to be able to either start typing in either "10" or "Mail" in the same box.

[June7]

Unfortunately, comboboxes and list boxes do not work that way, at least not without some significant VBA code.

[Amanda003]

Well, darn it. So say I combined the columns to where all the info was all in 1 column, would there be a way to type/search any part the entry for it to auto complete or would I have to start at the beginning of the entry like I do now?

[June7]

Closest I have seen is VBA code that replicates the combobox AutoExpand in a textbox that allows instant filter/search of records as each character is typed. However, even in that case the character matching starts at beginning of value and applies on only one field. You essentially want to filter on either of two fields or the concatenated text of two fields.

This would require VBA code to execute after each keystroke in the box and requery the combobox RowSource using itself as the filter criteria in a wildcard expression. Never seen this although I expect the code would work. However, the results in continuous form will be unsatisfactory. Conditional comboboxes with alias don't work nice in continuous or datasheet view. This is because the filtering removes the record with alias value from the RowSource so records will appear blank in that field because the alias is not available for display.

[Amanda003]

That all went completely over my head but thank you for your responses! I personally have no problem with how the database works right now but there is someone else in the office that is never happy with anything so I thought I would ask but they can just deal with it.

Thank you again!