Solved Look Up vs List Box (1 Viewer)

sctb0825

New member
Local time
Today, 00:05
Joined
Dec 28, 2021
Messages
20
I have a field that I use on several forms, ie Mr. Mrs, Ms, Prof, Dr. Hon.

Question? is it better to put these in a table and use Look Up on each form or to use a List box on each form?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:05
Joined
Oct 29, 2018
Messages
17,524
Hi. Definitely use a table for lookup values. As far as which control to use on your form, you can use either a combobox or a listbox, depending on your taste or design preferences.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:05
Joined
Oct 29, 2018
Messages
17,524
In addition I have several similar List that I use the same way.
Depending on the number and depth of them, you can either use separate tables for each, or combine all of them into one table. However, a single table lookup will require some additional design considerations. For example, if some of your lookup values are numeric while others are text, then you will probably have to convert the numeric values into text, to be able to keep all the lookup values in one column in the same table. You would then, probably, have to convert them back into numbers in your forms where you would use the lookups.
 

sctb0825

New member
Local time
Today, 00:05
Joined
Dec 28, 2021
Messages
20
So to be clear, I would design it in the table as a lookup the use it on my form as a combo or listbox?
 

sctb0825

New member
Local time
Today, 00:05
Joined
Dec 28, 2021
Messages
20
Just to be clearer, you don't want to design the lookup at the table level. Instead, you would use a combo or listbox on the form.

Let me be more specific, I have a table with just the list, then let's say in another table called Customers, I would include a field that has list table and define the field in the Customers table as a Lookup? or Short Text?
Or, just use Combobox or listbox in form?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:05
Joined
Oct 29, 2018
Messages
17,524
Let me be more specific, I have a table with just the list, then let's say in another table called Customers, I would include a field that has list table and define the field in the Customers table as a Lookup? or Short Text?
Or, just use Combobox or listbox in form?
Okay, let's say you have a table with a list of values you want to look up. This table should have a primary key field. For example:
Code:
ID   Payment
1    Cash
2    Credit Card
3    Check
Then, in another table, you want to select those values as a look up. The structure of that table might look something like this:
Code:
ID   Payment   Date
1    1    1/14/2022
2    3    1/12/2022
That's what the table will look like. However, on the data entry or edit form, that payment type will show the text values (Cash, Credit Card, etc.)

Hope that makes sense...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:05
Joined
Feb 19, 2002
Messages
34,345
Please read the referenced article on why you should NOT use lookups on tables. They are a crutch for people who don't know how to create a query with a join. Users NEVER have direct access to tables so it makes no difference to them. Just use a combo on a form with a RowSource that is a table or query.

Here's a link to a mini-app I include in all my applications. It is used to manage simple lookup lists. Most of the time, the user is allowed to manage the various lists but there is security that allows you to control that if you want to implement it.

 

GPGeorge

Grover Park George
Local time
Yesterday, 23:05
Joined
Nov 25, 2004
Messages
108
At root, the question comes down to the integrity of the relational database design.

Dropdowns , or combo boxes, are INTERFACE features. I.e. they are a way to allow users to interact with data via an object designed for that interaction. Interface features belong in the interface, i.e. in forms. They are NOT data constructs. They do NOT belong in tables.
 

GPGeorge

Grover Park George
Local time
Yesterday, 23:05
Joined
Nov 25, 2004
Messages
108
Someone needs to send that memo around MS because they don't seem to get it.
Not putting words into anyone's mouth or trying to infer motives, but my impression is that leaving lookup fields in tables probably reflects how hard it is to walk back mistakes loudly and publicly asserted. Pulling them out would be tantamount to admitting a mistake; it won't happen.
 

Users who are viewing this thread

Top Bottom