Form field - want non-alphabetical display of lookup table values (1 Viewer)

HGCanada

Registered User.
Local time
Yesterday, 22:32
Joined
Dec 30, 2016
Messages
82
RESOLVED: Form field - want non-alphabetical display of lookup table values

I have a single-column lookup table listing the values that I want displayed in a combo box on a form (we'll be using the same lookup table for multiple fields in the database, which is why I need the lookup table).

I would like the values to be displayed in my combo box in the order in which I typed them into the lookup table, but the combo box always lists them alphabetically, in an order that makes no sense. Is there a way to fix this?

Thanks in advance. :)
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 02:32
Joined
Jan 14, 2017
Messages
18,186
The combo will sort the column alphabetically by default.
To prevent this, add a primary key autonumber field to the table.
Set this as the first column in your combo and hide it by setting the column width to zero
 

HGCanada

Registered User.
Local time
Yesterday, 22:32
Joined
Dec 30, 2016
Messages
82
Thanks - I will try that.
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:32
Joined
Sep 21, 2011
Messages
14,048
FWIW I generally add a SortOrder field to my lookup tables, so that I get them in the order I want them, rather than in the order they were entered.?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 02:32
Joined
Sep 12, 2006
Messages
15,614
If you only have a single column table, I can't see how you can get the records to sort in the order entered. Maybe you can, but I would have thought it would sort alphabetically. Without a date-entered field, it's hard to see how you could get back to ordering by time of record entry.
 

Micron

AWF VIP
Local time
Yesterday, 22:32
Joined
Oct 20, 2018
Messages
3,476
I'm with the sort order field. If you use an auto number field and want to interject a value later, then you can't - you can only append. A sort order field will provide a reliable order, and in fact, I always number that field in some multiples fashion e.g. 1,5,10,15...so that I can insert one or more items without having to shift everything.
 

isladogs

MVP / VIP
Local time
Today, 02:32
Joined
Jan 14, 2017
Messages
18,186
I agree about using a sort order field rather than autonumber - my usual preference for that being 10,20,30,40 etc to allow for later additions
 

HGCanada

Registered User.
Local time
Yesterday, 22:32
Joined
Dec 30, 2016
Messages
82
Thanks very much. I had already added the field to the form as a combo box, and I could not see an option to add a second field and make it invisible. So, I tinkered a bit, went back to my single-column lookup table, added a second column that was numeric, and made that my primary key, but as long as it was the second column, my combo box still grabbed the text values, correctly ordered.
 

June7

AWF VIP
Local time
Yesterday, 18:32
Joined
Mar 9, 2014
Messages
5,423
Presume you want to save the ID as foreign key into related tables. Combobox properties like:

RowSource: SELECT ID, Description FROM Table ORDER BY ID;
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
ControlSource: the field you want to save ID into, must be a Long Integer number
 

HGCanada

Registered User.
Local time
Yesterday, 22:32
Joined
Dec 30, 2016
Messages
82
Thanks June. Actually, no, we just want text saved in the tables for now. The numeric ID field is just there to ensure that the text sorts how we want it to! The people who analyze the data later will create their own numeric coding after we extract the data. Our field options will likely change a lot throughout data collection, and although as isladogs suggested, we can number the options by the 10s in order to leave space for new options in between, I think the numeric coding will end up very messy in the lookup tables.
 

June7

AWF VIP
Local time
Yesterday, 18:32
Joined
Mar 9, 2014
Messages
5,423
You need to understand what the autonumber primary key is for. It does not necessarily have meaning to users. It is just intended as a way to link related tables records. Can still add another field for the 'numeric coding'. Repeating the text into multiple tables is duplicating data and contrary to relational database principles. Retrieve the assigned text info in queries that join tables on the related key fields.
 

essaytee

Need a good one-liner.
Local time
Today, 13:32
Joined
Oct 20, 2008
Messages
512
Thanks June. Actually, no, we just want text saved in the tables for now. The numeric ID field is just there to ensure that the text sorts how we want it to! The people who analyze the data later will create their own numeric coding after we extract the data. Our field options will likely change a lot throughout data collection, and although as isladogs suggested, we can number the options by the 10s in order to leave space for new options in between, I think the numeric coding will end up very messy in the lookup tables.

What the data looks like in the fields is not the issue, as long as the design is valid. Keep your users away from the tables, they should see the data via
forms, where it's nicely presented. Users poking around in the tables, you invite problems, you don't want that.
 

HGCanada

Registered User.
Local time
Yesterday, 22:32
Joined
Dec 30, 2016
Messages
82
Thanks. I'm not sure if I'm explaining it properly. I use Autonumber all the time for those purposes, yes. The users won't be using the tables for data entry - I just check the tables to make sure the forms are capturing the information correctly. We'll be extracting the tables into Excel for analysis later.

The Lookup table is feeding several fields on a single table, that use the same response options. I am not sure if I need to set it up as a relationship, if I just point the field to the Lookup table through field properties, to grab the possible response options. In cases where only a single field is ever going to use a specific set of response options, I just manually enter the options as a value list in field properties.

For now, all I wanted was for all the fields that have the options "possibly, probably, definitely, unsure" to have those options appear in our drop-down lists in that order, rather than in alphabetical order. The numbers in the second column are not meaningful at all to us.

Thanks all. It seems to be working well now.
 

June7

AWF VIP
Local time
Yesterday, 18:32
Joined
Mar 9, 2014
Messages
5,423
I NEVER set lookup field in table, especially if alias is involved but you say you don't want that so go ahead.

So RowSource simply: SELECT Description FROM table ORDER BY ID;
 

Users who are viewing this thread

Top Bottom