Form field - want non-alphabetical display of lookup table values

[HGCanada]

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.

 

[isladogs]

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]

Thanks - I will try that.

 

[Gasman]

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]

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.

 

[Deleted member 147267]

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]

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]

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]

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]

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]

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]

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]

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]

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;

 

[HGCanada]

Thanks. Will check my database tomorrow...