Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    4

    Unanswered: Using a search box within an Access form

    I have added a search box to the header of an Access form starting with code I found in an article by Susan Harkins on TechRepublic.com. Add a search control to an Access form | TechRepublic

    The search works, with problems. The code is below. (My text box is Text59, my table is called songs, and the search field is called title.)

    Here are the problems:

    1) I have to enter my search string in the search box with quotation marks, e.g. "God Bless America". How can I fix this so I don't need the quotation marks?

    2) I would like to search by substring. I would like the search to retrieve the first matching record alphabetically. So if I am searching for "God Bless America" it might be all I have to type is "God Bless A" and it will go to the correct record. If I am not getting ahead of myself, could this mean it would search by a substring that is the length of the search string I entered? So some function like len(Text59) would return a value of that length (in this example 11) and then search the table with a substring of len?

    Thanks. Tony


    Private Sub Text59_AfterUpdate()
    'Find record based on contents of Text59.

    Dim strSearch As String

    On Error GoTo errHandler

    'Delimited for text search.

    'strSearch = "songs.title = " & Chr(39) & Me!Text59.Value & Chr(39)

    'Delimited for numeric values.

    strSearch = "songs.title = " & Me!Text59.Value

    'Find the record.

    Me.RecordsetClone.FindFirst strSearch

    Me.Bookmark = Me.RecordsetClone.Bookmark

    Exit Sub

    errHandler:

    MsgBox "Error No: " & Err.Number & "; Description: " & _
    Err.Description

    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Remove (or comment) the line:
    Code:
    strSearch = "songs.title = " & Me!Text59.Value
    It can only be used when searching numeric values (which a song title usually is not) and forces you to enclose the contents of Text59 in quotation marks.

    2. Uncomment this line (remove the single quotation mark at the beginning of the line):
    Code:
    'strSearch = "songs.title = " & Chr(39) & Me!Text59.Value & Chr(39)
    Then change it to:
    Code:
    strSearch = "songs.title Like '" & Me!Text59.Value & "*'"
    3. Add this condition:
    Code:
    Me.RecordsetClone.FindFirst strSearch
    If Me.RecordsetClone.NoMatch = False Then
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    This will prevent an error from occuring when the search fails.

    4. You should change the name of the TextBox (Text59) to something more meaningful.
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    4
    Thanks for your help.
    Last edited by bellczar; 02-16-13 at 04:38.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For eliminating the error that occurs when the searched string contains an apostrophe, use:
    Code:
    strSearch = "songs.title Like '" & Replace(Me!Text59.Value, "'", "''") & "*'"
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •