Can you help me with my Search Form

J

jaygoodwin77

New member
Local time
Yesterday, 20:53
Joined
May 7, 2008
Messages
8
Hi all,

Have now zipped up my DB, and wanted some help with search form, what is the best way to get this working?
 

Attachments

Hi,
Your search screen has a little way to go, but I'm hoping that the code below will assist in moving things forward. The attached image is the search screen and you can see the various options/ dropdowns available to the users. Some of the options also have a 'do not include' option to allow a kind of negative search facility.
It could probably be handled a little better, as I'm sure someone will point out soon enough, but it's working for me just fine.
Good luck.

Code: 
Private Sub cmdScan_Click()
    On Error GoTo Err_cmdScan_Click
    
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim sections As Integer
    Dim strCR, strINT, strBR, strTown, strPCode, strCreation, strContact, strLSource, strLType, strCType, strCStatus, strCCAuth As String
    Dim varItem As Variant
    
cmdScan_Start:
    Me.Refresh
    sections = 0
    strCR = " "
    strINT = " "
    strBR = " "
    strTown = " "
    strPCode = " "
    strCreation = " "
    strContact = " "
    strLSource = " "
    strLType = " "
    strCType = " "
    strCStatus = " "
    strCCAuth = " "

    Set dbs = CurrentDb
    strSQL = "SELECT [Case Id] FROM case WHERE "

If selCROn Then
        If selCRNot = "Not" Then
            strCR = strCR & "([Sales Rep] Not Like "
            For Each varItem In Me.selCR.ItemsSelected
                strCR = strCR & Chr(34) & Me.[selCR].ItemData(varItem) & Chr(34) & " And [Sales Rep] Not Like "
             Next
             strCR = Left(strCR, Len(strCR) - 26) & ")"
             sections = sections + 1
        Else
            If selCRNot = "All" Then
                strCR = strCR & "[Sales Rep]"
             Else
                strCR = strCR & "([Sales Rep] ="
                For Each varItem In Me.selCR.ItemsSelected
                     strCR = strCR & Chr(34) & Me.[selCR].ItemData(varItem) & Chr(34) & " OR [Sales Rep] = "
                Next
                    strCR = Left(strCR, Len(strCR) - 18) & ")"
            End If
        sections = sections + 1
        End If
End If

If selTownOn Then
    If sections = 0 Then
        strTown = "([Corres Addr3] like '" & Forms![frmData-Mining-Case]![selTown] & "')"
    Else
        strTown = "AND ([Corres Addr3] like '" & Forms![frmData-Mining-Case]![selTown] & "')"
    End If
    sections = sections + 1
End If

If selPCodeOn Then
    If sections = 0 Then
        strPCode = "([Corres PCode] like '" & Forms![frmData-Mining-Case]![selPCode] & "')"
    Else
        strPCode = "AND ([Corres PCode] like '" & Forms![frmData-Mining-Case]![selPCode] & "')"
    End If
    sections = sections + 1
End If

If selCreationOn Then
    If sections = 0 Then
        strCreation = "([Creation Date] between #" & Forms![frmData-Mining-Case]![selCreationLowb] & "/" & Forms![frmData-Mining-Case]![selCreationLowa] & "/" & Forms![frmData-Mining-Case]![selCreationLowc] & "# AND #" & Forms![frmData-Mining-Case]![selCreationHighb] & "/" & Forms![frmData-Mining-Case]![selCreationHigha] & "/" & Forms![frmData-Mining-Case]![selCreationHighc] & "#)"
    Else
        strCreation = "AND ([Creation Date] between #" & Forms![frmData-Mining-Case]![selCreationLowb] & "/" & Forms![frmData-Mining-Case]![selCreationLowa] & "/" & Forms![frmData-Mining-Case]![selCreationLowc] & "# AND #" & Forms![frmData-Mining-Case]![selCreationHighb] & "/" & Forms![frmData-Mining-Case]![selCreationHigha] & "/" & Forms![frmData-Mining-Case]![selCreationHighc] & "#)"
    End If
    sections = sections + 1
End If

If selCCAuthOn Then
    If sections = 0 Then
        strCCAuth = "([ExpAuth Current] like '" & Forms![frmData-Mining-Case]![selCCAuth] & "')"
    Else
        strCCAuth = "AND ([ExpAuth Current] like '" & Forms![frmData-Mining-Case]![selCCAuth] & "')"
    End If
    sections = sections + 1
End If

If selLSourceOn Then
    If sections = 0 Then
        If selLSourceNot = "Not" Then
            strLSource = strLSource & "([Lead Source] Not Like "
            For Each varItem In Me.selLSource.ItemsSelected
                strLSource = strLSource & Chr(34) & Me.[selLSource].ItemData(varItem) & Chr(34) & " And [Lead Source] Not Like "
            Next
            strLSource = Left(strLSource, Len(strLSource) - 28) & ")"
            sections = sections + 1
        Else
            If selLSourceNot = "All" Then
                strLSource = strLSource & "[Lead Source]"
             Else
                strLSource = strLSource & "([Lead Source] ="
                For Each varItem In Me.selLSource.ItemsSelected
                    strLSource = strLSource & Chr(34) & Me.[selLSource].ItemData(varItem) & Chr(34) & " OR [Lead Source] = "
             Next
                strLSource = Left(strLSource, Len(strLSource) - 20) & ")"
            End If
        sections = sections + 1
        End If
    Else
    If selLSourceNot = "Not" Then
            strLSource = strLSource & "And ([Lead Source] Not Like "
            For Each varItem In Me.selLSource.ItemsSelected
                strLSource = strLSource & Chr(34) & Me.[selLSource].ItemData(varItem) & Chr(34) & " And [Lead Source] Not Like "
            Next
            strLSource = Left(strLSource, Len(strLSource) - 28) & ")"
            sections = sections + 1
        Else
            If selLSourceNot = "All" Then
                strLSource = strLSource & "And ([Lead Source])"
             Else
                strLSource = strLSource & "And ([Lead Source] ="
                For Each varItem In Me.selLSource.ItemsSelected
                    strLSource = strLSource & Chr(34) & Me.[selLSource].ItemData(varItem) & Chr(34) & " OR [Lead Source] = "
             Next
                strLSource = Left(strLSource, Len(strLSource) - 20) & ")"
            End If
        sections = sections + 1
        End If
    End If
End If

If selCTypeOn Then
    If sections = 0 Then
        If selCTypeNot = "Not" Then
            strCType = strCType & "([Case Status] Not Like "
            For Each varItem In Me.selCType.ItemsSelected
                strCType = strCType & Chr(34) & Me.[selCType].ItemData(varItem) & Chr(34) & " And [Case Status] Not Like "
            Next
            strCType = Left(strCType, Len(strCType) - 28) & ")"
            sections = sections + 1
        Else
            If selCTypeNot = "All" Then
                strCType = strCType & "[Case Status]"
             Else
                strCType = strCType & "([Case Status] ="
                For Each varItem In Me.selCType.ItemsSelected
                    strCType = strCType & Chr(34) & Me.[selCType].ItemData(varItem) & Chr(34) & " OR [Case Status] = "
             Next
                strCType = Left(strCType, Len(strCType) - 20) & ")"
            End If
        sections = sections + 1
        End If
    Else
    If selCTypeNot = "Not" Then
            strCType = strCType & "And ([Case Status] Not Like "
            For Each varItem In Me.selCType.ItemsSelected
                strCType = strCType & Chr(34) & Me.[selCType].ItemData(varItem) & Chr(34) & " And [Case Status] Not Like "
            Next
            strCType = Left(strCType, Len(strCType) - 28) & ")"
            sections = sections + 1
        Else
            If selCTypeNot = "All" Then
                strCType = strCType & "And ([Case Status])"
             Else
                strCType = strCType & "And ([Case Status] ="
                For Each varItem In Me.selCType.ItemsSelected
                    strCType = strCType & Chr(34) & Me.[selCType].ItemData(varItem) & Chr(34) & " OR [Case Status] = "
             Next
                strCType = Left(strCType, Len(strCType) - 20) & ")"
            End If
        sections = sections + 1
        End If
    End If
End If

If selINTOn Then
    If sections = 0 Then
        If selINTNot = "Not" Then
            strINT = strINT & "([Administrator] Not Like "
            For Each varItem In Me.selINT.ItemsSelected
                strINT = strINT & Chr(34) & Me.[selINT].ItemData(varItem) & Chr(34) & " And [Administrator] Not Like "
            Next
            strINT = Left(strINT, Len(strINT) - 30) & ")"
            sections = sections + 1
        Else
            If selINTNot = "All" Then
                strINT = strINT & "[Administrator]"
             Else
                strINT = strINT & "([Administrator] ="
                For Each varItem In Me.selINT.ItemsSelected
                    strINT = strINT & Chr(34) & Me.[selINT].ItemData(varItem) & Chr(34) & " OR [Administrator] = "
             Next
                strINT = Left(strINT, Len(strINT) - 22) & ")"
            End If
        sections = sections + 1
        End If
    Else
        If selINTNot = "Not" Then
            strINT = strINT & "And ([Administrator] Not Like "
            For Each varItem In Me.selINT.ItemsSelected
                strINT = strINT & Chr(34) & Me.[selINT].ItemData(varItem) & Chr(34) & " And [Administrator] Not Like "
            Next
            strINT = Left(strINT, Len(strINT) - 30) & ")"
            sections = sections + 1
        Else
            If selINTNot = "All" Then
                strINT = strINT & "And ([Administrator])"
             Else
                strINT = strINT & "And ([Administrator] ="
                For Each varItem In Me.selINT.ItemsSelected
                    strINT = strINT & Chr(34) & Me.[selINT].ItemData(varItem) & Chr(34) & " OR [Administrator] = "
             Next
                strINT = Left(strINT, Len(strINT) - 22) & ")"
            End If
        sections = sections + 1
        End If
     End If
End If

If selBROn Then
    If sections = 0 Then
        If selBRNot = "Not" Then
            strBR = strBR & "([Branch] Not Like "
            For Each varItem In Me.selBR.ItemsSelected
                strBR = strBR & Chr(34) & Me.[selBR].ItemData(varItem) & Chr(34) & " And [Branch] Not Like "
            Next
            strBR = Left(strBR, Len(strBR) - 23) & ")"
            sections = sections + 1
        Else
            If selBRNot = "All" Then
                strBR = strBR & "[Branch]"
             Else
                strBR = strBR & "([Branch] ="
                For Each varItem In Me.selBR.ItemsSelected
                    strBR = strBR & Chr(34) & Me.[selBR].ItemData(varItem) & Chr(34) & " OR [Branch] = "
             Next
                strBR = Left(strBR, Len(strBR) - 15) & ")"
            End If
        sections = sections + 1
        End If
    Else
     If selBRNot = "Not" Then
            strBR = strBR & "And ([Branch] Not Like "
            For Each varItem In Me.selBR.ItemsSelected
                strBR = strBR & Chr(34) & Me.[selBR].ItemData(varItem) & Chr(34) & " And [Branch] Not Like "
            Next
            strBR = Left(strBR, Len(strBR) - 23) & ")"
            sections = sections + 1
        Else
            If selBRNot = "All" Then
                strBR = strBR & "And ([Branch])"
             Else
                strBR = strBR & "And ([Branch] ="
                For Each varItem In Me.selBR.ItemsSelected
                    strBR = strBR & Chr(34) & Me.[selBR].ItemData(varItem) & Chr(34) & " OR [Branch] = "
             Next
                strBR = Left(strBR, Len(strBR) - 15) & ")"
            End If
        sections = sections + 1
        End If
    End If
End If

If selContactOn Then
    If sections = 0 Then
        strContact = "([Contact By] like '" & Forms![frmData-Mining-Case]![selContact] & "')"
    Else
        strContact = "AND ([Contact By] like '" & Forms![frmData-Mining-Case]![selContact] & "')"
    End If
    sections = sections + 1
End If

If selLTypeOn Then
    If sections = 0 Then
        If selLTypeNot = "Not" Then
            strLType = strLType & "([Lead Type] Not Like "
            For Each varItem In Me.selLType.ItemsSelected
                strLType = strLType & Chr(34) & Me.[selLType].ItemData(varItem) & Chr(34) & " And [Lead Type] Not Like "
            Next
            strLType = Left(strLType, Len(strLType) - 26) & ")"
            sections = sections + 1
        Else
            If selLTypeNot = "All" Then
                strLType = strLType & "[Lead Type]"
             Else
                strLType = strLType & "([Lead Type] ="
                For Each varItem In Me.selLType.ItemsSelected
                    strLType = strLType & Chr(34) & Me.[selLType].ItemData(varItem) & Chr(34) & " OR [Lead Type] = "
             Next
                strLType = Left(strLType, Len(strLType) - 18) & ")"
            End If
        sections = sections + 1
        End If
    Else
    If selLTypeNot = "Not" Then
            strLType = strLType & "And ([Lead Type] Not Like "
            For Each varItem In Me.selLType.ItemsSelected
                strLType = strLType & Chr(34) & Me.[selLType].ItemData(varItem) & Chr(34) & " And [Lead Type] Not Like "
            Next
            strLType = Left(strLType, Len(strLType) - 26) & ")"
            sections = sections + 1
        Else
            If selLTypeNot = "All" Then
                strLType = strLType & "And ([Lead Type])"
             Else
                strLType = strLType & "And ([Lead Type] ="
                For Each varItem In Me.selLType.ItemsSelected
                    strLType = strLType & Chr(34) & Me.[selLType].ItemData(varItem) & Chr(34) & " OR [Lead Type] = "
             Next
                strLType = Left(strLType, Len(strLType) - 18) & ")"
            End If
        sections = sections + 1
        End If
    End If
End If

If selCStatusOn Then
    If sections = 0 Then
        If selCStatusNot = "Not" Then
            strCStatus = strCStatus & "([Customer Status] Not Like "
            For Each varItem In Me.selCStatus.ItemsSelected
                strCStatus = strCStatus & Chr(34) & Me.[selCStatus].ItemData(varItem) & Chr(34) & " And [Customer Status] Not Like "
            Next
            strCStatus = Left(strCStatus, Len(strCStatus) - 32) & ")"
            sections = sections + 1
        Else
            If selCStatusNot = "All" Then
                strCStatus = strCStatus & "[Customer Status]"
             Else
                strCStatus = strCStatus & "([Customer Status] ="
                For Each varItem In Me.selCStatus.ItemsSelected
                    strCStatus = strCStatus & Chr(34) & Me.[selCStatus].ItemData(varItem) & Chr(34) & " OR [Customer Status] = "
             Next
                strCStatus = Left(strCStatus, Len(strCStatus) - 24) & ")"
            End If
        sections = sections + 1
        End If
    Else
    If selCStatusNot = "Not" Then
            strCStatus = strCStatus & "And ([Customer Status] Not Like "
            For Each varItem In Me.selCStatus.ItemsSelected
                strCStatus = strCStatus & Chr(34) & Me.[selCStatus].ItemData(varItem) & Chr(34) & " And [Customer Status] Not Like "
            Next
            strCStatus = Left(strCStatus, Len(strCStatus) - 32) & ")"
            sections = sections + 1
        Else
            If selCStatusNot = "All" Then
                strCStatus = strCStatus & "And ([Customer Status])"
             Else
                strCStatus = strCStatus & "And ([Customer Status] ="
                For Each varItem In Me.selCStatus.ItemsSelected
                    strCStatus = strCStatus & Chr(34) & Me.[selCStatus].ItemData(varItem) & Chr(34) & " OR [Customer Status] = "
             Next
                strCStatus = Left(strCStatus, Len(strCStatus) - 24) & ")"
            End If
        sections = sections + 1
        End If
    End If
End If

    strSQL = strSQL & " " & strCR & " " & strTown & " " & strPCode & " " & strCreation & " " & strCCAuth & " " & strLSource & " " & strCType & " " & strINT & " " & strBR & " " & strContact & " " & strLType & " " & strCStatus & ";"
    Set qdf = dbs.CreateQueryDef("dm-case", strSQL)
    
    DoCmd.OpenForm "frmDM-CaseCt", , , , , acHidden
    If Forms![frmDM-CaseCt]![CaseCt] = 0 Then
'       MsgBox "This search will return no records.  Please amend criteria.", vbOKOnly, "Search Error"
        DoCmd.OpenForm "frmDM-Zero-Matches"
        DoCmd.Close acForm, "frmDM-CaseCt", acSaveNo
        Exit Sub
    Else
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryDM-Update-Case1"
        DoCmd.OpenQuery "qryDM-Update-Case2"
        DoCmd.OpenQuery "qryDM-Update-Using-Case"
        DoCmd.OpenQuery "qryDM-Update-Using-Total"
        DoCmd.OpenQuery "qryDM-Update-Total"
        DoCmd.SetWarnings True
        DoCmd.Close acForm, "frmDM-CaseCt", acSaveNo
    
        DoCmd.OpenForm "frmData-Mining-Results"
    End If
    
Exit_cmdScan_Click:
    Exit Sub

Err_cmdScan_Click:
    If Err.Number = "3012" Then
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acQuery, "dm-case"
        DoCmd.OpenQuery "qryDM-Update-Total-Case"
        Resume cmdScan_Start
        DoCmd.SetWarnings True
    End If
    MsgBox Err.Description
    Resume Exit_cmdScan_Click
     
End Sub
 

Attachments

  • CaseScreen.jpg
    CaseScreen.jpg
    68.3 KB · Views: 235
You must log in or register to reply here.

Similar threads

C
Problem with 'Like' SQL filter
Replies
6
Views
623
Josef P.
J
A
Search button for more than one field search
Replies
3
Views
221
Ken Sheridan
Ken Sheridan
H
Problem with Search Boxes
2
Replies
21
Views
2,289
theDBguy
theDBguy
A
Update of stock quantity
Replies
19
Views
1,689
xavier.batlle
X
C
Majp Search and Filter
Replies
7
Views
623
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom