I have a multi select list box call

[ice051505]

I have a multi select list box called "Spc_Slc" and want to pass the selected value as criteria to the field called "Species" in my query. I don't know how to connect the code to my query without click command button, because I only want to use the query as data source to my other form. I have the code like this:

Private Sub Spc_Slc_AfterUpdate()
On Error GoTo Err_Run

If Spc_Slc.ItemsSelected.Count = 0 Then
MsgBox "No items selected.", vbExclamation
Exit Sub
End If

Dim strCriteria As String
Dim varItem As Variant

' Build a criteria string
For Each varItem In Spc_Slc.ItemsSelected
strCriteria = strCriteria & "', '" & Spc_Slc.ItemData(varItem)
Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 3) & "'"

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset("SELECT [Species].[SpeciesRef], [Species].[Species] FROM [Species] ORDER BY [Species]" & _
"WHERE [Species].[Species] IN (" & strCriteria & ");")

rs.Close

Exit_Run:
Set rs = Nothing
Exit Sub

Err_Run:
MsgBox Err.Description
Resume Exit_Run

End Sub

Thanks!

 

[Andrzejek]

Try something like:

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String

strSQL = "SELECT SpeciesRef, Species " _
    & " FROM Species " & _
    & " WHERE Species IN (" & strCriteria & ")" _
    & " ORDER BY Species"

Debug.Print strSQL

Set rs = dbs.OpenRecordset(strSQL)

Have fun.

---- Andy

 

[ice051505]

I tried this code but it gives my the error: object variable or with block variable not set.
Can you help me to fix it?

 

[ice051505]

Private Sub Spc_Slc_AfterUpdate()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT SpeciesRef, Species FROM Species WHERE Species IN (" & strCriteria & ") ORDER BY Species"

Debug.Print strSQL

Set rs = dbs.OpenRecordset(strSQL) <== this line gives error, need some help here, thanks!
End Sub

 

[PHV]

You forgot to instantiate dbs:
Set dbs = CurrentDb

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

 

[ice051505]

Now I get another error, Run-time error'3075': Syntax error(missing operator) in query expression 'Species IN('
Again, here is my code

Private Sub Spc_Slc_AfterUpdate()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT SpeciesRef, Species FROM Species WHERE Species IN (" & strCriteria & ") ORDER BY Species"

Debug.Print strSQL

Set rs = dbs.OpenRecordset(strSQL)
End Sub

Thanks!

 

[Andrzejek]

What do you get in Immediate Window from:
[tt]
Debug.Print strSQL
[/tt]

And can you run this SQL in Access?

Have fun.

---- Andy