Change code to display zero results in subform on opening

[Garindan]

Hi all, I have created a form which searches and displays results in a subform. The search works perfectly and I can search on any combination of fields. However, on opening the form or pressing the clear button, all records are shown in the subform. I would like to change this so the subform is empty when the form is opened or the clear button is pressed. I gather I need to change the code under 'btnClear_Click()' as this also clears the search boxes on form opening, but I'm not sure how.

Can anyone help? 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 = ""
    Me.cmbStatus = ""
    Me.cmbSalesperson = ""
    
    DoEvents
    Me.fsubCustomerSearchDetails.Form.RecordSource = "SELECT * FROM qselCustomerSearchDetails;"

    Me.fsubCustomerSearchDetails.Requery

    
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 for Status
    If Me.cmbStatus > "" Then
        varWhere = varWhere & "[Status] LIKE """ & Me.cmbStatus & "*"" AND "
    End If
    
    ' Check for Salesperson
    If Me.cmbSalesperson > "" Then
        varWhere = varWhere & "[Salesperson] LIKE """ & Me.cmbSalesperson & "*"" 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

 

[Garindan]

Any ideas anyone? Many thanks in advance!

 

[JANR]

In your btnClear sub you set the recordsorce to ALL records. change that to vbNullString

.....
Me.fsubCustomerSearchDetails.Form.RecordSource = [B]vbNullString [/B]
 
Me.fsubCustomerSearchDetails.Requery

Also you don't need the Requery since setting a Recordsource will force a requery.

JR

 

[Garindan]

Brilliant thanks. That works, except it shows a blank record with #Name? in every text box. Is there any way to change that?

 

[JANR]

ooops I wasen't thinking about bound forms, try and set the recordsourse with an impossible where-clause.

Me.fsubCustomerSearchDetails.Form.RecordSource = "SELECT * FROM qselCustomerSearchDetails [B]WHERE 1=0[/B];"

JR

 

[Garindan]

Excellent, works perfectly! Many many thanks!!

 

[JANR]

No problem, learning new tricks is part of the game.

JR