[dba-SQLServer] List Box values to SP

If you are using a multi-select listbox, you can always loop through the list box as such:

'Inserting records from a listbox (Looping through the listbox)
    Dim varIndex As Variant
    Dim lRecordsAffected As Long
    Dim Cnn As ADODB.Connection
    Dim Cmd As ADODB.Command
    Set Cnn = New ADODB.Connection
    Set Cnn = CurrentProject.Connection
    For Each varIndex In Me.MyListBox.ItemsSelected 'For every item selected in list box
    Set Cmd = New ADODB.Command
        With Cmd
            Set .ActiveConnection = Cnn
                .CommandType = adCmdStoredProc
                .CommandText = "stp_YourSprocHere"
                .Parameters.Append .CreateParameter("@addrID", adGUID, adParamInput, , Me.lstAddrLocOrContact.Column(3, varIndex))
                Set .ActiveConnection = Cnn
                .Execute lRecordsAffected, , adExecuteNoRecords  ' Do not return a recordset
            End With
        Set Cmd = Nothing
    Next 'Loop to the next selected item
    Cnn.Close
    Set Cnn = Nothing



Another thing that I just recently did on a different form was to create a global temp table on the form open (ADP).
I am using this form as a "maintenance" screen to clear out duplicate / erroneous entries.
I Move certain records from one listbox to the next listbox by flagging a field in the temp table with a code. 
Both listboxes have the same stored procedure as the its rowsource (only sending back a different parameter, for the filter).
When I am ready to do my multi table updates with the values listed in the listbox, I do it all through T-SQL by joining against the temp table. Upon closing the form, the global temp table deletes itself. Pretty neat.

D






Martin wrote:

I need to pass the values from a list box in Access to a stored procedure
which does a multi table insert.Unless of course theres a better way to do
this?

Martin