Search Form Using Combo Box

[dizy8]

Hi All,

I have this form below and this is working well with the below coding. I just want to add one more category which is [DocStatus]. Can help me please. I've been trying but it really wont run if I add one more field. Thank you guys.





Function SearchCriteria()
Dim Dept, strDocType, strStatus As String
Dim task, strCriteria As String


If IsNull(Me.cbo_Dept) Then
Dept = "(Dept) like '*'"


Else
Dept = "(Dept) = '" & Me.cbo_Dept & "'"
End If


If IsNull(Me.cbo_DocType) Then
strDocType = "(DocType) like '*'"
Else
strDocType = "(DocType) = '" & Me.cbo_DocType & "'ORDER BY tbl_TR.ID ASC"

End If


strCriteria = Dept & "And" & strDocType
task = "Select * from tbl_TR where " & strCriteria
Me.SubForm_TR.Form.RecordSource = task
Me.SubForm_TR.Form.Requery
End Function

[jojowhite]

try this:

Function SearchCriteria()    Dim Dept, strDocType, strStatus As String
    Dim task, strCriteria As String
        
    task = "Select * from tbl_TR"
    
    If Not IsNull(Me.cbo_Dept) Then
       strCriteria = strCriteria & "[Dept] = '" & Me.cbo_Dept & "' and "
    End If
    
    
    If Not IsNull(Me.cbo_DocType) Then
        strCriteria = strCriteria & "[DocType] = '" & Me.cbo_DocType & "' and "      'ORDER BY tbl_TR.ID ASC"
    
    End If
    
    If Len(strCriteria) <> 0 Then
        strCriteria = " " & Left$(strCriteria, Len(strCriteria) - 5)
    End If
    task = task & strCriteria & " ORDER BY tbl_TR.ID ASC;"
    
    Me.SubForm_TR.Form.RecordSource = task
    Me.SubForm_TR.Form.Requery
End Function

on design view, add this to cbo_dept and cbo_docType AfterUpdate event:

=SearchCriteria()

[CJ_London]

Step through your code to check values, use debug.print to see what you actually have and tell us any error messages you get.

on the face of it, you need spaces either side of ‘and’

[orange]

Further to the advice provided, be aware that:

Dim Dept, strDocType, strStatus As String

does not define each variable as a string. In this case only strStatus is a string.
Dept and strDocType will be dimmed as variant.

With vba you must explicitly dim each variable otherwise the default is variant.

You could use:

Dim Dept as string, strDocType as string, strStatus As String

OR

Dim Dept as string
Dim strDocType as string
Dim strStatus As String

[dizy8]

Hi Orange,

So I have this search form below. My search is based on what I enter in [Dept], [Doctype] and [PIC]. I followed what you have said but i have error below. Please help.

[Welshgasman]

Debug.Print strCriteria AND Task
In fact I would Debug.Print all of your concatenated strings.

Plus task is NOT defined.

[Micron]

Maybe some part of this will help you figure it out.
http://allenbrowne.com/ser-62.html

[davegri]

Looks to me like you are concatenating strings with no spaces between elements, which is jamming it all together as a meaningless string. Debug.print would instantly show this.
For example, "And" should be " And ", otherwise "And" gets crowded on both sides.

[jojowhite]

you may try to Replace your function with this one:

Function SearchCriteria()
    Dim task As String
    Dim strCriteria As String
        
    task = "Select * from tbl_TR"
    
    If Me.cbo_Dept.ListIndex <> -1 Then
       strCriteria = strCriteria & "[Dept] = '" & Me.cbo_Dept & "' And "
    End If
    
    If Me.cbo_DocType.ListIndex <> -1 Then
        strCriteria = strCriteria & "[DocType] = '" & Me.cbo_DocType & "' And "      'ORDER BY tbl_TR.ID ASC"
    
    End If
    
    If Me.cbo_PIC.ListIndex <> -1 Then
        strCriteria = strCriteria & "[PIC] = '" & Me.cbo_PIC & "' And "
    End If

    If Len(strCriteria) <> 0 Then
        strCriteria = " Where " & Left$(strCriteria, Len(strCriteria) - 5)
    End If
    
    task = task & strCriteria & " ORDER BY tbl_TR.ID ASC;"
    
    Me.SubForm_TR.Form.RecordSource = task
    Me.SubForm_TR.Form.Requery
End Function