Looping an Update command

[database_1]

Hello, I have a line of code that allows the user to update an already input training for a training database. Where the EMP_ID and TRNG_ID correlate, the TRNG_ID is replaced with a newly made one which has the new date attached to it. My problem is, many training dates need changed to the same date. Currently I have a combobox but I would like to move to a multi select box but I am unsure if I could loop it or not. I could always do a double click method but I wanted to try this and see if it was possible. Thank you.





Set rst = CurrentDb.OpenRecordset("TRNG_TBL")
With rst
.AddNew
.Fields("TRAINING") = Me.TXT_TR.Value
.Fields("DATE") = Me.TXT_DATE.Value
.Fields("SPCL_ID") = 2
.Fields("POS_ID") = Me.COM_POS.Value
.Update
End With




Dim set_id As Integer
set_id = DMax("TRNG_ID", "TRNG_TBL", "SPCL_ID = '" & 2 & "'")


Dim EMP_ID As Integer
EMP_ID = Me.TXT_EMP.Column(2)


Dim t_name As String
t_name = DLast("[TRAINING]", "[TRNG_TBL]", "[TRNG_ID] = " & set_id & "")


CurrentDb.Execute "UPDATE [EMP_TRNG_TBL] SET [TRNG_ID] = " & set_id & " WHERE [TRAINING] = '" & t_name & "' And [EMP_ID] = " & EMP_ID & ""

[kd2017]

I don't fully understand your situation but you can loop through a multiselect listbox like so and test if an item is selected. If an item is selected you could run your query against each item, OR you could build a list of selected IDs in a string and run a single update query after the loop is finished.

     Dim i As Integer
     For i = 0 To Me.LISTBOX.ListCount - 1
        If Me.LISTBOX.Selected(i) Then
            'Do something with the selected item
            Debug.Print Me.LISTBOX.ItemData(i)
        End If
    Next i