Using Multiselect box for input to a query 1

[gavintennant]

Hi,

Can anyone help me with a problem I'm having?
From a list box I'm trying to allow a user to select multiple items (multiselect) then use the selection as the input to a query. Is this possible using multiselect and does anyone have some sample code for this?

thanks very much in advance

Gavin

 

[PaulF]

you can create a query on the fly... This example uses the employee table from Northwind, a listbox named lstNames with the LastName as the Bound Column, creates a new query named TempQdf, opens the query, then deletes it from the QueryDef Collection.

Private Sub cmdOpenQuery_Click()
Dim ctl As Control
Dim db As DAO.database
Dim qdf As QueryDef
Dim varItm As Variant
Dim strSQL As String
Dim strCriteria As String
Dim strField As String
strField = "[LastName] = '"
Set ctl = Me!lstNames
For Each varItm In ctl.ItemsSelected
strCriteria = strCriteria & " Or " & strField & ctl.ItemData(varItm) & "'"
Next varItm
strCriteria = Mid(strCriteria, 5)
strSQL = "Select * From Employees Where " & strCriteria
Set db = CurrentDb
With db
On Error Resume Next
Set qdf = .CreateQueryDef("TempQdf", strSQL)
DoCmd.OpenQuery "TempQdf"
With db.QueryDefs
.Delete "TempQdf"
End With
End With

Set db = Nothing
Set qdf = Nothing
End Sub


PaulF