Search Button in a Form not Working

[spideynok]

Hi! I created a new project that has the same function and use with my first project. I put a Search/Filter button in a Form. Same as what I do on my first project. But on this one. Whenever I search a data if I put NULL value on the textbox it will show me 1 record(w/c is on the first row) and when i put a value on it(w/c has the same value on the database/table) it will show me nothing? I didn't change any of the code except for the txtbox,cmdbutton,database/table,form,subform names. Please help Thanks in advanced!

BTW i had this code :

LEGEND :
Command18 -- button i use as search button
Text16 -- txtbox where i put the value that i want to search
SMS -- database/table
SMS_ValidateSubform -- form where my database/table will show
Card_Number -- name of the column on my database/table which I'm searching on.

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

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

[June7]

Need a space after SMS before the " so that SMS and WHERE don't run together when the string is compiled. Surprised you don't get error message. Otherwise, can't see any other issue. Step debug.

[spideynok]

Yes i already put space on it. but still i had the same result?

[June7]

Have you step debugged? Follow the code as it executes. See where is deviates from expected behavior, fix. Repeat.

[spideynok]

Yes and it already stops at the first line..

Me.SMS_ValidateSubform.Form.RecordSource = "SELECT * FROM SMS" & BuildFilter

here.. what's wrong with it? hehe!

[June7]

Hmmm? I always give subform containers a name different from the object they hold, like ctrValidate. Sometimes that seems to make a difference but you said other code in this same structure works. That still doesn't show the space I suggested, are you sure it's there? To help any further I would have to analyze the project if you want to provide it.

[spideynok]

oh sorry I didn't post the code that has space on it.. Yes it's okay.

InstantCard2.zip

[June7]

I renamed the subform container to ctrValidate then I changed the code to refer to the container name. Then it works.
Me.ctrValidate.Form.RecordSource = "SELECT * FROM SMS " & BuildFilter
Me.ctrValidate.Requery

[spideynok]

Hi sir. Thank you. I did what you do. Change the code to ctrValidate and I also change the form SMS_ValidateSubform to ctrValidate. But it has an error saying Compile error : Method or data member not found.

[June7]

Don't change name of the form, change name of the container control that holds the form.

[spideynok]

umm your referring to the table name? sorry. I don't still get it. uhmm is it okay if you send me the one that you already edited?

[June7]

I am referring the subform container control. Subforms are created by putting a container control on another form. The container has a SourceObject property. The source object can be a table, query, form, report. With the form/subform in design view, click the 'subform' once. This selects the container control and will see its properties in the Properties dialog. Click again and now the object in the container is selected and will see its properties. The edit I suggest is simple once you grasp the concept of what a subform really is. Give it another try.

[spideynok]

uhmm so i need to change the table name?
because when I change to control source. to ctrValidate. it shows me error because it doesn't exist.

[June7]

Do not change table name. Do not change form name. Do not change ControlSource property.

Change the name of the container control.

Refer to this tutorial http://office.microsoft.com/en-us/ac...010278648.aspx
Toward the end where the narrator says 'name the subform' he is really naming the container control. Can also tell that the container is selected by the yellowish outline displayed.

[spideynok]

Hi again Sir June7. I did what was said on the video but when I run the Form. It automatically shows me ONE record and not all record on the table.

uhmm.. can I ask where can I find the Container Control?

I am referring the subform container control. Subforms are created by putting a container control on another form. The container has a SourceObject property. The source object can be a table, query, form, report. With the form/subform in design view, click the 'subform' once. This selects the container control and will see its properties in the Properties dialog. Click again and now the object in the container is selected and will see its properties. The edit I suggest is simple once you grasp the concept of what a subform really is. Give it another try.

am I right that he Container control is the Control source?