Using a search box within an Access form

[bellczar]

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. http://www.techrepublic.com/blog/mso...cess-form/2102



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

[alcapps]

strSearch = "songs.title = " & Me!Text59.Value
change this to..


strSearch = "song.title Like '*" & me!Text59 & "*'"

no quotes and it will return all values with that text in it.

[bellczar]

Thanks for your help.