Solved Look Up vs List Box (1 Viewer)

sctb0825

Member
Local time
Today, 15:25
Joined
Dec 28, 2021
Messages
53
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?
 

sctb0825

Member
Local time
Today, 15:25
Joined
Dec 28, 2021
Messages
53
In addition I have several similar List that I use the same way.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:25
Joined
Oct 29, 2018
Messages
21,358
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
Today, 15:25
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 15:25
Joined
Dec 28, 2021
Messages
53
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

Member
Local time
Today, 15:25
Joined
Dec 28, 2021
Messages
53
Do I need to create a query to go with that setup
 

sctb0825

Member
Local time
Today, 15:25
Joined
Dec 28, 2021
Messages
53
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
Today, 15:25
Joined
Oct 29, 2018
Messages
21,358
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, 18:25
Joined
Feb 19, 2002
Messages
42,976
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
Today, 15:25
Joined
Nov 25, 2004
Messages
1,776
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2002
Messages
42,976
and just to add - just because you can doesn't mean you should
Someone needs to send that memo around MS because they don't seem to get it.
 

GPGeorge

Grover Park George
Local time
Today, 15:25
Joined
Nov 25, 2004
Messages
1,776
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2002
Messages
42,976
It was the putting them in I was referring to. MS only removes useful features like linking to dBase files and reading A97
 

Users who are viewing this thread

Top Bottom