Search/Filter Working but has some error

[spideynok]

Hi! I have a code..

Private Sub Command18_Click()
'Update the record source
    Me.SMS_ValidateSubform.Form.RecordSource = "SELECT * FROM SMS " & BuildFilter
        
    ' Requery the subform
    Me.SMS_ValidateSubform.Requery
End Sub

and..

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    ' Check for LIKE First Name
    If Me.Text16 > "" Then
        varWhere = varWhere & "[Card_Number] LIKE """ & Me.Text16 & "*"" 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

Its working on my first project. but when i use it on my new project which is the same with my first project. It has a problem regarding on filtering. When I search/filter on my new project, it only show the first record either I put a "Card_Numbe r" on that or it is Null.

I can't see any error on my code. but I'm confuse about what's happening when I'm searching/filtering a record.

Thanks in advanced (~.^)olll

[twinnyfo]

spideynok,

I may have found some problem areas in your filter string generation, but I will also make a few recommendations for you, which may sound picky, but may also help in the long run.

First, I would suggest using appropriate names for your controls, such as naming the command buttons according to what they do, and naming text boxes according to what data they contain. This helps you (and perhaps anyone coming after you) understand what any particular control is for. See my minor changes below....

Second, I'm not sure why you are declaring your function results and variables as Variants. This sets aside undue resources for essentially an "unknown" type of variable, then changes the data types once it determines what is going into that variable. Since filters are strings, set your variable types for the filter text as strings.

Also, "it appears" that this code is just looking at the card number on the form, then filtering the records by that card number (i.e. in a search box???) plus additional variables from the form? If this is the case, I have modified the code below based on that presumption. If this is not the case, please clarify, because you have your filter appending an additional "AND" that never needs to be there in the first place, then removing it again. However, I do notice several other variables listed, so I've also inferred some code to assist.

Your Filter was missing the "WHERE" required for the SELECT statement. See below for how this was added. It was also missing the final semicolon ";", which is required for every SELECT statement.

I think I've captured the essence of what you need below, but please clarify if I've missed the mark.

Option Compare Database
Option Explicit

Private Sub cmdFilterRecords_Click() 
    'Update the record source
    Dim strFilter as String  'I prefer to establish a string for this, then set its value based on the function, although it's not required, you get the same results
    strFilter = BuildFilter
    'Keep in mind that this will return ALL records if the form is blank....

    Me.SMS_ValidateSubform.Form.RecordSource = "SELECT * FROM SMS " & strfilter & ";"
    ' Requery the subform -- [B][U]this is not required to requery because you have changed the Record Source...[/U][/B]
    Me.SMS_ValidateSubform.Requery 
End Sub

Private Function BuildFilter() As [B][U]String[/U][/B]
    'I would remove all these variables, unless you somehow use them elsewhere in the code
    Dim varWhere As [B][U]String[/U][/B]
    Dim strColor As [B][U]String[/U][/B] 'as an example
    Dim intItem As [B][U]Integer[/U][/B] 'as an example
    Dim intIndex As Integer 
  
    BuildFilter = ""  ' Reset the Main filter--I don't like setting to Null 
  
    ' Check for LIKE First Name - card number must be TEXT, this assumes you only have the beginning characters of the card in the form.  This should probably use an "=" instead of "LIKE" and remove the "*"
    If Me.txtCardNumber <> "" Then 
        BuildFilter = " [B][U]WHERE[/U][/B] [Card_Number] LIKE '" & Me.txtCardNumber & "*'" 
    End If 
  
     ' Check for LIKE Second Name -- MUST BE TEXT
    If Me.txtColor <> "" Then
        'This nested If makes sure we always have a WHERE in the statement or just adds to it
        If BuildFilter = "" Then
            BuildFilter = " WHERE [Color] = '" & Me.txtColor & "'" 
        Else
            BuildFilter = BuildFilter & " [B][U]AND[/U][/B] [Color] = '" & Me.txtColor & "'" 
        End If
    End If
  
     ' Check for LIKE Third Name -- for INTEGER variable types
    If Me.txtItem <> "" Then 
        If BuildFilter = "" Then
            BuildFilter = " WHERE [Item] = " & Me.txtItem
            'Notice no single quote in this expression 
        Else
            BuildFilter = BuildFilter & " AND [Item] = " & Me.txtItem 
        End If
    End If 
 
    'I would remove this entire section of code below....
    ' 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  
End Function

I hope this helps. SELECT statements based on variables can be tricky, so it might be wise to cycle through the teesting, one variable at a time, and use Debug.Print to see what your Filter String looks like throughout the stages of its development until you achieve your desired results.

As always, glad to offer additional help if you hit snags.