Updating a junction table through a user friendly form

[Alex Ess]

I have a database linking a table of employees to a table of devices and a table of phone numbers. The employees change devices and the devices sometimes change phone numbers. I want a junction table with records of the combinations of employee-device-number with a unique identifier and a date for each combination. The point is to be able to look up a person and find a list of records of which phones and numbers this person has used.

The employee table has:
Employee Name fields
An autonumber Employee ID field

The device table has:
A Hex code unique to the device
An autonumber Device ID field

The Number table has:
A Phone Number field
An autonumber Number ID field

The junction table has:
An autonumber Junction ID
The autonumber Employee ID field linked to that field in tblEmployee


The autonumber Device ID field linked to that field in tblDevice
The autonumber Number ID field linked to that field in tblPhoneNumber

I want to create a way for a user to add a new line to the junction table through a form.

You would look up the employee through the form and there would be a subform with a Device Hex # field and a phone number field.

Now if a person were updating someone’s record they would have a paper form or a post-it with the hex number of the device or the phone number or both. The junction table doesn’t have those fields, just the autonumber IDs associated with the hex codes and phone numbers. How can I get Access to let a user enter the type of number they work with on paper, look up the associated IDs and create a new line in the junction table for a new combination of employee-device-number?

[rpeare]

Have you used Combo Boxes?

A combo box will let you have two columns of data (in your case, deviceID and HexNumber). The BOUND COLUMN of this combo box will be your DEVICEID, your COLUMN COUNT will be 2, and your COLUMN WIDTHS will be 0 (zero) and 1. This allows you to suppress the DEVICEID from being viewed in your data entry and the user to type in the hex number.