How / where can I add records to Junction Table in MS Access ?

in your context, is a 'Scanner' a person, or a device (not that it really matters).

Normally, when I need to do this, I'll use a combo box, and two listboxes.  In the combo box, I'll display either the Contract, or the Scanner.  For this example, lets assume that we are using Contract.  Then, in the listbox on the left (lstAssigned), I'll display the Scanners that are mapped to that contract with a query that looks something like:

SELECT S.ID, S.Name
FROM tbl_Scanners as S
INNER JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE CS.ContractID = Forms!yourFormName.cboContract

Then, in the listbox on the right (lstUnassigned), I display the list of Scanners that are not mapped to that contract, with a query that looks something like:

SELECT S.ID, S.Name
FROM tbl_Scanners as S
LEFT JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE NZ(CS.ContractID, 0) <> Forms!yourFormName.cboContract

This 2nd query should identify all of the scanners, not assigned to the contract displayed in your Contract combo box.

Then, I include a couple of buttons (positioned between the two lists boxes) which allow the user to assign the scanner to the contract or to remove the scanner from the contract.  These buttons start out being disabled and are enabled / disabled in the Click event of each of the listboxes.  If the users clicks into the left listbox, then I enable the Remove button and disable the Add button.  Reverse that for the listbox on the right (scanners not assigned).

Then, the code behind the Add button looks something like:

Private sub cmdAddScanner

    Dim strSQL as string
    
    strSQL = "INSERT INTO tbl_ContractScanners(ContractID, ScannerID) " _
                & "Values (" & me.cboContract & ", " & me.lstUnAssigned & ")"
   currentdb.execute strsql, dbfailonerror
   me.lstAssigned.Requery
   me.lstUnassigned.Requery

   me.cmdAdd.Enabled = false

End Sub

Select all Open in new window

The code to remove a scanner from a contract would look like:

Private Sub cmdRemoveScanner

    Dim strSQL as string

    strSQL = "DELETE FROM tbl_ContractScanners " _
                 & "WHERE ContractID = " & me.cboContract _
                 & "AND ScannerID = " & me.lstAssigned
    Currentdb.Execute strSQL, dbfailonerror
    me.lstAssigned.requery
    me.lstUnAssigned.requery
    me.cmdRemove.Enabled = false

End Sub

Select all Open in new window