MS-Access Junction Table Inserts/Deletions Between Two Mutually Exclusive Lists (2 Listboxes)

Question

With this kind of design, I would like to create a functionality with which to add and delete records from the junction table.

The case is from when editing an employee and selecting what exams they can take. In the left list box would be the exams that they aren't eligible for (yet, anyway) and the exams that they are eligible for on the right list box.

The table is something like this:

TABLE EmpExam 
(
  EmpID,
  ExamID
)

The EmpID would be known at all times, but the ExamID would be selected from the left list box. Records in the right list box would probably have to have both the EmpID and the ExamID in order to be deleted.

Instant deletions/insertions aren't necessary once they're into their respective boxes are not necessary (they can wait until the form is closed).

Is there a good/standard way to accomplish this in Access?

Answer 1

Why use listboxes when you will have to add items to the table using code and then delete them using code?

For what you want to do, a subform is the usual solution. Furthermore, you can use the recordsetclone of your subform. Note that you should probably have a datetime field in that setup. Also, if ExamID is unique, and cascade delete is enabled, deleting from the main table will delete from the subtable.

Answer 2

I ended up using two listboxes with 1 add button and 1 remove button that triggers their VBA On Click methods that execute raw SQL.

The method looks something like this:

If IsNull(cboInEligible.Column(1))
  Exit Sub
End If

CurrentDB.Execute ("INSERT INTO tblEmpExam (ExamID, EmpID) " & _
                   "VALUES (" & ExamID & ", " & lstInEligible.Column(1) & ")")

lstInEligible.Requery
lstEligible.Requery

The delete query is similarly done.

The two listboxes are mutually exclusive.

SELECT EmpID, EmpName
FROM Employee
WHERE EmpID NOT IN (SELECT EmpID FROM tblEmpExam WHERE ExamID = [txtExamID]);

txtExamID is a hidden (but obvious to the designer) control on the form since I can't refer to the form's ExamID except through a control or through absolute naming.

The other list box has EmpID IN instead of EmpID NOT IN to make it exclusive.