Priority listing within a combo box

G

Glenn

Does anyone know how to display the contents of a list of items from a combo
box in the order the user wants (rather than alphabetically...which is what
Access keeps doing on its own). I'd like to list the most commonly used
items first in the combo box with the other less used items appearing last
(at the bottom of the list).

Access keeps placing the items in my list in alphabetical order.

Thanks.
 
B

Brendan Reynolds

Add another field that can be used to specify the order, and base the combo
box on a query that sorts on that field. For example suppose I have a table
with two fields, Description and SortOrder. The data might be something like
....

Description SortOrder
First Item 10
Second Item 20
Third Item 30

The row source for the combo box would be ...

SELECT Description FROM SomeTable ORDER BY SortOrder
 
S

Sprinks

Hi, Glenn.

AFAIK there's no feature by which Access learns which ones are used most
often, but if you are getting the values for the combo box from a table, you
can easily add a Rank field to the table and sort by it.

The RowSource for the combo box will be something like:

SELECT YourFieldList FROM YourTable
ORDERBY YourTable.Rank;

Sprinks
 
G

Glenn

Sprinks,

Your solution makes sense to me and should work; however, I keep getting an
error message when I click on the combo box in the form saying, "Characters
found after end of SQL statement". In the Row source for this field I have
placed the following text, "SELECT Battery FROM Battery;ORDERBY Battery.Rank;

I forgot to add that the table I am trying to rank order is a listing of
tests (battery) and when a particular test is selected the corresponding
dollar amount associated with that test automatically drops into another
field on the form. I've used VBA code to make this work and I'm not sure if
that is interfering with the ranking text I'm trying to use.

Am I making any sense here?
 
R

Rob Parker

Remove the semicolon from the middle of the rowsource string. It should
read:

SELECT Battery FROM Battery ORDERBY Battery.Rank;

The semicolon indicates the end of an SQL string. There can only be one,
and it must be at the end of the string.

HTH,

Rob
 
B

Brendan Reynolds

In addition to Rob's good advice, there is a space missing between "ORDER"
and "BY". The complete SQL statement should be ...

SELECT Battery FROM Battery ORDER BY Battery.Rank;

As there is only one table involved, it is not necessary to specify the
table when naming the field, so this could be slightly simplified to ...

SELECT Battery FROM Battery ORDER BY Rank;
 
G

Glenn

Thanks Rob...it now works!
--
Glenn


Rob Parker said:
Remove the semicolon from the middle of the rowsource string. It should
read:

SELECT Battery FROM Battery ORDERBY Battery.Rank;

The semicolon indicates the end of an SQL string. There can only be one,
and it must be at the end of the string.

HTH,

Rob
Click to expand...
 
G

Glenn

Brendan,

It works!! Thanks so much for your help. It is amazing how a single space
(in between 'ORDER' and 'BY') makes all the difference.
 
Top