Custom combo box prompting

M

mscertified

I have a table of key words which is three levels in separate columns. I want
the user to be able to type characters into the combo box and as each
character is typed to match on the letters of any of the three columns e.g.

KW1=Software KW2=Office KW3=Word
KW1=Software KW2=Office KW3=Outlook
KW1=Software KW2=Windows


If the user types 'O', I want the first two records to appear
If the user types 'W', I want the first and third records to appear
If the user types 'WO', I want the first record to appear
If the user types 'S', I want all three records to appear

Has anyone done anything like this? Thanks.
 
J

John Nurick

I can't think of a remotely simple way of doing this.

The Autoexpand feature of Access combo boxes only works on the first
visible column in the combo box, so you can't display your three columns
of keywords and have Autoexpand work on all three of them.

One approach would be to use a union query to give the combo box a
4-column recordsource that (using your sample data) would look like
this:

LU KW1 KW2 KW3
Office Software Office Word
Office Software Office Outlook
Outlook Software Office Outlook
Software Software Office Outlook
Software Software Windows
Software Software Office Word
Windows Software Windows
Word Software Office Word

All four columns would have to be visible, of course, but you'd only be
able to type and auto-expand the first column.

Alternatively, you might be able to ditch Autoexpand and use a 3-column
combo box. You'd have to use code in its Change event procedure to build
the SQL statement for the recordsource with each keystroke; the result
would need to look something like this:

SELECT KW1, KW2, KW3
FROM MyTable
WHERE KW1 LIKE 'Wor*'
OR KW2 LIKE 'Wor*'
OR KW3 LIKE 'Wor*'
ORDER BY KW1, KW2, KW3
;
 
Top