insert multiple records using multi select list box

[selahlynch]

I am trying to insert multiple lines into a table using a Multi Select List Box.

The best solution I could come up with was to loop through each selected item and then run an insert query for each loop iteration.

For Each rownum In EmpList.ItemsSelected
    empIDval = EmpList.Column(0, rownum)
    sqlcmd = "INSERT INTO Attendance (EmpID, EventID, DateCompleted) VALUES (" & empIDval & ", 347, '10-jan-2010')"
    DoCmd.RunSQL sqlcmd
Next

This is not ideal for a few reasons. Is there a better way? Can I combine several entries into one INSERT query, and run DoCmd.RunSQL once only? Any other suggestions?

Thanks

 

[pbaldy]

To my knowledge there's no way around the loop, but a recordset is more efficient in my experience:

http://www.baldyweb.com/MultiselectAppend.htm

 

[selahlynch]

Thanks Pbaldy,
Your website was really helpful. And manipulating a Dataset object rather than working with SQL statements was good advice.

Here is a snippet of my code in case any forum readers might find it helpful:

Private Sub SubmitButtonA_Click()
    
    Dim db            As DAO.Database
    Dim rs            As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Attendance", dbOpenDynaset)
    
    If EmpList.ItemsSelected.Count = 0 Then
        MsgBox "No employees selected"
        GoTo Endme
    End If
    
    If IsNull(EventCombo.Value) Then
        MsgBox "No events were selected"
        GoTo Endme
    End If
    
    If IsNull(DateBoxA.Value) Then
        MsgBox "No date was specified"
        GoTo Endme
    End If
    
    row = EventCombo.ListIndex
    eventIDval = EventCombo.Column(0, row)
    eventnameval = EventCombo.Column(1, row)
    dateval = Format(DateBoxA.Value, "medium date")
    namelist = "Add the following attendance records?" & vbNewLine & vbNewLine
    
    For Each row In EmpList.ItemsSelected
        empIDval = EmpList.Column(0, row)
        empnameval = EmpList.Column(1, row)
        namelist = namelist & eventnameval & "  " & empnameval & "  " & dateval & vbNewLine
    Next
    
    answer = MsgBox(namelist, vbQuestion + vbOKCancel, "SubmitAttendance")
    If answer = vbOK Then
        For Each row In EmpList.ItemsSelected
            empIDval = EmpList.Column(0, row)
            empnameval = EmpList.Column(1, row)
            rs.AddNew
            rs!EmpID = empIDval
            rs!EventID = eventIDval
            rs!dateCompleted = dateval
            rs.Update
        Next
    End If

Endme:

End Sub

 

[pbaldy]

I'm glad you found it helpful!

 

[TallMan]

Interesting. I learned a lot on this thread. Thanks guys!