Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48

    Search Button in a Form not Working

    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.

    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
    Code:
    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    Yes i already put space on it. but still i had the same result?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    Have you step debugged? Follow the code as it executes. See where is deviates from expected behavior, fix. Repeat.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    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!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    oh sorry I didn't post the code that has space on it.. Yes it's okay.

    InstantCard2.zip

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    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.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    Don't change name of the form, change name of the container control that holds the form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    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?

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    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.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,674
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    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?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search Button in a Form
    By spideynok in forum Forms
    Replies: 14
    Last Post: 03-18-2012, 10:08 PM
  2. Button to search table with any field in form
    By sephiroth2906 in forum Forms
    Replies: 3
    Last Post: 04-19-2011, 11:17 AM
  3. Replies: 9
    Last Post: 02-15-2011, 03:05 PM
  4. search button on Form
    By josejuancruz in forum Access
    Replies: 1
    Last Post: 12-23-2010, 07:21 PM
  5. Replies: 3
    Last Post: 01-14-2010, 08:32 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums