MS Access appending data from two separate multiselect lists to the same records of the same table

MS Access appending data from two separate multiselect lists to the same records of the same table

I have the two pictured separate multi-select lists, set to ‘Extended’:

enter image description here

The list on the left with label ‘Select Lock and Key IDs:’ is called list_keyIDs and the list on the right with label ‘Select Tags’ is called listbox_tag_nos.

At the moment I am looping through each item selected in list_keyIDs and appending to a new record in TBL_transaction when clicking the button called button_keyIDs.

But I need to append one of the tags selected to each one of the lock/key ID records created in the TBL_transaction.

Below is my VBA for appending the selected list_keyIDs to TBL_transaction. If anyone has any ideas I will be very grateful.

Option Compare Database

Private Sub button_keyIDs_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TBL_transaction", dbOpenDynaset, dbAppendOnly)
'add selected value(s) to table
  Set ctl = Me.list_keyIDs
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!trans_key_no = ctl.Column(1, varItem)
    rs!trans_lock_no = ctl.Column(0, varItem)
    rs.Update
  Next varItem

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

End Sub

Go to Source
Author: WithRegards