Loop to add multiple records

[roarcrm]

Hello,

I currently have a form that adds data to a table, it currently only lets us select one client so if 10 clients attend a workshop then we have to fill this form in 10 times. I wondered if it was possible to have a multi-select list box to select as many clients as required and then loop through the rest of the fields that would all be the same. The code to add currently is:

Private Sub Command25_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Interaction")
With rst
     .AddNew
     .Fields("Member") = ClientCombo.Value
     .Fields("Date of Interaction") = DateofInteraction.Value
     .Fields("Time Spent") = TimeSpent.Value
     .Fields("Interaction") = Interaction.Value
     .Fields("Service") = Service.Value
     .Fields("Staff") = Staff.Value
     .Fields("Comment") = Comment.Value
     .Update
 End With
 DoCmd.Close acForm, "Log Interaction", acSaveYes
End Sub

Any help with this? Thanks.

[ItsMe]

You can iterate a multiselect listbox using a for each statement. Creating and testing a loop can be difficult if your code fails to ever exit the loop. It is good to understand how to use breaking points and the debugger tools when creating loops that iterate.

So your code would look something like this.

    

'There is no need to declare and initialize the variant varItem
'Dim varItem as Variant
'varItem = ""

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Interaction")

For Each varItem In Me.lstContol.ItemsSelected

With rst
     .AddNew
     .Fields("Member") = Me.lstControl.Column(0, varItem)
     .Fields("Date of Interaction") = DateofInteraction.Value
     .Fields("Time Spent") = TimeSpent.Value
     .Fields("Interaction") = Interaction.Value
     .Fields("Service") = Service.Value
     .Fields("Staff") = Staff.Value
     .Fields("Comment") = Comment.Value
     .Update
 End With

Next varItem

rst.close
set rst = Nothing
 DoCmd.Close acForm, "Log Interaction", acSaveYes

[roarcrm]

Excellent thank you for that!

[ItsMe]

You bet. It may be helpful to add code that will check if the user actually selected an item within the ListBox. You can see what properties are available by typing the control name and then the dot operator. Intellisence will display the Objects members. (Me.lstControl.) Checking the control's properties before executing code is a good idea.