Code:
Private Sub cmdAddOne_Click()
Dim db As DAO.Database
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim IsRptOpen As Boolean
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from [T10_JunctionTable_BWG] where BilletIDfk=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("WorkingGroupIDfk") = [WorkingGroupIDpk]
.Fields("BilletIDfk") = Me.lstAvailable.ItemData(varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
Me.cboWorkingGroup.Requery
End Sub
Code:
Private Sub cmdRemoveOne_Click()
On Error GoTo cmdRemoveOne_Click
Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Dim IsRptOpen As Boolean
For Each varItem In Me.lstSelected.ItemsSelected
strWhere = strWhere & "BilletIDfk=" & Me.lstSelected.ItemData(varItem) & " OR "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 4)
strSQL = "Delete * from [T10_JunctionTable_BWG] where WorkingGroupIDfk=" & [WorkingGroupIDpk] & " AND (" & strWhere & ");"
Set db = CurrentDb
db.Execute strSQL
Me.lstAvailable.Requery
Me.lstSelected.Requery
Me.cboWorkingGroup.Requery
Set db = Nothing
Exit_cmdRemoveOne_Click:
Exit Sub
cmdRemoveOne_Click:
Resume Exit_cmdRemoveOne_Click
End Sub
You also have an error in query3, the rowsource for lstAvailable.
Code:
SELECT o.BilletIDpk, o.RA_BIN AS RA_BIN_NUMBER, o.RA_Billet_Title, mq.BilletIDfk
FROM T01_Billets AS o LEFT JOIN (SELECT T10_JunctionTable_BWG.[BilletIDfk], [T10_JunctionTable_BWG].[WorkingGroupIDfk] FROM T10_JunctionTable_BWG WHERE T10_JunctionTable_BWG.[WorkingGroupIDfk]=Forms!F01_Listboxes![WorkingGroupIDpk]) AS mq ON o.BilletIDpk = mq.BilletIDfk
WHERE (((mq.BilletIDfk) Is Null));