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 -- this is not required to requery because you have changed the Record Source...
-
Me.SMS_ValidateSubform.Requery
-
End Sub
-
-
Private Function BuildFilter() As String
-
'I would remove all these variables, unless you somehow use them elsewhere in the code
-
Dim varWhere As String
-
Dim strColor As String 'as an example
-
Dim intItem As Integer '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 = " WHERE [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 & " AND [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.