creating bridge-table records

[dadubs]

Problem is making new entries in a bridge or join table.

Employees
EmployeeID
FirstName

Certifications
CertificationID
CertName

EmployeeCert (join table)
EmployeeID
CertificationID

- Each employee can have many Certifications. Each Certification can be held by many Employees. A many to many relationship, hence the join table.
- I have separate data entry forms for Employees and Certifications. I have a sub form on the Employees form that displays the certifications that the current Employee has. But, I have manually put entries in the bridge table.
- What control(s) are necessary to put new records in the bridge table? Please include the query to populate that control. Any references to web tutorials would be appreciated.
- My concept is to have a form without a datasource, a listbox of Employees, and a listbox of Certifications. The user selects an entry from each listbox (one Employee and one Certification), then presses a button to make the connection. I am not adverse to VBA code, but would prefer not to have to use it.

 

[jzwp22]

Considering that your list of certifications will probably not change much over time, I would recommend putting a subform based on the EmployeeCert (join) table on the employee form (I assume that your employee form is bound to the employee table). If you have your relationships established in the relationship diagram window, Access will automatically link the subform to the main form. In the subform, I would use a combo box that is based on the certifications table. You would tie the bound field of the combo box to the certificationID field in the join table. No code would be necessary for this approach.

 

[dadubs]

I now have an Employees form with data source the Employees table. I also have a subform with a list of certifications. What action do I take to add a new certification to the bridge table between Employees and Certifications? What do I click?

 

[jzwp22]

I also have a subform with a list of certifications

The subform should be based on the junction/bridge table NOT the certification table. You will need to add a combo box to the subform. That combo box should have the certification table as its row source.