Run-time error '3664' Datatype mismatch in criteria expression

Posted on 2009-02-18
Last Modified: 2013-11-27
I'm trying to resolve a run-time error '3664' Datatype mismatch in criteria expression error.
I'm wondering if it has anything to do with my tables or if the code is not relevent to Access 2007.

The error is showing to be in the following line:  

rstFrm.FindFirst "[MainID] = '" & rst![Value] & "'" (line 30)

I want my form to open to the recordset that I was viewing before I closed it, the previous session.
Option Compare Database

  Private Sub Form_Load()

           ' Declare database object and recordset objects.

           Dim db As Database, rst As Recordset, rstFrm As Recordset

           ' Set the database object to the current database.

           Set db = CurrentDb

           ' Open the table tblStorage.

           Set rst = db.OpenRecordset("tblStorage")

           ' Set the index for the seek.

           rst.Index = "PrimaryKey"

           ' Look for the MainIDLast record.

           rst.Seek "=", "MainIDLast"

           ' If a match, get the customer ID and take the form to that

           ' record. If no match, do nothing.

           If Not rst.NoMatch Then

               If Not IsNull(rst![Value]) Then

               ' Create clone of the form's record set.

               Set rstFrm = Me.RecordsetClone

               ' Find the matching record.

               rstFrm.FindFirst "[MainID] = '" & rst![Value] & "'"

               If Not rstFrm.NoMatch Then

                   ' Have the form go to that matching record.

                   Me.Bookmark = rstFrm.Bookmark

               End If

                   rstFrm.Close ' Close the recordset rstFrm.

               End If

           End If

           rst.Close ' Close the recordset rst.


End Sub

Private Sub Form_Unload(Cancel As Integer)

 ' The code below finds or creates a record in tblStorage where the

      ' field Variable contains "MainIDLast", and stores the current

      ' MainID in the field called Value.


          ' Declare database object and recordset object.

          Dim db As Database, rst As Recordset

          ' If the current record has no customer ID, do nothing.

          If IsNull(Me![MainID]) Then Exit Sub

          Set db = CurrentDb

          Set rst = db.OpenRecordset("tblStorage")

          rst.Index = "PrimaryKey"

          rst.Seek "=", "MainIDLast"

          ' If not found, create the entry.

          If rst.NoMatch Then


             rst![Variable] = "MainIDLast"

             rst![Value] = Me![MainID]

             rst![Description] = "ID of last edited customer record," _

                                 & Me.Name & "."

             rst.Update ' Update the recordset.

          Else          ' Else save the customer ID of the current record.


             rst![Value] = Me![MainID]

             rst.Update  ' Update the recordset.

          End If

          rst.Close      ' Close the recordset.


End Sub

Open in new window

Question by:9XqUwH3S
    LVL 119

    Accepted Solution

    MainID is autoNumber  Number data type

     rstFrm.FindFirst "[MainID] = " & rst![Value]

    Author Closing Comment

    Beutiful. Thank you.

    Thanks again.

    I accidently pressed 'no' on the "Was this helpful?" question. Let me know if you need me to change that, somehow.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now