none
Struggling with Multiple Multi List Boxes RRS feed

  • Question

  • Hi All,

    I redid my form because I couldn't figure out how to get the second list box into my code. Would someone be able to let me know if I am on the correct path? I have two list boxes - One named Origin and the other Enab. Any help would be greatly appreciated

    The error is get is:

    Run-time error '3075':

    Syntax error (missing operator)in query expression "Origin_ID In(,5,1) and Origin_ID IN(SELECT Orign_ID FROM tbl_Lesson_Learned WHERE Enab_Org_ID IN (1))'.

    Below is the code


    Private Sub Command5_Click()


        Dim varItem As Variant
        Dim strOriginIDList As String
        Dim strEnabIDList As String
        Dim strCriteria As String
        Dim ctrl As Control
        
        Set ctrl = Me.Origin
        
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strOriginIDList = strOriginIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strOriginDList = Mid(strOriginIDList, 2)
            
            strCriteria = " And Origin_ID In(" & strOriginIDList & ")"
            
        End If
        
        Set ctrl = Me.Enab
        
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strEnabIDList = strEnabIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strEnabIDList = Mid(strEnabIDList, 2)
            
            strCriteria = strCriteria & " And Origin_ID IN" & _
                "(SELECT Origin_ID FROM tbl_Lesson_Learned " & _
                "WHERE Enab_Org_ID IN (" & strEnabIDList & "))"
            
        End If
        
        If Len(strCriteria) > 0 Then
            ' remove leading ' and '
            strCriteria = Mid(strCriteria, 6)
            
            DoCmd.OpenReport "Report 01", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria
        Else
            MsgBox "No employee(s) or project(s) selected.", vbExclamation, "Warning"
        End If
        

    End Sub


    Private Sub cmdSelectAll_Click()

        Dim n As Integer
        
        With Me.Origin
            For n = 0 To .ListCount - 1
                .Selected(n) = True
            Next n
        End With
        
    End Sub


    Private Sub cmdSelectAllProjects_Click()

        Dim n As Integer
        
        With Me.Enab
            For n = 0 To .ListCount - 1
                .Selected(n) = True
            Next n
        End With

    End Sub



    Janet Bonelli

    Tuesday, April 26, 2016 3:43 PM

Answers

  • The error is quite clearly due to the leading comma in the value list: In(,5,1).  However, at first sight I can't see anything in your code which would leave the redundant comma in place as you are removing it with strOriginDList = Mid(strOriginIDList, 2).

    Could there be a blank row at the start of the list?  If not,set a breakpoint on the For Each varItem In ctrl.ItemsSelected line and step into the code line by line as it executes, examining the value of the strOriginDList  variable at each step.  This should help you pin down why the offending comma is there.

    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:40 AM
    • Marked as answer by David_JunFeng Sunday, May 8, 2016 2:02 PM
    Tuesday, April 26, 2016 4:47 PM

All replies

  • The error is quite clearly due to the leading comma in the value list: In(,5,1).  However, at first sight I can't see anything in your code which would leave the redundant comma in place as you are removing it with strOriginDList = Mid(strOriginIDList, 2).

    Could there be a blank row at the start of the list?  If not,set a breakpoint on the For Each varItem In ctrl.ItemsSelected line and step into the code line by line as it executes, examining the value of the strOriginDList  variable at each step.  This should help you pin down why the offending comma is there.

    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:40 AM
    • Marked as answer by David_JunFeng Sunday, May 8, 2016 2:02 PM
    Tuesday, April 26, 2016 4:47 PM
  • Hi, JLB43

    According to your description and error, I aggree with Ken Sheridan's suggestion, Mid function is used to returns a Variant (String) containing a specified number of characters from a string.

    So I suggest that you could use Right function to remove leading comma.

    For more information, click here to refer about Access Functions (by category)

    Wednesday, April 27, 2016 2:06 AM
  • Thanks again to everyone. It was actually a simple typo - I typed OriginDList instead of OriginID List

      ' remove leading comma
            strOriginDList = Mid(strOriginIDList, 2)


    Janet Bonelli

    Wednesday, April 27, 2016 4:46 PM
  • That suggests that you are not requiring the explicit declaration of variables.  This is not a good idea, as your experience has illustrated.

    Ken Sheridan, Stafford, England

    Wednesday, April 27, 2016 4:51 PM
  • Agreed. Hopefully as I gain experience I will learn to do things the correct way. Thank you again for taking a look at it.

    Janet Bonelli

    Wednesday, April 27, 2016 4:55 PM
  • Hi, JLB43

    You are welcome, we are glad to help you resolve your issue.
    Thursday, April 28, 2016 8:03 AM