drop down in form

[paulvan]

Instead of using a drop-down in a table I would like to populate the table via a form using data from an other table.

Example
I have a table with list of lab technicians (labtechs)
I have a table (Process) with a process where I would like to add a field with the labtech but only from the list in table: Labtech.


Instead of making a drop-down in the Process table that links to the Labtech table, I would like to populate the labtech field in the Process table via a Form where I only have the choice of labtechs from the Labtech table.
So basically I want a drop down in the form but NOT in the table.

is that possible?

Paul

[jzwp11]

Welcome to the forum!

Absolutely it is possible. In fact it is the preferred way of doing it. Having lookups (combo/list boxes) in your table can cause problems so they are best left for forms. See this site for more details.

In your process table, you should have a field for the labtech, but more specifically for the key field that corresponds to the lab tech. Typically I use an autonumber primary key field and reference that as a foreign key.

LabTech
-pkLabTechID primary key, autonumber
-txtFName
-txtLName

Process
-pkProcID primary key, autonumber
-fkLabTechID foreign key to LabTech table

For your form, you can use the wizard to create a form bound to the process table. Next, delete the control that the wizard created for the labtech field. Use the combo box wizard to create the lab tech combo box. Follow the steps through the wizard and make sure to bind the combo box to the labtech field.

[paulvan]

Thank you very much, that worked.
One more problem though. The combo box in the form lists the Primary keys of the Labtech table and not the names of the labtech.
Probably, i didn't bind the correct filed in the combo box, however, I couldn't figure out how to start the Combo box "wizard", I just added manually a combo box.

[jzwp11]

First to use the combo box wizard, you would just click on the combo box icon on the ribbon (Design tab), then go to the form and click (to place) & drag to size from there the wizard should take over

You can hide the primary key value. From design view, click on the combo box and the open the property sheet. Go to the Format tab. Look for the column widths property. For each column, there should be a number with unit of measure separated by a semi-colon like this: 1";1";2"

This represents 3 columns with widths of 1", 1" and 2" respectively. The first non-zero width is the one that is displayed in the combo box once a selection is make. If I assume that the key column is the first column, to hide it just set the width to zero: 0";1";2"

[paulvan]

ah, that is easy.

One more question. Similar to the tech field I would like to do the same thing for selecting samples from a different table. However, the list of samples can be very large but they are assigned to different projects. Can I restrict the dropdown of the sample by first selecting the appropriate project?

[jzwp11]

Yes, that can be done (and is commonly done). It uses a technique called cascading combo boxes (or list boxes). Roger Carlson has example databases on his site.