No lookup fields at table level then what?

[justgeig]

If you shouldn't use lookup fields at the table level, what do you do to avoid them? My ultimate goal is to have a nice form to enter data into and avoid letting the end-user see the ID for a specific table.

I have a database with a Customers table. In that table is a DistributionType number field that links to the ID field of a DistributionTypes table (referential integrity is enforced to prevent bad data being entered.) I have all my reports using the ID field and that's ok because I know what ID #3 is referring to. I want a data entry form for my end-users that will use a drop down box to allow them to choose a DistributionType based on the actual name and not the number. Am I coming at this wrong?

[Bob Fitz]

Store the Id number from DistributionTypes in the field in BrochureCustomers. On the form have a combo box that is bound to the field in BrochureCustomers. Use DistributionTypes as its Row Source but hide the first column so that the user only sees the DistributionTypes and not the Id.

[justgeig]

THANKS! I was so close :-) All I was missing was the Combo box ... I was trying to create a look-up on the actual field in the form instead of on a separate control and then storing that result into the correct field.

[ssanfu]

If you shouldn't use lookup fields at the table level, what do you do to avoid them?

Use look-up tables. Much easier to add items to a look-up table, among other benefits. Plus, if you ever decide to convert to SQL Server, SQL Server Express, etc, you won't have to redesign you databsse - look-up fields are not supported in the "big boy" databases.