Help with this Search Form by Gromit which I have adapted

boknoval

Registered User.
Local time
Tomorrow, 06:24
Joined
Mar 9, 2012
Messages
25
(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
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
 

Attachments

Okay Bok, I have made several changes to your code.. But before that, I have some few comments, that I believe will help you in future..

1. When using code that someone has written, make sure you first understand on how the function works. So if in case you make some modifications you will know what lines to modify based on the changes.

2. Always use Debug.Print when creating dynamic Queries so you can see your progress on the immediate window, which will show any syntax errors or missing parentheses.

Coming to your code, Well I will try to walk along the code so I do not miss anything..
1. I know the other code has the test for checking a Variant as..
Code:
Me.txtOccupancy > ""
But I am not sure it is the best way to check for condition.. As Null > "" will return Null not a Boolean value.. I have replaced all Null Checks with
Code:
Len(Me.txtCity & vbNullString) > 0
This condition will Check for the Len() the Variant variable along with a vbNullString if it is > 0 which denotes that the Variable will contain something..

2. You have used " to identify the Strings from a variable..
Code:
"[CITY] LIKE """ & Me.txtCity & "*"" AND "
Which is a bit messy and confusing, normally I ise Single Quotes, something like..
Code:
"[CITY] LIKE '" & Me.txtCity & "*'"
Which makes it easier to identify the Value String from regular Strings..

3. Using Parentheses is a very important part of generating Query.. So I have added them as the Query is generated..

4.You have created the WHERE condition, and then added a New Criteria for Property Status and again reconstructed the WHERE, which lead to result something like..
Code:
SELECT * FROM qryPropertiesData WHERE [COLOR=Red]WHERE[/COLOR] ( [SUB_AREA] = "Albion" )
So it gives two where.. So I have deleted the previous WHERE construction.. (This is the reason you need to use Debug.Print, you will see the error straight off..

5. Some code can be replaced with simple structures, like
Code:
If IsNull(varProStatus) Then
        ' do nothing
Else
Instead of having a If Else, you can replace it with..
Code:
If Not IsNull(varProStatus) Then

6. After constructing each Sub Filters you have to use the Logical operator AND to separate them, which I have added.. Without that your code will be..
Code:
SELECT * FROM qryPropertiesData WHERE ( [SUB_AREA] = "Albion" )( [PRO_STATUS] = "T" )
So with all the above, that is the major changes I made.. Hope this helps..
 
Last edited:
Here is the changed Code.. I have highlighted the changes..
Code:
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  [COLOR=Green]' Main filter[/COLOR]
    varSubArea = Null  [COLOR=Green]' Subfilter used for Sub-Area[/COLOR]
    varProStatus = Null [COLOR=Green]' Subfilter used for Property Status[/COLOR]
    
   [COLOR=Green] ' Check for EQUAL Property Type[/COLOR]
    If [COLOR=Blue]Len(Me.txtPropertyType & vbNullString) > 0[/COLOR] Then
        varWhere = varWhere & "[COLOR=Blue]([/COLOR][PRO_TYPE] = [B][COLOR=Blue]'[/COLOR][/B]" & Me.txtPropertyType & "[COLOR=Blue][B]'[/B])[/COLOR] [COLOR=Blue][B]AND[/B][/COLOR] "
    End If
    
   [COLOR=Green] ' Check for LIKE Occupancy[/COLOR]
    If [COLOR=Blue]Len(Me.txtOccupancy & vbNullString) > 0[/COLOR] Then
        varWhere = varWhere & "[COLOR=Blue]([/COLOR][OCCUPANCY] LIKE [B][COLOR=Blue]'[/COLOR][/B]" & Me.txtOccupancy & "*[B][COLOR=Blue]')[/COLOR][/B] [COLOR=Blue][B]AND[/B][/COLOR] "
    End If
    
   [COLOR=Green] ' Check for LIKE City[/COLOR]
    If [COLOR=Blue]Len(Me.txtCity & vbNullString) > 0[/COLOR] Then
        varWhere = varWhere & "[COLOR=Blue]([/COLOR][CITY] LIKE '" & Me.txtCity & "*[COLOR=Blue][B]'[/B])[/COLOR] [COLOR=Blue][B]AND[/B][/COLOR] "
    End If
   
   [COLOR=Green] ' Check for Sub-Area in multiselect list[/COLOR]
    For Each varItemSubArea In Me.lstSubArea.ItemsSelected
        varSubArea = varSubArea & "[COLOR=Blue]([/COLOR][SUB_AREA] = '" & _
                    Me.lstSubArea.ItemData(varItemSubArea) & "'[COLOR=Blue])[/COLOR] OR "
    Next
    
   [COLOR=Green] ' Test to see if we have subfilter for Sub-Area...[/COLOR]
   [COLOR=Blue] If Not IsNull(varSubArea) [/COLOR]Then
       [COLOR=Green] ' strip off last "OR" in the filter[/COLOR]
        If Right(varSubArea, 4) = " OR " Then
            varSubArea = Left(varSubArea, Len(varSubArea) - 4)
        End If
        [COLOR=Green]' Add some parentheses around the subfilter[/COLOR]
        varWhere = varWhere & "(" & varSubArea & ")[COLOR=Blue][B] AND [/B][/COLOR]"
    End If
        
   [COLOR=Green] ' Check for Property Status in multiselect list[/COLOR]
    For Each varItemProStatus In Me.lstProStatus.ItemsSelected
        varProStatus = varProStatus & "[COLOR=Blue]([/COLOR][PRO_STATUS] = [COLOR=Blue][B]'[/B][/COLOR]" & _
                    Me.lstProStatus.ItemData(varItemProStatus) & "[COLOR=Blue][B]'[/B])[/COLOR] OR "
    Next
    
    [COLOR=Green]' Test to see if we have subfilter for Sub-Area...[/COLOR]
    [COLOR=Blue]If Not IsNull(varProStatus)[/COLOR] Then
       [COLOR=Green] ' strip off last "OR" in the filter[/COLOR]
        If Right(varProStatus, 4) = " OR " Then
            varProStatus = Left(varProStatus, Len(varProStatus) - 4)
        End If
        
        [COLOR=Green]' Add some parentheses around the subfilter[/COLOR]
        varWhere = varWhere & "(" & varProStatus & ")[B][COLOR=Blue] AND[/COLOR][/B] "
    End If
    
   [COLOR=Green] ' Check if there is a filter to return...[/COLOR]
    If Not IsNull(varWhere) Then
        varWhere = "WHERE [COLOR=Blue](" [/COLOR]& varWhere
        [COLOR=Green]' strip off last "AND" in the filter[/COLOR]
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
        varWhere = varWhere & [COLOR=Blue]")"[/COLOR]
    End If
    BuildFilter = varWhere
End Function
 
WOW! That was quite very informative Paul! Thank you very much! Aside from just finding ways to solve my dilemma, I've really learned something from you. Really appreciated your effort to explain everything to me. Thanks!
 
You are most welcome.. :) Glad to help..

While trying to explain things I might have bombarded so much information, so please take time to understand what is happening in the changed code.. Post back if you have any doubt, I will do my best to help you here..
 
Yeah, well, the first time I read your explanation, I was like 'what?'.. But when I read it couple more times, that's when I understood every changes you have made. I would like to commend you for the effort to teach someone like me! I really mean it from the bottom of my heart. Thank you very much!
 

Users who are viewing this thread

Back
Top Bottom