I have a form where I have to loop through selected items in a listbox and add those ID's to the database. What's happening though is that it's looping through x times the number of items I select, but it only adds the ID number of the first item in the listbox regardless of what other items I selected (and the first item is not selected. My code is below:
' Loop through each selected item and add that to the Eng_Topic lookup table
For Each varItem In Me.Res_Topic_ID.ItemsSelected
StrSQL = "INSERT INTO Eng_Topic (Eng_ID, Res_Topic_ID) VALUES (" & lastID & ", " & Me.Res_Topic_ID.ItemData(varItm) & ")"
DoCmd.RunSQL StrSQL
Debug.Print StrSQL
Next varItem
End Sub
Any help is appreiated.
Put Option Explicit
at the top of each module.
It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time.
To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor.
This is really a must have for VBA development.
It will then tell you that varItm
in Me.Res_Topic_ID.ItemData(varItm)
is undeclared - because it should be varItem
.
That's why you always get the first item from the listbox.