Results 1 to 8 of 8
  1. #1
    AdamT is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    14

    Error Message on db.OpenRecordset (Compile error Type mismatch)

    I need help with the following Code which is written in VBA in MS Access 2010. My goal is to get the current revision from A2CPCIChassisPL7447550 table, then use it to access to another table (NFCSRevisionList) to get next revision (for example is current revision is "C", then from second table I need to retrieve the next revision which is "D").

    I am getting the following error on the following line code


    Set r1 = db.OpenRecordset("SELECT DISTINCTROW ID, Revision FROM NFCSRevisionList Where Revision = ") & TextCurrentRevision


    Dim TextCurrentRevision
    Dim r As DAO.Recordset, db As DAO.Database
    Dim r1 As DAO.Recordset, db1 As DAO.Database

    Set db = CurrentDb
    Set r = db.OpenRecordset("SELECT DISTINCTROW A2ID, Revision, A2DrawingNumber, CageCode FROM A2CPCIChassisPL7447550 ORDER BY Revision DESC")
    TextCurrentRevision = r!Revision

    'get next revision from the table
    Set db1 = CurrentDb
    Set r1 = db.OpenRecordset("SELECT DISTINCTROW ID, Revision FROM NFCSRevisionList Where Revision = ") & TextCurrentRevision
    r1.MoveNext
    MsgBox r1!Revision
    ' promt the user for the latest revision
    MsgBox "Current Revision :" & TextCurrentRevision & " Next assigned Revision should be: " & r1!Revision
    'Cleanup NFCSRevisionList
    r.Close
    Set r = Nothing
    Set r1 = noting
    db.Close
    Set db = Nothing
    Set db1 = Nothing

    V/r
    Adam

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    Set r1 = db.OpenRecordset("SELECT DISTINCTROW ID, Revision FROM NFCSRevisionList Where Revision = " & TextCurrentRevision )
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    AdamT is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    14
    Paul,
    Thank you. I used your suggestion and I got the following Error message:

    Runitime Error '3061':
    Too few parameters.Expected 1

    V/r
    Adam

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this technique to see if the SQL is being populated correctly.

    BaldyWeb-Immediate window

    Post the SQL here if you don't spot the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    AdamT is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    14
    Paul,
    I used the technique from "BaldyWeb", still the same Error message

    V/r
    Adam

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you miss this?

    Quote Originally Posted by pbaldy View Post
    Post the SQL here if you don't spot the problem.
    The technique doesn't fix anything on its own, it's a debugging tool.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    AdamT is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    14
    The debugging tool is not working under access 2010.

    V/r
    Adam

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Odd, because it works on my copy of 2010.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error: Run-time error '13' Type mismatch
    By uronmapu in forum Access
    Replies: 1
    Last Post: 09-07-2012, 05:38 AM
  2. Replies: 2
    Last Post: 06-23-2012, 11:59 PM
  3. Replies: 4
    Last Post: 06-08-2012, 09:08 AM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Error 13 Type Mismatch error
    By GlennBurg in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 03:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums