Many-to-many form design

[vladimir_novkov]

[SOLVED] Many-to-many form design

Hello,

I am new to both this forum and MS Access. I need to design a database for a translation company and I have run into some troubles. Here is the case:

The company has employees, who may have multiple competencies. They can be a translator, an editor, etc. I am aware that a need a many-to-many relationship, but I can't seem to get around the form design. How can I design a form in which the customer may add a new employee and select his/her multiple competencies. I have established the many-to-many relationship, but I'm stuck :banghead:

The other thing is that a single project may be assigned to multiple employees. I believe that the solution in this case will be similar, though.

I would appreciate any help.

 

[Galaxiom]

Employees are shown and added in the main form.

The competencies go in a subform using the junction table as the RecordSource.

The EmployeeID is entered in the subform recordset automatically by the LinkFields. A combo with Competencies as the RowSource is used to select the CompetencyID.

 

[vladimir_novkov]

Thank you very much for your answer! I think I got it However, I ran into another issue. I don't know whether I have to enforce referential integrity on the junction table relationships. If I do, Access tells me that "You cannot add or change a record because a related record in required in table "Competencies". Should I just remove the referential integrity?

Thank you again for the quick and accurate response.

Vladimir

 

[Galaxiom]

Referential integrity should work fine if the combo RowSource is based on the Competencies table.

There might be an error in the Competencies combo setup on the subform.

Check the BoundColumn property of the combo is holding the values that you want to store in the field of the Competencies table.

 

[vladimir_novkov]

Sorry for not thanking you until now. Your solution solved my problem! Thank you!

 

[Cwwrig]

Employees are shown and added in the main form.

The competencies go in a subform using the junction table as the RecordSource.

The EmployeeID is entered in the subform recordset automatically by the LinkFields. A combo with Competencies as the RowSource is used to select the CompetencyID.

This answer was quite helpful in developing a form I was working on. My follow up question to this is what if you want to be able to "add" competencies using the same form? In other words is it feasible/recommended to be able to add employees and competencies using the same form? If so advice on how to set this up would be helpful. I have my form setup based upon your quoted advice, but I can't add new "competencies" I can only select them from the existing list.

 

[highandwild]

What I think is being missed here is the transfer of the selected Competances to the junction table between Employees and Competances, lets call it tbl_EmployeesCompetances as I always precede table names with "tbl_".

I find it easier to have a seperate form with just a Listbox, a Submit button and a Cancel button to enable users to select the many side records, in this case the Competances. The Multi-Select listbox enables users to select one or many Competances and then upon using the Submit button, a record is created in the tbl_EmployeesCompetances table for each item selected. This record will at least contain the Employee ID and the Competance ID.

You will need to trawl through the selected items in the listbox and then create a tbl_EmployeesCompetances record for each item selected. There is plenty of help on the internet regarding both of these techniques.

Another option is to have a sub form listing the Competances instead of the Listbox and a checkbox used to select the items required. You will then need to append the selected items to the tbl_EmployeesCompetances table after having first deleted all of the tbl_EmployeesCompetances records for the Employee.

You will need to consider the updating of the list of Competances for each Employee. The list of Competances to select from will need to have those alreay selected for the Employee already selected.

I hope that this helps. It is always amazing how something so apparently simple requires a number of different techniques.