(im a new member so i can;t post with links properly... replace (dot) with . )
Gromit's Search Form Reference:
access-programmers(dot)co(dot)uk/...ad.php?t=99353
I have adapted his search form but I can't make it to work properly.
When I click the search button without entering or selecting criteria, it keeps on returning an earror. I need your help guys. I am not really a programmer.
Here's the code
I have also attached the my database. Thanks
Gromit's Search Form Reference:
access-programmers(dot)co(dot)uk/...ad.php?t=99353
I have adapted his search form but I can't make it to work properly.
When I click the search button without entering or selecting criteria, it keeps on returning an earror. I need your help guys. I am not really a programmer.
Here's the code
Code:
Option Compare Database
Option Explicit
Private Sub btnClear_Click()
Dim intIndex As Integer
Dim intIndex2 As Integer
' Clear all search items
Me.txtPropertyType = ""
Me.txtOccupancy = ""
Me.txtCity = ""
' De-select each item in Sub Area (multiselect list)
For intIndex = 0 To Me.lstSubArea.ListCount - 1
Me.lstSubArea.Selected(intIndex) = False
Next
' De-select each item in Sub Area (multiselect list)
For intIndex2 = 0 To Me.lstProStatus.ListCount - 1
Me.lstProStatus.Selected(intIndex2) = False
Next
End Sub
Private Sub btnSearch_Click()
' Update the record source
Me.frmsubProperties.Form.RecordSource = "SELECT * FROM qryPropertiesData " & BuildFilter
' Requery the subform
Me.frmsubProperties.Form.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 varSubArea As Variant
Dim varProStatus As Variant
Dim varItemSubArea As Variant
Dim varItemProStatus As Variant
Dim intSubAreaIndex As Integer
Dim intProStatusIndex As Integer
varWhere = Null ' Main filter
varSubArea = Null ' Subfilter used for Sub-Area
varProStatus = Null ' Subfilter used for Property Status
' Check for LIKE Property Type
If Me.txtPropertyType > "" Then
varWhere = varWhere & "[PRO_TYPE] LIKE """ & Me.txtPropertyType & "*"" AND "
End If
' Check for LIKE Occupancy
If Me.txtOccupancy > "" Then
varWhere = varWhere & "[OCCUPANCY] LIKE """ & Me.txtOccupancy & "*"" AND "
End If
' Check for LIKE City
If Me.txtCity > "" Then
varWhere = varWhere & "[CITY] LIKE """ & Me.txtCity & "*"" AND "
End If
' Check for Sub-Area in multiselect list
For Each varItemSubArea In Me.lstSubArea.ItemsSelected
varSubArea = varSubArea & "[SUB_AREA] = """ & _
Me.lstSubArea.ItemData(varItemSubArea) & """ OR "
Next
' Test to see if we have subfilter for Sub-Area...
If IsNull(varSubArea) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varSubArea, 4) = " OR " Then
varSubArea = Left(varSubArea, Len(varSubArea) - 4)
End If
' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varSubArea & " )"
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
' Check for Property Status in multiselect list
For Each varItemProStatus In Me.lstProStatus.ItemsSelected
varProStatus = varProStatus & "[PRO_STATUS] = """ & _
Me.lstProStatus.ItemData(varItemProStatus) & """ OR "
Next
' Test to see if we have subfilter for Sub-Area...
If IsNull(varProStatus) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varProStatus, 4) = " OR " Then
varProStatus = Left(varProStatus, Len(varProStatus) - 4)
End If
' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varProStatus & " )"
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 also attached the my database. Thanks