Possible to make a customer name lookup field insert customerID data?

[Digitalcandy]

I am making a very simple Access DB containing Dyes and some history of the Dyes. I have 4 tables;

Dyes
DyeHistory
Parts
Customers

Here is the DB structure;

Dyes
- DyeID
- DyeNum
- Color
- Description

DyeHistory
- HistoryID
- DyeID
- CustID
- PartID
- Location

Parts
- PartID
- PartNum
- PartRev
- ToolNum

Customers
- CustID
- CustNum
- CustName




So I'm looking to make a lookup field in the DyeHistory table for both CustID and PartID. I would like to make a combo box for both. For CustID I would like the combo box to display the list of CustNames in the Customer table but when you choose the customer name it actually writes the CustID. For PartID in the DyeHistory table I would like the combo box to lookup the ToolNum field in the Parts table but write the PartID.

Is this possible?

TIA

 

[GoDawgs]

You can do that, no problem. Just make your Row Source for the Combo Box lookup your ID field and the field you want to be able to see (in that order). Then make sure the bound column is 1 and column count is 2. Then make the column width 0";2" or whatever size you need...this will hide the actual ID field so it will look like you're just selecting the name while the DB is storing the ID (you can show the ID too if you like, just how you want things to look). Just a question, is the toolnum repeated at all? This will make it harder to know which tool you are selecting since some will be duplicated in the combo box...if that makes sense. Hope that helps.

Kevin

 

[Digitalcandy]

I think I know what you're saying but with your suggestion won't the form display the ID number and not the customer name once the record is inserted?

I want the lookup to be the customer name and record the ID number but still display the customer name in the form once the record is inserted. However, if you go to the table I want the ID number and not the customer name.


The tool number is the same scenario and no, there are not duplicates.

 

[GoDawgs]

I'm not quite sure what you're doing, I thought this was for the table itself...but it really doesn't matter in the end, a combo box will store the value of the "bound column" but only show columns according to the row width...so the ID column will be hidden if the column width is 0";2" but will be stored if the bound column is 1.