Datatype Mismatch using FindFirst

I am trying to use the FindFirst method to load a record based on a field on my form where users will enter a value. I keep getting the DataType Mismatch error 3464 whenever I try to run this code. Can anyone help shed some light on what I have wrong in my code?

Dim rst As DAO.Recordset
Dim strCriteria As String

Set rst = CurrentDb.OpenRecordset("dbo_qtmast1", dbOpenDynaset) 'Me.RecordsetClone
strCriteria = "[fquoteno] ='" & Me.txtQuoteSelect & "'"

rst.FindFirst (strCriteria)
    If rst.NoMatch Then
        Me.txtQuoteSelect.SetFocus
        Dialog.Box Prompt:="No entry found.", _
            Buttons:=(vbOKOnly + vbInformation), _
            Title:="BizOps Message", _
            ButtonDelay:=0, _
            AllowCopyToClipboard:=False, _
            AllowSaveToFile:=False, _
            AllowBeep:=True
    Else
        Me.Bookmark = rst.Bookmark
    End If

Set rst = Nothing
DoCmd.GoToControl "txtQuoteSelect"

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
If the field is numeric then there should be no quotes.
strCriteria = "[fquoteno] =" & Me.txtQuoteSelect

Open in new window

0
 
NorieVBA ExpertCommented:
Where in the code do you get the error?
0
 
ste5anSenior DeveloperCommented:
What is Dialog? It's not a built-in function or feature..

And escape your input..

  Dim rst As DAO.Recordset
  Dim strCriteria As String

  Set rst = CurrentDb.OpenRecordset("dbo_qtmast1", dbOpenDynaset)
  strCriteria = "[fquoteno] ='" & Replace(txtQuoteSelect.Value, "'", "''") & "'"
  rst.FindFirst strCriteria
  If rst.NoMatch Then
    MsgBox "nope"
  Else
    Me.Bookmark = rst.Bookmark
  End If

  Set rst = Nothing
  txtQuoteSelect.SetFocus

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Dialog is a custom function to use my own message boxes instead of the built-in access ones. The Dialog.Box and below is the custom message box function.

I always get confused with the single quotes and double quotes when using text values in the FindFirst.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That change didn't work. I am still getting the error for DataType. I get the error on

rst.FindFirst (strCriteria)
0
 
NorieVBA ExpertCommented:
What's the data type of the field fquoteno?
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
It's a number field. There is also an inputmask associated with that field: 999999
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That worked. Thank you very much!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Assuming fquoteno is a Text Data Type (?) .... try this

strCriteria = "[fquoteno] = " &  Chr(34) & Me.txtQuoteSelect & Chr(34)

Chr(34) is a Double Quote.
0
 
PatHartmanCommented:
A more efficient way to allow the user to search is to make your query take arguments.  Then you can simply requery the form to bring up the requested data.

Select .. From yourtable Where somefield = Forms!yourform!yourfield;

The form will open empty when using this technique since the form field will be null.

The method you are using is typical old style Access but will be slower than the query method if you ever need to upsaxe the database.  Why bring down thousands or hundreds of thousands of records from the server when the user only needs to see one at at time?  The query method won't make a big difference on a small table or on a Jet/ACE BE but learning more efficient techniques helps in the long run.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.