Entering Data into Table with AutoNumber Foreign Key

Question

I've run into a bit of an issue which at its most basic and gutted form is composed of two tables and a relationship.

Suppose you have two tables, one holding a list of companies, the other holding the employees. Table Company has two fields (that are relevant), companyName (Short Text), and companyID (AutoNumber). Perhaps two companies will have the same name, or one needs to easily change the name of a company, and so companyName is a normal field while companyID is the primary key.

The second table, Employee, contains employeeName (Short Text) and the companyID (AutoNumber) that they work for. Together, they are the primary key. Just to be clear, Employee.companyID is a foreign key that references Company.companyID

Easily, directly into the table or using a form, I can add data to Company. 

However, I have no ability to select the Employee.companyID to add employees. 

- I can't change the datatype of Employee.companyID, as then the relationship doesn't work ("Relationship must be on the same number of fields with the same data types)

- Neither type of AutoNumber helps (increment or random)

- The only solutions I can think of are to either not have the foreign key (not ideal but space isn't much of a concern), or not use AutoNumber and generate the value within VBA (sounds excessive considering this seems like a simple/common enough issue)

Hopefully, I'm overlooking something obvious. Thanks!

Answer 1

I'd use an AutoNumber field employeeID as primary key of the Employee table.

To enter data, create a main form based on Company, and a subform based on Employee, with companyID to link them. Access will automatically fill in companyID in the Employee subform when you create a new record.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)