Adapt search code to require entry? (1 Viewer)

[Garindan]

Hi all, this is my code for a search form. At the moment if searched with nothing entered into the combo boxes it returns all records.

Could anybody tell me how I can adapt it to require something to be entered into the combo/text boxes?

Many thanks!

Option Compare Database
Option Explicit

Private Sub btnClear_Click()

    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtHouse_FlatNumber = ""
    Me.cmbStreet = ""
    Me.cmbTown_City = ""
    
    DoEvents
    Me.fsubCustomerEntryRestrictedDetails.Form.RecordSource = "SELECT * FROM qselCustomerEntryRestrictedDetails WHERE 1=0;"
    
End Sub

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.fsubCustomerEntryRestrictedDetails.Form.RecordSource = "SELECT * FROM qselCustomerEntryRestrictedDetails " & BuildFilter
    
    ' Requery the subform
    Me.fsubCustomerEntryRestrictedDetails.Requery
    
End Sub

Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    ' Check for House/Flat Number
    If Me.txtHouse_FlatNumber > "" Then
        varWhere = varWhere & "[House_FlatNumber] LIKE """ & Me.txtHouse_FlatNumber & "*"" AND "
    End If
    
    ' Check for Street
    If Me.cmbStreet > "" Then
        varWhere = varWhere & "[Street] LIKE """ & Me.cmbStreet & "*"" AND "
    End If
    
    ' Check for Town/City
    If Me.cmbTown_City > "" Then
        varWhere = varWhere & "[Town_City] LIKE """ & Me.cmbTown_City & "*"" AND "
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

 

[TJPoorman]

Private Sub btnSearch_Click()
    
    ' Update the record source
    If BuildFilter = "" Then
        MsgBox "Nothing to filter"
    Else
        Me.fsubCustomerEntryRestrictedDetails.Form.RecordSource = "SELECT * FROM qselCustomerEntryRestrictedDetails " & BuildFilter
    
        ' Requery the subform
        Me.fsubCustomerEntryRestrictedDetails.Requery
    End If
End Sub

 

[MS$DesignersRCretins]

Can't you simply test BuildFilter before applying it? maybe prompt user to enter something, and exit the code. (Test vs. null, a la Debug.Print IsNull(BuildFilter))

 

[MS$DesignersRCretins]

Sorry, I walked on TJPoorman's more detailed answer. Read his instead! Sorry TJ. I need to remember to refresh before posting!

 

[Garindan]

Brilliant! Thankyou!!

 

[Garindan]

Sorry, one more question...

How can I adapt it so something needs to be entered in each box? (there's 3)

I've been trying to use 'If Not IsNull(Me.cboMoveTo) Then' but I can't get it.

Thankyou!

 

[MS$DesignersRCretins]

Try testing if LEN is <1 perhaps like

if (len(Me.txtHouse_FlatNumber)<1 _
    or len(Me.cmbStreet)<1 _
    or len(Me.cmbTown_City)<1)

I haven't used combo boxes for a while but one way, maybe not the best, would be to have a default choice like "(none)" (no quotes) and react to that.

Sometimes IsNull or Is Null is the way to go, and sometimes testing for a zero length string is, depending on the situation. I think you've hit a case where the latter applies.

 

[Garindan]

Try testing if LEN is <1

Thanks. This seemed to work well, except in testing I have found that if I first put something in a box and search, then delete what's in the box and search, it will still search.

After deleting an entry from text/combo box it behaves like there's something still in there. This is a blank " " value isn't it?

Any idea's?

 

[TJPoorman]

I use the NZ function. This will change a null value to "", then you test for ""

if nz(Me.txtHouse_FlatNumber,"")="" _
    or nz(Me.cmbStreet,"")="" _
    or nz(Me.cmbTown_City,"")="")

 

[Garindan]

Great thank you! All working

 

[Garindan]

Sorry about this, I've been away from vba for a long time...

I need to change

If Me.cmbTown_City > "" Then
        varWhere = varWhere & "[Town_City] LIKE """ & Me.cmbTown_City & "*"" AND "
    End If

to not be wildcard, i.e. only exact matches. But I can't get it :banghead:

 

[Garindan]

Got it... I just changed LIKE to = and removed the asterisk.

If Me.txtHouse_FlatNumber > "" Then
        varWhere = varWhere & "[House_FlatNumber] = """ & Me.txtHouse_FlatNumber & """ AND "
    End If

That's right isn't it? They're all text fields. I get very confused with all the quote marks and where they go!

 

[MS$DesignersRCretins]

Got it... I just changed LIKE to = and removed the asterisk.

You got it right. Here's a twist that I sometimes add that may not apply to you: occasionally instead of going for exact matches I search for asterisk-string-asterisk and use LIKE anyway, like if I want "Smith" I search for "*Smith*" and use LIKE. Maybe I'm not sure that someone is Beth or Elizabeth so I use *Beth*

If Me.txtHouse_FlatNumber > "" Then
        varWhere = varWhere & "[House_FlatNumber] = """ & Me.txtHouse_FlatNumber & """ AND "
    End If

That's right isn't it? They're all text fields. I get very confused with all the quote marks and where they go!

That looks syntactically okay. I'll admit that sometimes I just keep adding double quotes until I have enough . You might take advantage of the debug window, going Control-G in VBA, and type
?Me.txtHouse_FlatNumber
or
?varWhere
and such, maybe just before and just after the assignment, as you step through your code using the F8.key.

 

[Garindan]

Great, thank you