Clear and refresh subform button not working

[Garindan]

Hi all,

on frmCustomerSearch I have two buttons with code, a Search button and a Clear button. I would like the clear button to clear the search boxes and then refresh the subform 'fsubCustomerSearchDetails' to show all records in the subform. It clears the search boxes fine, but I cant get it to refresh and show all records.

Here's the code:-

Option Compare Database
Option Explicit

Private Sub btnClear_Click()

    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtFirstName_Initial = ""
    Me.txtSurname = ""
    Me.txtBusinessName = ""
    Me.txtEmailAddress = ""
    Me.txtHouse_FlatNumber = ""
    Me.cmbStreet = ""
    Me.cmbArea = ""
    Me.cmbTown_City = ""
    Me.cmbCounty = ""
    Me.txtPostCode = ""

    
End Sub

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.fsubCustomerSearchDetails.Form.RecordSource = "SELECT * FROM qselCustomerSearchDetails " & BuildFilter
    
    ' Requery the subform
    Me.fsubCustomerSearchDetails.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 First Name/Initial
    If Me.txtFirstName_Initial > "" Then
        varWhere = varWhere & "[FirstName_Initial] LIKE """ & Me.txtFirstName_Initial & "*"" AND "
    End If
    
    ' Check for Surname
    If Me.txtSurname > "" Then
        varWhere = varWhere & "[Surname] LIKE """ & Me.txtSurname & "*"" AND "
    End If
    
    ' Check for Business Name
    If Me.txtBusinessName > "" Then
        varWhere = varWhere & "[BusinessName] LIKE """ & Me.txtBusinessName & "*"" AND "
    End If
    
    ' Check for Email Address
    If Me.txtEmailAddress > "" Then
        varWhere = varWhere & "[EmailAddress] LIKE """ & Me.txtEmailAddress & "*"" AND "
    End If
    
    ' 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 Area
    If Me.cmbArea > "" Then
        varWhere = varWhere & "[Area] LIKE """ & Me.cmbArea & "*"" AND "
    End If
    
    ' Check for Town/City
    If Me.cmbTown_City > "" Then
        varWhere = varWhere & "[Town_City] LIKE """ & Me.cmbTown_City & "*"" AND "
    End If
    
    ' Check for County
    If Me.cmbCounty > "" Then
        varWhere = varWhere & "[County] LIKE """ & Me.cmbCounty & "*"" AND "
    End If
    
    ' Check for Post Code
    If Me.txtPostCode > "" Then
        varWhere = varWhere & "[PostCode] LIKE """ & Me.txtPostCode & "*"" 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

I have tried btnClear_Click() with Me.fsubCustomerSearchDetails.Requery as well but it doesn't work. Any ideas? Many thanks in advance!

 

[DCrake]

On your button clear event after you have cleared the last contol add the following lines

DoEvents
Me.fsubCustomerSearchDetails.Form.RecordSource = "SELECT * FROM qselCustomerSearchDetails;"

Me.fsubCustomerSearchDetails.Requery

 

[Garindan]

Brilliant, works perfectly! Many thanks!!