Textbox and Listbox extra click

Question

stevempeterson 0 Newbie Poster

13 Years Ago

I am using Access 2010 and have 2 tables:

ORGANIZATIONS [id], [categoryID], [requirements], [address], [city], [state], [zip], [web], [person], [title], [phone], [fax], [email], [paperwork], [brochures], and [dateModified]

LIST [ID] and

The data types are either text, number, attachment, memo, hyperlink, or date
I have a form with a listbox , textbox, and button. The purpose of these controls together is to query the records found in table1. listbox is used to offer the criteria of which fields in table to search, using the keyword in text, while button makes the query happen.
The query is dynamically created with VB. The problem I am having is that if choices are made in list1 and the user types a new search term in the textbox and clicks search, the previous value of the textbox is still used instead of the new text unless the user clicks in the listbox before clicking the button. The events in the listbox are under the _afterupdate event.
Listbox:

Private Sub list1_AfterUpdate()

'SELECT ALL' OPTION: If selected, all options are selected and then this is toggled off
If list1.Selected(0) = True Then
    list1.Selected(0) = False: list1.Selected(1) = False: list1.Selected(2) = True: list1.Selected(3) = True
    list1.Selected(4) = True: list1.Selected(5) = True: list1.Selected(6) = True: list1.Selected(7) = True
    list1.Selected(8) = True: list1.Selected(9) = True: list1.Selected(10) = True: list1.Selected(11) = True
    list1.Selected(12) = True: list1.Selected(13) = True: list1.Selected(14) = True: list1.Selected(15) = True
    list1.Selected(16) = True: list1.Selected(17) = True
End If

'SELECT NONE' OPTION: If selected, all options are deselected
If list1.Selected(1) = True Then
    list1.Selected(0) = False: list1.Selected(1) = False: list1.Selected(2) = False: list1.Selected(3) = False
    list1.Selected(4) = False: list1.Selected(5) = False: list1.Selected(6) = False: list1.Selected(7) = False
    list1.Selected(8) = False: list1.Selected(9) = False: list1.Selected(10) = False: list1.Selected(11) = False
    list1.Selected(12) = False: list1.Selected(13) = False: list1.Selected(14) = False: list1.Selected(15) = False
    list1.Selected(16) = False: list1.Selected(17) = False
End If

End Sub

Button:

Private Sub cmdSearch_Click()

Me.lstSearchCriteria.Requery


'ORGANIZATION' OPTION
If lstSearchCriteria.Selected(2) = True Then
    'MsgBox lstSearchCriteria.Selected(2) & " = true"
    strOrg = "WHERE " & "((organizations.id) " & varVariable & ")" & " OR "
    If ((lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And (lstSearchCriteria.Selected(6) = False) And _
      (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And (lstSearchCriteria.Selected(10) = False) And _
      (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And _
      (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then
        strOrg = "WHERE " & "((organizations.id) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(2) = False Then
    'MsgBox lstSearchCriteria.Selected(2) & " = false"
    strOrg = ""
End If

'SERVICES' OPTION
If lstSearchCriteria.Selected(3) = True Then
    If ((lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And _
     (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And _
     (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And _
     (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isServLast = True
    End If
    If lstSearchCriteria.Selected(2) = False Then
        isServFirst = True
    End If

    If isServFirst = True And isServLast = True Then
        'ONLY SELECTION:  First = True and Last = True
        strList = "WHERE " & "((list.list) " & varVariable & ")"

    ElseIf isServFirst = True And isServLast = False Then
        'FIRST SELECTION:  First = True and Last = False
        strList = "WHERE " & "((list.list) " & varVariable & ")" & " OR "

    ElseIf isServFirst = False And isServLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strList = "((list.list) " & varVariable & ")" & " OR "

    ElseIf isServFirst = False And isServLast = True Then
        'LAST SELECTION: First = False and Last = True
        strList = "((list.list) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(3) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strList = ""
End If


'REQUIREMENTS' OPTION
If lstSearchCriteria.Selected(4) = True Then
    If ((lstSearchCriteria.Selected(5) = False) And (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And _
     (lstSearchCriteria.Selected(9) = False) And (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And _
     (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And _
     (lstSearchCriteria.Selected(17) = False)) Then

        isReqLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False)) Then
        isReqFirst = True
    End If

    If isReqFirst = True And isReqLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strRequire = "WHERE " & "((organizations.requirements) " & varVariable & ")"

    ElseIf isReqFirst = True And isReqLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strRequire = "WHERE " & "((organizations.requirements) " & varVariable & ")" & " OR "

    ElseIf isReqFirst = False And isReqLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
         strRequire = "((organizations.requirements) " & varVariable & ")" & " OR "

    ElseIf isReqFirst = False And isReqLast = True Then
        'LAST SELECTION: First = False and Last = True
        strRequire = "((organizations.requirements) " & varVariable & ")"
    End If

ElseIf lstSearchCriteria.Selected(4) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strRequire = ""
End If


'ADDRESS' OPTION
If lstSearchCriteria.Selected(5) = True Then
    If ((lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
      (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And _
      (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isAddLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False)) Then
        isAddFirst = True
    End If

    If isAddFirst = True And isAddLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strAddress = "WHERE " & "((organizations.address) " & varVariable & ")"

    ElseIf isAddFirst = True And isAddLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strAddress = "WHERE " & "((organizations.address) " & varVariable & ")" & " OR "

    ElseIf isAddFirst = False And isAddLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strAddress = "((organizations.address) " & varVariable & ")" & " OR "

    ElseIf isAddFirst = False And isAddLast = True Then
        'LAST SELECTION: First = False and Last = True
        strAddress = "((organizations.address) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(5) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strAddress = ""
End If


'CITY' OPTION
If lstSearchCriteria.Selected(6) = True Then
    If ((lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And (lstSearchCriteria.Selected(10) = False) And _
     (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And _
     (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isCityLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False)) Then
        isCityFirst = True
    End If

    If isCityFirst = True And isCityLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strCity = "WHERE " & "((organizations.city) " & varVariable & ")"

    ElseIf isCityFirst = True And isCityLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strCity = "WHERE " & "((organizations.city) " & varVariable & ")" & " OR "

    ElseIf isCityFirst = False And isCityLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strCity = "((organizations.city) " & varVariable & ")" & " OR "

    ElseIf isCityFirst = False And isCityLast = True Then
        'LAST SELECTION: First = False and Last = True
        strCity = "((organizations.city) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(6) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strCity = ""
End If


'STATE' OPTION
If lstSearchCriteria.Selected(7) = True Then
    If ((lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And _
      (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And _
      (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isStateLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False)) Then
        isStateFirst = True
    End If

    If isStateFirst = True And isStateLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strState = "WHERE " & "((organizations.state) " & varVariable & ")"

    ElseIf isStateFirst = True And isStateLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strState = "WHERE " & "((organizations.state) " & varVariable & ")" & " OR "

    ElseIf isStateFirst = False And isStateLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strState = "((organizations.state) " & varVariable & ")" & " OR "

    ElseIf isStateFirst = False And isStateLast = True Then
        'LAST SELECTION: First = False and Last = True
        strState = "((organizations.state) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(7) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strState = ""
End If


'ZIP' OPTION
If lstSearchCriteria.Selected(8) = True Then
    If ((lstSearchCriteria.Selected(9) = False) And (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And _
    (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And _
    (lstSearchCriteria.Selected(17) = False)) Then

        isZipLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False)) Then
        isZipFirst = True
    End If

    If isZipFirst = True And isZipLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strZip = "WHERE " & "((organizations.zip) " & varVariable & ")"

    ElseIf isZipFirst = True And isZipLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strZip = "WHERE " & "((organizations.zip) " & varVariable & ")" & " OR "

    ElseIf isZipFirst = False And isZipLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strZip = "((organizations.zip) " & varVariable & ")" & " OR "

    ElseIf isZipFirst = False And isZipLast = True Then
        'LAST SELECTION: First = False and Last = True
        strZip = "((organizations.zip) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(8) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strZip = ""
End If


'WEB' OPTION
If lstSearchCriteria.Selected(9) = True Then
    If ((lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And _
      (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isWebLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False)) Then
        isWebFirst = True
    End If

    If isWebFirst = True And isWebLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strWeb = "WHERE " & "((organizations.web) " & varVariable & ")"

    ElseIf isWebFirst = True And isWebLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strWeb = "WHERE " & "((organizations.web) " & varVariable & ")" & " OR "

    ElseIf isWebFirst = False And isWebLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strWeb = "((organizations.web) " & varVariable & ")" & " OR "

    ElseIf isWebFirst = False And isWebLast = True Then
        'LAST SELECTION: First = False and Last = True
        strWeb = "((organizations.web) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(9) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strWeb = ""
End If


'PERSON' OPTION
If lstSearchCriteria.Selected(10) = True Then
    If ((lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And _
      (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isPerLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False)) _
      Then

        isPerFirst = True
    End If

    If isPerFirst = True And isPerLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strPerson = "WHERE " & "((organizations.person) " & varVariable & ")"

    ElseIf isPerFirst = True And isPerLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strPerson = "WHERE " & "((organizations.person) " & varVariable & ")" & " OR "

    ElseIf isPerFirst = False And isPerLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strPerson = "((organizations.person) " & varVariable & ")" & " OR "

    ElseIf isPerFirst = False And isPerLast = True Then
        'LAST SELECTION: First = False and Last = True
        strPerson = "((organizations.person) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(10) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strPerson = ""
End If


'TITLE' OPTION
If lstSearchCriteria.Selected(11) = True Then
    If ((lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And _
      (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isTitleLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False)) And _
      (lstSearchCriteria.Selected(10) = False) Then

        isTitleFirst = True
    End If

    If isTitleFirst = True And isTitleLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strTitle = "WHERE " & "((organizations.title) " & varVariable & ")"

    ElseIf isTitleFirst = True And isTitleLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strTitle = "WHERE " & "((organizations.title) " & varVariable & ")" & " OR "

    ElseIf isTitleFirst = False And isTitleLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strTitle = "((organizations.title) " & varVariable & ")" & " OR "

    ElseIf isTitleFirst = False And isTitleLast = True Then
        'LAST SELECTION: First = False and Last = True
        strTitle = "((organizations.title) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(11) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strTitle = ""
End If


'PHONE' OPTION
If lstSearchCriteria.Selected(12) = True Then
    If ((lstSearchCriteria.Selected(13) = False) And (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And _
      (lstSearchCriteria.Selected(17) = False)) Then

        isPhoneLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
      (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False)) Then

        isPhoneFirst = True
    End If

    If isPhoneFirst = True And isPhoneLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strPhone = "WHERE " & "((organizations.phone) " & varVariable & ")"

    ElseIf isPhoneFirst = True And isPhoneLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strPhone = "WHERE " & "((organizations.phone) " & varVariable & ")" & " OR "

    ElseIf isPhoneFirst = False And isPhoneLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strPhone = "((organizations.phone) " & varVariable & ")" & " OR "

    ElseIf isPhoneFirst = False And isPhoneLast = True Then
        'LAST SELECTION: First = False and Last = True
        strPhone = "((organizations.phone) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(12) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strPhone = ""
End If


'FAX' OPTION
If lstSearchCriteria.Selected(13) = True Then
    If ((lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isFaxLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
      (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False)) Then

        isFaxFirst = True
    End If

    If isFaxFirst = True And isFaxLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strFax = "WHERE " & "((organizations.fax) " & varVariable & ")"

    ElseIf isFaxFirst = True And isFaxLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strFax = "WHERE " & "((organizations.fax) " & varVariable & ")" & " OR "

    ElseIf isFaxFirst = False And isFaxLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strFax = "((organizations.fax) " & varVariable & ")" & " OR "

    ElseIf isFaxFirst = False And isFaxLast = True Then
        'LAST SELECTION: First = False and Last = True
        strFax = "((organizations.fax) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(13) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strFax = ""
End If


'EMAIL' OPTION
If lstSearchCriteria.Selected(14) = True Then
    If ((lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

        isEmailLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
      (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False)) Then

        isEmailFirst = True
    End If

    If isEmailFirst = True And isEmailLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strEmail = "WHERE " & "((organizations.email) " & varVariable & ")"

    ElseIf isEmailFirst = True And isEmailLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strEmail = "WHERE " & "((organizations.email) " & varVariable & ")" & " OR "

    ElseIf isEmailFirst = False And isEmailLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strEmail = "((organizations.email) " & varVariable & ")" & " OR "

    ElseIf isEmailFirst = False And isEmailLast = True Then
        'LAST SELECTION: First = False and Last = True
        strEmail = "((organizations.email) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(14) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strEmail = ""
End If


'PAPERWORK' OPTION
If lstSearchCriteria.Selected(15) = True Then
    If ((lstSearchCriteria.Selected(16) = False) And (lstSearchCriteria.Selected(17) = False)) Then

         isPaperLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
      (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And _
      (lstSearchCriteria.Selected(14) = False)) Then

        isPaperFirst = True
    End If

    If isPaperFirst = True And isPaperLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strPaper = "WHERE " & "((organizations.paperwork.FileName) " & varVariable & ")"

    ElseIf isPaperFirst = True And isPaperLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strPaper = "WHERE " & "((organizations.paperwork.FileName) " & varVariable & ")" & " OR "

    ElseIf isPaperFirst = False And isPaperLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strPaper = "((organizations.paperwork.FileName) " & varVariable & ")" & " OR "

    ElseIf isPaperFirst = False And isPaperLast = True Then
        'LAST SELECTION: First = False and Last = True
        strPaper = "((organizations.paperwork.FileName) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(15) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strPaper = ""
End If


'BROCHURES' OPTION
If lstSearchCriteria.Selected(16) = True Then
    If lstSearchCriteria.Selected(17) = False Then

         isBroLast = True
    End If
    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
      (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
      (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And _
      (lstSearchCriteria.Selected(14) = False) And (lstSearchCriteria.Selected(15) = False)) Then

        isBroFirst = True
    End If

    If isBroFirst = True And isBroLast = True Then
        'ONLY SELECTION: First = True and Last = True
        strBrochure = "WHERE " & "((organizations.brochures.FileName) " & varVariable & ")"

    ElseIf isBroFirst = True And isBroLast = False Then
        'FIRST SELECTION: First = True and Last = False
        strBrochure = "WHERE " & "((organizations.brochures.FileName) " & varVariable & ")" & " OR "

    ElseIf isBroFirst = False And isBroLast = False Then
        'MIDDLE SELECTION: First = False and Last = False
        strBrochure = "((organizations.brochures.FileName) " & varVariable & ")" & " OR "

    ElseIf isBroFirst = False And isBroLast = True Then
        'LAST SELECTION: First = False and Last = True
        strBrochure = "((organizations.brochures.FileName) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(16) = False Then
    'NO SELECTION: All is False (option wasn't selected in the first place)
    strBrochure = ""
End If


'MODIFIED DATE' OPTION
If lstSearchCriteria.Selected(17) = True Then
    'varVariable = "Like " & """#" & CDate(a) & "#""" & ""

    If ((lstSearchCriteria.Selected(2) = False) And (lstSearchCriteria.Selected(3) = False) And (lstSearchCriteria.Selected(4) = False) And (lstSearchCriteria.Selected(5) = False) And _
     (lstSearchCriteria.Selected(6) = False) And (lstSearchCriteria.Selected(7) = False) And (lstSearchCriteria.Selected(8) = False) And (lstSearchCriteria.Selected(9) = False) And _
     (lstSearchCriteria.Selected(10) = False) And (lstSearchCriteria.Selected(11) = False) And (lstSearchCriteria.Selected(12) = False) And (lstSearchCriteria.Selected(13) = False) And _
     (lstSearchCriteria.Selected(14) = False)) And (lstSearchCriteria.Selected(15) = False) And (lstSearchCriteria.Selected(16) = False) Then

        strModifiedDate = "WHERE " & "((organizations.dateModified) " & varVariable & ")"
    Else
        strModifiedDate = "((organizations.dateModified) " & varVariable & ")"
    End If
ElseIf lstSearchCriteria.Selected(17) = False Then
    strModifiedDate = ""
End If



Dim varID, varCategoryID, varRequirements, varAddress, varCity, varState, varZip, varWeb, varPerson As Variant
Dim varTitle, varPhone, varFax, varEmail, varPaperwork, varBrochures, varItem, dateModified As Variant

Dim strSelect, strFrom, strWhere, strOrder, strQuery As String
Dim dropQuery, strWhereAll, strQueryAll, strWhereList, strDescrip, strDelim, strDoc, strListFind
Dim myQuery As Object
Dim lngLen As Long

    On Error Resume Next
    Set myQuery = CurrentDb.CreateQueryDef("SearchQuery", strQueryAll)
           
    If Err.Number = 3012 Then
        dropQuery = "Drop Table SearchQuery"
        CurrentDb.Execute dropQuery
    End If
   
    DoCmd.Close acQuery, "SearchQuery", acSaveNo
           
    If a = "" Then
        MsgBox "Please enter keyword!"
    ElseIf Len(a) < 2 Then
        MsgBox "Please enter at least two characters!"
    ElseIf IsNull(varID) = False Or IsNull(varCategoryID) = False Or IsNull(varRequirements) = False Or _
        IsNull(varAddress) = False Or IsNull(varCity) = False Or IsNull(varState) = False Or IsNull(varZip) = False Or _
        IsNull(varWeb) = False Or IsNull(varPerson) = False Or IsNull(varTitle) = False Or IsNull(varPhone) = False Or _
        IsNull(varFax) = False Or IsNull(varEmail) = False Or IsNull(varPaperwork) = False Or IsNull(varBrochures) = False Or IsNull(dateModified) = False Then
     
    MsgBox strOrg & vbCrLf & strList & vbCrLf & strRequire & vbCrLf & strAddress & vbCrLf & strCity & vbCrLf & strState & vbCrLf & strZip & vbCrLf & strWeb _
 & vbCrLf & strPerson & vbCrLf & strTitle & vbCrLf & strPhone & vbCrLf & strFax & vbCrLf & strEmail & vbCrLf & strPaper & vbCrLf & strBrochure & vbCrLf & strModifiedDate
        
        
        strSelect = "SELECT organizations.id, list.list, organizations.requirements, organizations.address, organizations.city, organizations.state, " & _
        "organizations.zip, organizations.web, organizations.person, organizations.title, organizations.phone, organizations.fax, organizations.email, " & _
        "organizations.paperwork.FileName, organizations.brochures.FileName, organizations.dateModified"
        
        strFrom = vbCrLf & "FROM list INNER JOIN organizations ON list.ID = organizations.categoryID.Value"
        
        strWhere = vbCrLf & strOrg & strList & strRequire & strAddress & strCity & strState & strZip & strWeb & _
          strPerson & strTitle & strPhone & strFax & strEmail & strPaper & strBrochure & strModifiedDate
        
        strOrder = vbCrLf & "ORDER BY organizations.id;"

        strQuery = strSelect & "" & strFrom & "" & strWhere & strOrder
            
       MsgBox strQuery
            
        On Error Resume Next
            Set myQuery = CurrentDb.CreateQueryDef("SearchQuery", strQuery)
                                  
            If Err.Number = 3012 Then
                dropQuery = "Drop Table SearchQuery"
                CurrentDb.Execute dropQuery
            ElseIf Err.Number <> 3012 Then
                Set myQuery = CurrentDb.CreateQueryDef("SearchQuery", strQuery)
                DoCmd.OpenQuery "SearchQuery", , acReadOnly
            Else
                MsgBox (Err.Number & ":  " & Err.Description)
            End If
    Else
        MsgBox ("There was an error, try again later!")
    End If
    
End Sub

Private Sub lstSearchCriteria_AfterUpdate()

isServFirst = False: isReqFirst = False: isAddFirst = False: isCityFirst = False: isStateFirst = False: isZipFirst = False: isWebFirst = False: isPerFirst = False
    isTitleFirst = False: isPhoneFirst = False: isFaxFirst = False: isEmailFirst = False: isPaperFirst = False: isBroFirst = False: isDateFirst = False

isServLast = False: isReqLast = False: isAddLast = False: isCityLast = False: isStateLast = False: isZipLast = False: isWebLast = False: isPerLast = False
    isTitleLast = False: isPhoneLast = False: isFaxLast = False: isEmailLast = False: isPaperLast = False: isBroLast = False: isDateLast = False

varVariable = "Like " & """*" & a & "*""" & ""

'SELECT ALL' OPTION
If lstSearchCriteria.Selected(0) = True Then
    'MsgBox lstSearchCriteria.Selected(0) & " = true"
    lstSearchCriteria.Selected(0) = False: lstSearchCriteria.Selected(1) = False: lstSearchCriteria.Selected(2) = True: lstSearchCriteria.Selected(3) = True
    lstSearchCriteria.Selected(4) = True: lstSearchCriteria.Selected(5) = True: lstSearchCriteria.Selected(6) = True: lstSearchCriteria.Selected(7) = True
    lstSearchCriteria.Selected(8) = True: lstSearchCriteria.Selected(9) = True: lstSearchCriteria.Selected(10) = True: lstSearchCriteria.Selected(11) = True
    lstSearchCriteria.Selected(12) = True: lstSearchCriteria.Selected(13) = True: lstSearchCriteria.Selected(14) = True: lstSearchCriteria.Selected(15) = True
    lstSearchCriteria.Selected(16) = True: lstSearchCriteria.Selected(17) = True
'ElseIf lstSearchCriteria.Selected(0) = False Then
   ' MsgBox lstSearchCriteria.Selected(0) & " = false"
    'Do something else
End If

'SELECT NONE' OPTION
If lstSearchCriteria.Selected(1) = True Then
    'MsgBox lstSearchCriteria.Selected(1) & " = true"
    lstSearchCriteria.Selected(0) = False: lstSearchCriteria.Selected(1) = False: lstSearchCriteria.Selected(2) = False: lstSearchCriteria.Selected(3) = False
    lstSearchCriteria.Selected(4) = False: lstSearchCriteria.Selected(5) = False: lstSearchCriteria.Selected(6) = False: lstSearchCriteria.Selected(7) = False
    lstSearchCriteria.Selected(8) = False: lstSearchCriteria.Selected(9) = False: lstSearchCriteria.Selected(10) = False: lstSearchCriteria.Selected(11) = False
    lstSearchCriteria.Selected(12) = False: lstSearchCriteria.Selected(13) = False: lstSearchCriteria.Selected(14) = False: lstSearchCriteria.Selected(15) = False
    lstSearchCriteria.Selected(16) = False: lstSearchCriteria.Selected(17) = False
'ElseIf lstSearchCriteria.Selected(1) = False Then
    'MsgBox lstSearchCriteria.Selected(1) & " = false"
    'Do something else
End If


End Sub

microsoft-access

2 Contributors
5 Replies
123 Views
4 Days Discussion Span
Latest Post 13 Years Ago Latest Post by stevempeterson

debasisdas 580 Posting Genius

13 Years Ago

I visited this thread several times in last couple of days, but never had the patience to read 600 + lines of code.

May be i will visit this again in the weekend.

Edited 13 Years Ago by debasisdas because: n/a

debasisdas 580 Posting Genius

13 Years Ago

Happy to learn that your problem is sorted out.

Happy Coding :)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

stevempeterson 0 Newbie Poster · Answer 1 · 2011-03-20T08:37:06+00:00

Oops... The first bit of code is incorrect, here is the correct code:

Private Sub lstSearchCriteria_AfterUpdate()

isServFirst = False: isReqFirst = False: isAddFirst = False: isCityFirst = False: isStateFirst = False: isZipFirst = False: isWebFirst = False: isPerFirst = False
    isTitleFirst = False: isPhoneFirst = False: isFaxFirst = False: isEmailFirst = False: isPaperFirst = False: isBroFirst = False: isDateFirst = False

isServLast = False: isReqLast = False: isAddLast = False: isCityLast = False: isStateLast = False: isZipLast = False: isWebLast = False: isPerLast = False
    isTitleLast = False: isPhoneLast = False: isFaxLast = False: isEmailLast = False: isPaperLast = False: isBroLast = False: isDateLast = False

varVariable = "Like " & """*" & a & "*""" & ""

'SELECT ALL' OPTION
If lstSearchCriteria.Selected(0) = True Then
    'MsgBox lstSearchCriteria.Selected(0) & " = true"
    lstSearchCriteria.Selected(0) = False: lstSearchCriteria.Selected(1) = False: lstSearchCriteria.Selected(2) = True: lstSearchCriteria.Selected(3) = True
    lstSearchCriteria.Selected(4) = True: lstSearchCriteria.Selected(5) = True: lstSearchCriteria.Selected(6) = True: lstSearchCriteria.Selected(7) = True
    lstSearchCriteria.Selected(8) = True: lstSearchCriteria.Selected(9) = True: lstSearchCriteria.Selected(10) = True: lstSearchCriteria.Selected(11) = True
    lstSearchCriteria.Selected(12) = True: lstSearchCriteria.Selected(13) = True: lstSearchCriteria.Selected(14) = True: lstSearchCriteria.Selected(15) = True
    lstSearchCriteria.Selected(16) = True: lstSearchCriteria.Selected(17) = True
'ElseIf lstSearchCriteria.Selected(0) = False Then
   ' MsgBox lstSearchCriteria.Selected(0) & " = false"
    'Do something else
End If

'SELECT NONE' OPTION
If lstSearchCriteria.Selected(1) = True Then
    'MsgBox lstSearchCriteria.Selected(1) & " = true"
    lstSearchCriteria.Selected(0) = False: lstSearchCriteria.Selected(1) = False: lstSearchCriteria.Selected(2) = False: lstSearchCriteria.Selected(3) = False
    lstSearchCriteria.Selected(4) = False: lstSearchCriteria.Selected(5) = False: lstSearchCriteria.Selected(6) = False: lstSearchCriteria.Selected(7) = False
    lstSearchCriteria.Selected(8) = False: lstSearchCriteria.Selected(9) = False: lstSearchCriteria.Selected(10) = False: lstSearchCriteria.Selected(11) = False
    lstSearchCriteria.Selected(12) = False: lstSearchCriteria.Selected(13) = False: lstSearchCriteria.Selected(14) = False: lstSearchCriteria.Selected(15) = False
    lstSearchCriteria.Selected(16) = False: lstSearchCriteria.Selected(17) = False
'ElseIf lstSearchCriteria.Selected(1) = False Then
    'MsgBox lstSearchCriteria.Selected(1) & " = false"
    'Do something else
End If

End Sub

stevempeterson 0 Newbie Poster · Answer 2 · 2011-03-23T08:30:28+00:00

I have since solved it in a quick and dirty fashion. I setup a function that gets triggered by the textbox's on change event, this function then does its thing and triggers the after update event of the listbox.

stevempeterson 0 Newbie Poster · Answer 3 · 2011-03-24T12:07:03+00:00

Thanks for trying anyway. Another thing that I thought odd was that I was trying to package it, but when I was testing in a VM, it kept compalaining about not having controls.dll in the project. After some searching, it turns out that this is for Nero, but the project doesn't even use that, nor is it loaded in access. I decided to instead of fighting it, just add it to the project, and after some messing around, I got it to work, but I am not sure if it was from registering the dll first, or just supplying it. Is there a way to make an Access 2010 database completely standalone without having to have either Access or the runtime installed?

Also, for now, when I use the solutions deployment wizard and have a form open automatically, the main Access window shows up. I would like to have every single item hide except for my project itself. How so I do this? I know how to chose the options to hide the full menu, navigation, and so forth, but it still leaves the main window open.

On another subject, I am not so good at streamlined code, it is more plug and pray in that I will code how I think it should work, and then add code to fix what didn't work, so it might be 100 lines of code that would take someone else 10 or something to that effect.