Is is possible to use a Drop down box in form to populate 2 fields

[lovejeeps]

I have 3 tables set up for company, contact and project. Using these tables
I have a tab form that has three tabs (company, contact and projects).
These are used to enter data 1st with the company info, then the contact name
info and finally projects for that contact working for the company. One
company can have many contacts and the contacts can have many projects. On
the projects tab in a sub form I have a drop down box that comes from a query
using the contact table (includes 2 fields, contact name and id number).
This is for selecting the specific company contact that is working on the
project. Once the contact is selected how do I store the id number in a
seperate field to run queries off of that field. Currently the dropdown box
works and shows the name and id but I can't seem to use the id number to run
other queries for reports. Any help?

 

[Allen Browne]

So you have 3 tables, and in the Relationships window you set up
relationships like this:

Company table:
CompanyID AutoNumber primary key
CompanyName Text

Contacts table:
ContactID AutoNumber primary key
ContactName Text
CompanyID Number relates to Company.CompanyID

Projects table:
ProjectID AutoNumber primary key
ContactID Number relates to Contacts.ContactID
ContactName Text

Firstly, if you do have the ContactName in the Projects table, remove it.
You are making a rod for your own back if you have the same name repeated in
multiple projects. You are guaranteed to get some bad data in this table at
some point, where the ContactID and ContactName doen't match. Avoid the
errors by storing the ContactID only.

Now your question is: when entering projects, can you use a combo box for
the ContactID? Yes: place a combo on the projects form, and give it
properties like this:
Control Source ContactID
RowSource SELECT ContactID, ContactName
FROM Contacts
ORDER BY ContactName, ContactID;
Bound Column 1
Column Count 2
Column Widths 0

The combo contains 2 columns. The first one (the ContactID) is zero-width,
so Access displays the second one (the contact name) When you choose a name,
it stores the ContactID (since the first one is the bound column.)