multiple list box problem continued

[ice051505]

Hi, I post my solution for the multiple list box pass selection to query before, however, I just found a error while test my project. If I unselect all my selection in the list box, it will show me that my strSQL statment is error, expect select, update or insert... can anyone help me to figure it out?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim lngLen As Long
Dim strDelim As String
Dim strWhere As String
strDelim = """"
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Selection2")
With Me.Spc_Slc
 For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
    strCriteria = strCriteria & """" & .Column(1, varItem) & """, "
    Else
     MsgBox "please select at least one species.", vbExclamation
    End If
 Next varItem
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
   strWhere = "Species IN (" & Left$(strWhere, lngLen) & ")"
   lngLen = Len(strCriteria) - 2
   If lngLen > 0 Then
    strSQL = "SELECT Species, SpeciesRef FROM Species WHERE Species.Species IN (" & strCriteria & ");"
   End If
End If
qdf.SQL = strSQL <==debug error here

[pbaldy]

This will help you see what the final SQL is:

http://www.baldyweb.com/ImmediateWindow.htm

[ice051505]

Sorry, I'm new to the Access, no idea how to use the immediate Window, I just type ?strSQL into the immediate window and it returns nothing, is that means my SQL returns Null? But if I didn't unselect all my selection from list box, I still got the result I want...

This will help you see what the final SQL is:

http://www.baldyweb.com/ImmediateWindow.htm

[pbaldy]

Using the ? method would only work with a breakpoint or in debug mode. Following your code, what do you suppose it will do at the end if you don't make any selections? Specifically here:


If lngLen > 0 Then

[ice051505]

I changed the code under lngLen>0, but it still give me the same error
Here is the part of my new code, can you check for me?

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
   strWhere = "Species IN (" & Left$(strWhere, lngLen) & ")"
   lngLen = Len(strCriteria) - 2
   strSQL = "SELECT Species, SpeciesRef FROM Species WHERE Species.Species IN (" & strCriteria & ");"
Else
   If IsNull(varItem) Then
   MsgBox "You must select at least one species!"
   DoCmd.CancelEvent
   End If
End If
qdf.SQL = strSQL

Using the ? method would only work with a breakpoint or in debug mode. Following your code, what do you suppose it will do at the end if you don't make any selections? Specifically here:


If lngLen > 0 Then

[pbaldy]

Note the test I use here to make sure something is selected:

http://www.baldyweb.com/multiselect.htm