Create recordset from selected records in multiselect listbox

[MickelR]

I have created a listbox with multiselect set to simple. I would like to create a recordset of the selected records that the user selected in the listbox.

I could create a table of the selected records first, but I was wondering if there would be a more efficient way.

Who has that brilliance to come up with the solution?

 

[Andrzejek]

Is that what you are after?
ListBox1 (MultiSelect set tu Multi) and a CommandButton1 on a UserForm

Option Explicit

Private Sub UserForm_Initialize()

With ListBox1
    .AddItem "Andy"
    .AddItem "Susie"
    .AddItem "Bob"
End With

End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim strNames As String
Dim strSQL As String

With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) = True Then
            If Len(strNames) = 0 Then
                strNames = "'" & .List(i) & "'"
            Else
                strNames = strNames & ", '" & .List(i) & "'"
            End If
        End If
    Next i
End With

strSQL = "SELECT * From SomeTable " _
& " Where NameField IN (" & strNames & ")"

Debug.Print strSQL

End Sub
[green]
'SELECT * From SomeTable  Where NameField IN ('Andy', 'Bob')[/green]

Have fun.

---- Andy

 

[PHV]

I could create a table of the selected records
So, where is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

That is a good technique and there are several techniques. If you go to the Access Queries forum and go to FAQs, there are about 4 other methods. Look at the bottom of the page.

 

[TheAceMan1]

How are ya MickelR . . .

If the primarykey of the row source is included in a column, you can pack the PK's in the[blue]In[/blue] clause of an SQL statement:

[blue]   Dim LBx As ListBox, SQL As String, Pack As String, idx
   
   Set LBx = Me![ListboxName]
   
   SQL = "SELECT PK, FirstName, LastName " & _
         "FROM TableName " & _
         "WHERE (PK IN "
   
   For Each idx In LBx
      If Pack <> "" Then
         Pack = "," & LBx.Column(0, idx)
      Else
         Pack = LBx.Column(0, idx)
      End If
   Next
   
   SQL = SQL & "(" & Pack & ")) " & _
         "ORDER BY [LastName];"
   
   Debug.Print SQL[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MickelR]

Hi all,

Thank you for all your suggestions. I had to tweak a few things around, but eventually I made it work using Andrzejek's suggestion.

.List(i) is not a valid item in the with .listbox. I used .column(row,i) instead.

Thanks again for all your help. I appreciate it.