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

    Search Button in a Form

    Hi. I actually created a Form that has txtBox, CommandButton and a subform below.. My search is working and I would like to add on my search code like..

    Example : on my database i had this name "John Patrick" and "Patrick John" on my current code.. when i put "John" on the textbox and press CommandButton(search) it will show me "John Patrick" and not "Patrick John". I want to ask if what would I do so it can also show "Patrick John" when I just put "John" on the txtbox?

    BTW here's my code..
    --------------------------------------------------------------------------------------------
    LEGEND :
    Command33 -- Button that i consider as Search Button
    GOLDNEWCONNECT_ValidateSubform -- a form where my database/table will show
    GOLDNEWCONNECT -- Database/Table
    Text34 -- Textbox where i will put the value/data that i want to search


    Emboss_Name -- the name of the column where the data that i would like to search is stored.

    ---------------------------------------------------------------------------------------------

    Code:
    Private Sub Command33_Click()
     'Update the record source
        Me.GOLDNEWCONNECT_ValidateSubform.Form.RecordSource = "SELECT * FROM GOLDNEWCONNECT " & BuildFilter
            
        ' Requery the subform
        Me.GOLDNEWCONNECT_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
          
        
        If Me.Text34 > "" Then
            varWhere = varWhere & "[Emboss_Name] LIKE """ & Me.Text34 & "*"" 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
    Thank you in Advanced (^.~)olll

  2. #2
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    Oh I think this is done. Just playing around Wildcards and I arrived at this Code.

    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                 If Me.Text34 > "" Then         varWhere = varWhere & "[Emboss_Name] LIKE ""*" & Me.Text34 & "*"" 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

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You would add a wildcard to the beginning the same way you've added one at the end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    Yes sir. Thank you!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Looks like you figured it out first.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    hehehehehe!

  7. #7
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    ow by the way sir pbaldy. I would like to ask if what if I want to search multiple record and I'll just separate it with comma?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm sorry, I'm not clear on the question.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    uhmm sorry for that sir. like i have a table with name "Jessica", "John", "Jasper" on my current search code.. i can search them One by One.. then now i want to search them all in just one click.. like i will put this on my txtbox.. jessica,john,jasper.. then my search form will show me the record of jessica john and jasper.. uhmm did i make it clear? hehe!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you could build an In() clause. Finished, and with wildcards, it would look like

    WHERE FieldName In('*Jessica*', '*john*', '*jasper*')

    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    uhmm not specifically Jessica,John and Jasper.. like on my search.. when i put anything on the textbox.. it will look on that data..

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ummm, I know. I said you could build an In() clause, and that's what it would look like when you were done.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    Hi sir. Sorry for being a novice. Please be more patient. I play around that In() but still I don't know where to put that code in my code? hehe.

  14. #14
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    I came up with another code. but that code is filter and i don't know where to put LIKE because it can search multiple data using comma but i need to put the exact value..

  15. #15
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    here's the code..

    Code:
    Private Sub Command26_Click()
        Dim strFilter As String
       
       If Me.Text24 > "" Then
        strFilter = Replace(Text24, ", ", ", ")
        strFilter = "[Card_Number] In('" & Replace(strFilter, ", ", "','") & "') "
        End If
    
        If strFilter <> "" Then
            Me.CSP_ValidateSubform.Form.Filter = strFilter
            Me.CSP_ValidateSubform.Form.FilterOn = True
        Else
            Me.CSP_ValidateSubform.Form.Filter = ""
            Me.CSP_ValidateSubform.Form.FilterOn = False
        End If
            
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search Button
    By bomber72 in forum Forms
    Replies: 6
    Last Post: 08-03-2011, 11:44 AM
  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
  •  
Other Forums: Microsoft Office Forums