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’:
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