populating junction table problem

[SansPeur]

Hi, ACCESS noob here. I have a simple database (using to test problems with a much larger one) with three tables, one being a junction table because the relationship between the other two (tblEmployee and tblDuties) is many-to-many.



tblEmployee
EmployeeID (PK, autonumber)
LastName (Text)
FirstName (Text)

tblDuties
DutyID (PK, autonumber)
DutyName (Text)

tblEmployeeDutiesJunction
EmployeeDutyID (PK, autonumber)
EmployeeID (FK, long integer)
DutyID (FK, long integer)

I have a form set-up to input data into tblEmployee (called frmEmployee).
I have already populated tblDuties with data.

I understand that I need a another form to populate the junction table. I would like, while creating a new employee record (using frmEmployee), to have tblEmployeeDutiesJunction.EmployeeID to be automatically populated with the current EmployeeID from frmEmployee. I already have a combo box set-up that when I select the duty name, it populates tblEmployeeDutiesJunction.DutyID with the proper DutyID from tblDuties. How can I get this to work?

Thanks very much in advance!

[John_G]

First - is the combo box for the duties on the Employee form? If so, how does it add a record to the junction table?

Unless you have a business rule that states that there can be no employee without at least one duty, then I suggest that you not try to populate the junction table from the employee form (what if you wanted to add multiple duties for that employee?). Use a separate form to assign and manage the employee - duty data.

John

[SansPeur]

Hi John, thanks for the reply. No, the combo box for the duties is on a separate form from frmEmployee (it is frmEmployeeDuties). Each employee will have at least one duty. How do I get the frmEmployee.EmployeeID into frmEmployeeDutiesJunction.EmployeeID? Thanks!

[John_G]

frmEmployeeDuties is adding a record to tblEmployeeDutiesJunction, with a value in the DutyID field - but only that field, and EmployeeID is blank - is that correct?

If so, that is not good database practice, because the record you have added is not valid because it represents only one side of the many-to-many relationship. So now, when you add a new employee record, are you wanting to add the employee to the junction table record you just created? What happens if you added more than one?

My suggestion is that you use frmEmployee just to enter employee data. Then put another combo box on frmEmployeeDuties to select the employee; frmEmployeeDuties will now do what it should, which is add valid records (i.e. with both EmployeID and DutyID having values) to the tblEmployeeDutiesJunction table.

HTH

John

[SansPeur]

Hi John, thanks again for helping me out. In regards to your question, that is exactly what I am trying to do...use a field on frmEmployeeDutiesJunction to "grab" the EmployeeID from an active frmEmployee (or tblEmployee, whichever would be best), but didn't want to do it via a combo box (was hoping that I could have a text box or some other way to pull the active field data from frmEmployee.EmployeeID and populate the associated field in frmEmployeeDuties.EmployeeID - without the user having to select from a pull-down - to satisfy the many-to-many relationship there).

Can I do what you stated in your third paragraph without using a combo box (can it be done using a text box so that the user dowsn't have to select)?

[John_G]

Hi -

Sure, no problem with not using a combo, but the user would have to enter the EmployeeID, not the name, since that is the data being stored in the junction table. But a combo box need not be that difficult. If you set the Auto Expand property of the combo to Yes, then Access with go to the first matching record as the user types characters into the box. So, if the user types in "Smi", Access will go to the first "Smith" in the list (or whatever name has "Smi" as the first three characters), and from there the user can expand the box to find the right one. (This assumes that that the list is sorted alphabetically).

If you choose to have the user enter the EmployeeID directly, then you should write some VBA to display the corresponding name in an unbound text box for verification.

John

[SansPeur]

John, Thanks for the info and clarification, truly appreciated!