Lookup fields for tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have read via the "ten commandments" of database building that I
should not use look-up fields at the table level. Before I learned of this a
number of months ago, I had completed a good part of a project I am working
on.

It appears that my tables stored the ID numbers of the info instead of the
meaningful info like an employee names such as "Stevens." Instead I have an
employee number in my table such as "3." I used the look-up wizzard to
choose the cust name, but I want the name stored, not the ID number.

How can I store a value from a table via a list and have that actual value
stored?

.. . . I certainly don't want to continue to "sin" by breaking one of the
commandments :)

Thank you for your help.
 
I think you mis-understood the information. You can and should use combo
boxes in forms. There is nothing wrong with storing an EmployeeID of 3
rather than "Stevens". Most of us recommend against using lookup fields in
table definitions.
 
"Most of us recommend against using lookup fields in table definitions."
Dauane, what does that mean?

I was referring to tables, not forms. Specifically, this is what I did: I
opened two tables, the first one was called Customers, and the other one was
called Customer Titles (Mr, Mrs, Miss, etc.). I created a lookup field to
look up a list of titles to use in the Customers table. This limited the
input to only those choices I provided and allowed me to easily keep the list
of choices in alphabetical order.

Is this how I should be doing it? Is that breaking a commandment? Doing it
this way gives me title ID numbers in my Customers table instead of the
actual title that I would prefer.

The titles talbe is a small examle of the sample thing I have done again and
again in a database I am trying to make. Most tables I lookup are much
larger.

Thanks for your help.
 
"Most of us recommend against using lookup fields in table definitions."
Dauane, what does that mean?

I was referring to tables, not forms. Specifically, this is what I did: I
opened two tables, the first one was called Customers, and the other one was
called Customer Titles (Mr, Mrs, Miss, etc.). I created a lookup field to
look up a list of titles to use in the Customers table. This limited the
input to only those choices I provided and allowed me to easily keep the list
of choices in alphabetical order.
Click to expand...

I think the basic point is that you should NOT use Table Datasheets
for any purpose other than design and debugging. They should NOT be
used for data entry; they should NOT be used for data searching; they
should not be visible to users *at all*.

Anything you can do in a table datasheet can be done - with more
control, more flexibility, and safer - with a Form.

The Lookup Wizard makes it easier to use Tables for data viewing and
data entry. This was seen by the developers at Microsoft as a good
thing; but in view of the principle above, it's seen by most working
Access developers as one of its worst sins. It's making it easier to
do something which *should not be done*.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John, I hope you respond to this message. The ONLY reason I am setting up
the lookup field in the windows design mode for the table is so that I can do
my entry on a form which has a list box for data entry.

I don't see how to get a list box on my form if I don't make the lookup when
making the table.

Help! It seems my question must be so basic that people who aren't having
my "problem" are having trouble seeing why I'm stuck. Somewhere I must have
gotten into a "rut" in my thinking and I'm not seeing how to get out.
 
Ajor said:
John, I hope you respond to this message. The ONLY reason I am setting up
the lookup field in the windows design mode for the table is so that I can do
my entry on a form which has a list box for data entry.

I don't see how to get a list box on my form if I don't make the lookup when
making the table.

Help! It seems my question must be so basic that people who aren't having
my "problem" are having trouble seeing why I'm stuck. Somewhere I must have
gotten into a "rut" in my thinking and I'm not seeing how to get out.
Click to expand...

There is NO requirement at the table level to use a ListBox or ComboBox as a
control bound to a particular field. Using a lookup field in a table might make
one of those the *default* control when you use the form wizard, but you can use
any control you like if you go into design view of the form and create it
yourself.

Just select ListBox from the toolbox bar (making sure the control wizard is
enabled) and then drag the desired field onto the form from the fieldlist. The
wizard will fire up and away you go.
 
I don't see how to get a list box on my form if I don't make the lookup when
making the table.
Click to expand...

Open the Form in design view.

Click the magic wand icon on the Toolbar.

Select the Listbox (or Combo Box, whichever you want) tool.

Drag it onto the Form where you want to see the control.

Follow the wizard's prompts.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top