Referencing ListBox multiselect values in sql statement

Question

I would like to open a recordset using matching values within a column of a multiselect listbox. At the moment my code only opens and edits the last record of the selection and I would like it to open all of them. Here is my code:-

 Set oRSAppt = Application.CurrentDb().OpenRecordset("Select * FROM [Appointments] WHERE [SlotID] =" & ListBox.Column(7, ListBox.ItemsSelected))
With oRSAppt
    If .BOF = True And .EOF = True Then
        MsgBox "No records found", , "Failed"
        Exit Sub
    Else
    .MoveFirst
    Do While Not .EOF
    .Edit
    .Fields("Status").Value = "Invoiced"
    .Fields("InvoiceID").Value = vInvoiceID
    .Update
    .MoveNext
    Loop
    .Close
    End If
End With

This link suggests a for loop to get the selected values from the listbox http://msdn.microsoft.com/en-us/library/office/ff823015%28v=office.15%29.aspx but I am not sure how to do this within the sql statement or whether I should even go about it this way - and maybe I've just been looking at this for so long I've missed an obvious solution. Any help would be appreciated.

Simon1979 · Accepted Answer · 2014-08-31 23:36:45Z

3

You will need to build your SQL statement first and yes, you need to use a loop. Something like this should do the trick:

Dim strSQL as String
Dim vItm as Variant
Dim oRSAppt As DAO.Recordset

For Each vItm In Me!Listbox.ItemsSelected
    strSQL = strSQL & ListBox.Column(7, vItm) & ","
Next vItm

strSQL = left(strSQL,len(strSQL) - 1) ' remove last comma

Set oRSAppt = CurrentDb.OpenRecordset("Select * FROM [Appointments] " _
    WHERE [SlotID] In (" & strSQL & ")")

answered Aug 31, 2014 at 23:36

Simon1979

2,1081 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sigil5 Over a year ago

Exactly what I needed, works perfectly - is DAO.Recordset preferable over Object? Thanks for your help.

sigil5 Over a year ago

I added an If statement with ListBox.ItemsSelected.Count = 0 to deal with null values.

Simon1979 Over a year ago

DAO.Recordset is preferable, you get the itellisense typing and I would say explicitly declaring any variable that doesn't require an additional reference is a good thing.

Collectives™ on Stack Overflow

Referencing ListBox multiselect values in sql statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related