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!
Could anybody tell me how I can adapt it to require something to be entered into the combo/text boxes?
Many thanks!
Code:
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