Drop down box not showing numbers

[saraann422]

Good afternoon,

I'm working on an Access database form, and I've added a drop down box to help find records.

The table I'm working from has 1000's of records. The two fields I'm using in the drop down are a field with just 4 digits and a names field. I've got the drop down to search for the 4 digits, and when you hit the drop down arrow it'll show those digits and last names (there are records that have the same four digits).

The problem is:
*I can get it to search for the 4 digits, but the drop down will not show in numeric/alphabetic order. So if I'm searching for 0015, the list of 0015 doesn't show, but it will show the first 0015 and the next record after that (which may be 9955).
* OR, I can change the dropdown to a query and have it sort by number/alphabet, but then if I search for anything over 4450, it just goes to 4450. The drop down will not show any number above it.

Does anyone know how I can fix this?

Thank you!
Sarah

 

[randy700]

Try including your key field in the query. You do not have to display the column, but it should make each record unique.


Randy

 

[saraann422]

The query has the following fields: ID field, 4 digit, name. I've put sort on the 4 digit field.

The people using it won't know what the id field is. I get an excel list and import it into this database, and the key/id field is auto assigned at that time.

It works until you try to pull something over 4599 (or something like that).

Thanks!
Sarah

 

[randy700]

They don't have to know what the id field is.
They shouldn't even see it.
However, because it's unique, it will allow you to see your duplicated 4 digit fields.

SELECT ID, 4digit, name FROM yourTable ORDER BY ID

In your combo box, set the row source to be the name of your query.
Set the column count to 3.
Set the column width to 0;1;2 or 0;0;2.
A column width of 0 will not display.


Randy