Search in a Form with Wildcards

[excellenthelp]

Hello Accessers,

I am wondering what I am not writing properly in this code. It is for a user to type in a textbox where it will search for whatever is being typed in there- like a search box.

It will continue to say an error: "Run-Time error '13': Type Mismatch".

Private Sub btnFind_Click()

    If (TxtFind & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ACA Name2] = " * " & TxtFind & " * ""
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & TxtFind & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    TxtFind = Null
End Sub

The line that is red highlighted is where the debugger leads me to after attempting to execute the code.

Help....?

Thanks!

[ranman256]

Its a string , you need quotes surrounding it...

rs.FindFirst "[ACA Name2] =' * " & TxtFind & " *' "

[rpeare]

Try

rs.FindFirst "[ACA Name2] = '*" & TxtFind & "*'"

[June7]

Wildcard usually used with LIKE operator. But I have not tried with the FindFirst method.

Use LIKE instead of = operator and apostrophe delimiters and no spaces around the * :

rs.FindFirst "[ACA Name2] LIKE '*" & TxtFind & "*'"

[excellenthelp]

Ranman256,

Thank you. It still could not find the record in the form. But this time, there is no error. =) I had double checked the spelling in the textbox (it is only three letters!)
Hmm...

[rpeare]

hah completely missed that june, was just going to comment that you have spaces before and after your text string so it's looking for that.

For instance if you had

ABCDEFG

ABC DEF G

and txtFind was DEF you would only find the second record, not the first that's why I stripped out the spaces

[excellenthelp]

It now works!! The code is:

Private Sub btnFind_Click()

    If (TxtFind & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    Dim strSearch As String
    
    strSearch = "[ACA Name2] LIKE '*" & TxtFind & "*'"
    rs.FindFirst strSearch
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & TxtFind & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    TxtFind = Null

    End Sub

Thanks for the help!!!

Marking this as SOLVED.