Inserting multiple records at once with SQL

[HansBades]

I have a vba script that inserts a record chosen from a list box into a table. But I have now made the list box multi-select. So when the user chooses multiple records from the table, I want to be able to insert them all at once. How is this usually done?

[Minty]

Two ways, you'll need to create a loop of the selected items or create an In() clause for your sql statement that contains the ID's of the selected records.

The second is more efficient than the loop as it does them all at once, although if it's only a few records you won't notice the difference.

[HansBades]

The In() clause worked great for me in another respect. I was able to use it for another issue I was having and it worked fantastic. But how do I use it to extract values from a listbox? I am trying to write a vba statement that inserts all of the values chosen in the listbox into the appropriate table.

[June7]

Still have to loop the listbox SelectedItems to build the IN() array. Then build the SQL statement which executes only once instead of for each iteration of loop.

Common topic. Review http://allenbrowne.com/ser-50.html

[mike60smart]

Hi

The following is Code that I have used to insert multiple selections from an Unbound Listbox:-

Private Sub cmdSet_Click()
10    On Error GoTo myerror
 
      Dim rnSQL As String
      Dim varCustOrderID As Long
      Dim varItem As Variant
 
20    On Error GoTo myerror
30    For Each varItem In Me.List10.ItemsSelected
40      varCustOrderID = Me.txtCustOrderID
       
50      varItem = Me.List10.Column(0, varItem)
 
60      rnSQL = "INSERT INTO tblOrderItemsSets  (CustOrderID,[SET]) Values ( " & varCustOrderID & " , '" & varItem & "')"
 
70    Debug.Print rnSQL
80    CurrentDb.Execute rnSQL
90    Next varItem
100   Forms!frmCustomers![frmOrderItemsSetssubform].Requery
 
ExitHere:
 
110   Exit Sub
 
myerror:
120   MsgBox Err.Description
130   Resume ExitHere
 
End Sub

[moke123]

This is a public function I use with multiselect list boxes.
It returns a string like 1,3,6,8 Which you can use in an In clause.
It has options to select column, seperator characters, and delimiters.

You call it by passing the listbox object

...Where SomeID in(" & getLBX(Me.SomeListBox) & ")"

Public Function getLBX(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                       Optional delim As Variant = Null) As String

'Iterates thru the multiselect listbox and constructs an array of the selected items
'Arguments:
'Lbx is Listbox Object ie.Me.MyListbox
'intColumn is the column # to be returned
'Seperator is the character seperating items in array returned
'Delim is optional delimiter to be return in array ie. #1/1/2001#,#12/25/2015#

    Dim strlist As String
    Dim varSelected As Variant

    'On Error GoTo getLBX_Error

    If lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else

        For Each varSelected In lbx.ItemsSelected

            If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then

                strlist = strlist & delim & lbx.Column(intColumn, (varSelected)) & delim & Seperator

            Else

                strlist = strlist


            End If

        Next varSelected

        If Nz(strlist, "") <> "" Then

            strlist = Left$(strlist, Len(strlist) - 1)  'remove trailing comma

        End If
    End If

    getLBX = strlist

    On Error GoTo 0
    Exit Function

getLBX_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"

End Function

Here's an example to show how it works

[Welshgasman]

Still have to loop the listbox SelectedItems to build the IN() array. Then build the SQL statement which executes only once instead of for each iteration of loop.

Common topic. Review http://allenbrowne.com/ser-50.html

Looping for Selected and one query, should be more efficient than looping for Selected and a query for each selected though, surely?

[NoellaG]

Quite right. Where I work developers that use RBAR (Row By Agonizing Row) programming get shot at sunrise by the DBA's.

[June7]

Looping for Selected and one query, should be more efficient than looping for Selected and a query for each selected though, surely?

That's what I suggested - build array string and execute SQL only once afterward.