Combo Boxes with wildcard Lookup

[phuser]

I would like to be able to use a wildcard * to look up inventory items in a
combo box.
*blue... It's a single item combo box.
would bring up every item that has the word blue in it.
Any suggestions?

 

[sebthirlway]

Hi

not sure by what you mean by "bring up every item". The combo can't
have multiple values, so I guess you mean that the combo should stop
showing its _entire_ list, and show just the matching items?

If so, it would be by far easier to have a separate text box for input
of your wildcards - otherwise you have to mess about with the combo's
Text property and On Change events, which interfere messily with the
combo's Before/After Update events.

Is your cbo based on a query? You could do it by putting something
like this in the text box's After Update event:

[combo box's name].Rowsource="SELECT AColumn From ATable WHERE AColumn
LIKE """ & Me.[text box name].Value & """"

where SELECT AColumn From ATable is the query for the combo's full
list. (nb if you're using SQL Server rather than Access you'd have to
adjust the syntax: single quotes instead of doubles, replace the * with
a &).

cheers


Seb

 

[phuser]

I guess my request is something that is beyond my scope, you have it right
by saying I would like to show all matching items containing the word input
after the *. I tried what you said but wasnt sure what to put in the
rowsource because in the "ProductID" the rowsource of the combobox contains
SELECT DISTINCTROW Products.* FROM Products ORDER BY Products.ProductName;
it's just that there are 3000 items & it requires a great deal of time to
just enter 1 item if your not sure of the productcode

 

[sebthirlway]

Do you mean that the combo takes a while to access all its rows, so
that browsing through the list isn't practicable?

You may be able to speed it up by changing the rowsource to just SELECT
DISTINCTROW ProductID, ProductName FROM Products ORDER BY Product Name.
Make sure the ColumnCount is 2 and the BoundColumn is 1 (assuming the
combo is designed for the user to select the ProductID, the first
column?).

In this case the Rowsource with the WHERE added in would be
SELECT DISTINCTROW ProductID, ProductName FROM Products WHERE
ProductName LIKE """ & [the text box name] & """".
(NB, to search for anything containing "blue" you'd have to type in
*blue*, not *blue - the latter will only finding things _ending_ in
"blue").
You'd have to put some scope for you to clear the contents of the text
box and view the whole list as well: e.g.
If Me.[name of text box]="" Then [name of
combobox].Rowsource=SELECT.....ORDER BY ProductName [without the WHERE
clause].

hope this helps.


Seb
ORDER BY Product Name

 

[phuser]

No browsing doesnt take a long time per say, it's just that if you dont know
exactly which letter the product starts with you have to put in A and scroll
through tons of products, then B etc. etc. to try and find the one your
looking for with auto fill you need to at least know the 1st letter, our
product codes are not as straight forward as one would hope. Having a
wildcard (*blue) that will lookup all products with the word blue in it
because you know the product at least contains blue in part.

 

[Guest]

Do you have the autoexpend property of the combo box set to Yes?
This way you just start typing and you will find it quickly. The Auto
Expand does this.
You type an A
you are positioned on the first entry that starts with A
You type Q
you are positioned on the first entry that starts with AQ
you type U
you are positoned on the first entry that starts with AQU
....
etc.

So even with 3000 entries, it can't take that long to find it.

No browsing doesnt take a long time per say, it's just that if you dont know
exactly which letter the product starts with you have to put in A and scroll
through tons of products, then B etc. etc. to try and find the one your
looking for with auto fill you need to at least know the 1st letter, our
product codes are not as straight forward as one would hope. Having a
wildcard (*blue) that will lookup all products with the word blue in it
because you know the product at least contains blue in part.

Do you mean that the combo takes a while to access all its rows, so
that browsing through the list isn't practicable?

You may be able to speed it up by changing the rowsource to just SELECT
DISTINCTROW ProductID, ProductName FROM Products ORDER BY Product Name.
Make sure the ColumnCount is 2 and the BoundColumn is 1 (assuming the
combo is designed for the user to select the ProductID, the first
column?).

In this case the Rowsource with the WHERE added in would be
SELECT DISTINCTROW ProductID, ProductName FROM Products WHERE
ProductName LIKE """ & [the text box name] & """".
(NB, to search for anything containing "blue" you'd have to type in
*blue*, not *blue - the latter will only finding things _ending_ in
"blue").
You'd have to put some scope for you to clear the contents of the text
box and view the whole list as well: e.g.
If Me.[name of text box]="" Then [name of
combobox].Rowsource=SELECT.....ORDER BY ProductName [without the WHERE
clause].

hope this helps.


Seb
ORDER BY Product Name

 

[phuser]

Yes, I do have the autoexpand set but it still does not help when you dont
know what letter the product starts with, lets say your looking for product
BPSOURPOP and you think the product is blowpop well you put in "bl" you dont
get what your looking for then try "po" ....nothing but if you could put in
"*pop", the drop down would then display all items that contain the word pop
in them would appear.

Do you have the autoexpend property of the combo box set to Yes?
This way you just start typing and you will find it quickly. The Auto
Expand does this.
You type an A
you are positioned on the first entry that starts with A
You type Q
you are positioned on the first entry that starts with AQ
you type U
you are positoned on the first entry that starts with AQU
...
etc.

So even with 3000 entries, it can't take that long to find it.

No browsing doesnt take a long time per say, it's just that if you dont
know
exactly which letter the product starts with you have to put in A and
scroll
through tons of products, then B etc. etc. to try and find the one your
looking for with auto fill you need to at least know the 1st letter, our
product codes are not as straight forward as one would hope. Having a
wildcard (*blue) that will lookup all products with the word blue in it
because you know the product at least contains blue in part.

Do you mean that the combo takes a while to access all its rows, so
that browsing through the list isn't practicable?

You may be able to speed it up by changing the rowsource to just SELECT
DISTINCTROW ProductID, ProductName FROM Products ORDER BY Product Name.
Make sure the ColumnCount is 2 and the BoundColumn is 1 (assuming the
combo is designed for the user to select the ProductID, the first
column?).

In this case the Rowsource with the WHERE added in would be
SELECT DISTINCTROW ProductID, ProductName FROM Products WHERE
ProductName LIKE """ & [the text box name] & """".
(NB, to search for anything containing "blue" you'd have to type in
*blue*, not *blue - the latter will only finding things _ending_ in
"blue").
You'd have to put some scope for you to clear the contents of the text
box and view the whole list as well: e.g.
If Me.[name of text box]="" Then [name of
combobox].Rowsource=SELECT.....ORDER BY ProductName [without the WHERE
clause].

hope this helps.


Seb
ORDER BY Product Name