How to populate a Field based off of a selection made in a Combo Box?

[TheWalkenator]

Hey all! I have a Table named "Personnel". In the table I have 3 existing fields; "Cell Phone", "Home Phone" and "Work Phone". I want to create a combo box field in the table (or corresponding form) to select the individuals 'Primary Phone Number'. Then, in a report, I want to be able to have a column that will post the primary phone number based off of the selection made in the combo box.

Any advice would be greatly appreciated...

[nico5038]

From a normalization point of view you would have to create a tblPhone with the fields:
PersonID
PhoneType
PhoneNumber
PrimaryYesNo
The PhoneType would hold "Cell", "Home" or "Work".
Now a query can select the rows with PrimaryYesNo = "Yes".
Or more advanced you could add a sequencenumber instead of the PrimaryYesNo field to show them in order or only #1.

Getting the idea ?

For getting a fieldvalue in the query you would need to dynamically create the query in VBA, or use a nested IIF() statement like:

select IIF(cmbTypeField="Cell",[Cell Phone],IIF(cmbTypeField="Home",[Home Phone],[Work Phone])) as PrimaryPhone, ....

Nic;o)