none
Getting runtime error on a query RRS feed

  • Question

  • I keep getting runtime errors with this code. I cant seem to diagnose the issue. Its probably stupid simple , cus that would make too much sense.

    My query is Employees Extended. I've tailored this code around many examples from videos and other forums and Im still getting the runtime error '13'. Its very frustrating.

    Set rs = db.OpenRecordset("Employees Extended", dbOpenDynaset, dbSeeChanges)

    Please help?

    Also, can someone suggest a good syntax training website or series of youtube vids? Thank a million.

    Option Compare Database
    Option Explicit
    
    Dim db As Database
    Dim rs As Recordset
    
    Private Sub cmdSubmitforApproval_Click()
        Dim useremail As String
        Dim approveemail As String
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Employees Extended", dbOpenDynaset, dbSeeChanges)
        
        Do Until rs.EOF
            If rs("Privilege Name") = "Purchase Approvals" Then
            approveemail = rs("Privilege Name") & "'" & approveemail
            rs.MoveNext
            Else
            rs.MoveNext
            End If
        Loop
            MsgBox appremail
    

    Monday, May 14, 2018 2:55 PM

Answers

  • Hi,

    Just a guess but try changing this line:

    Dim rs As Recordset

    into this:

    Dim rs As DAO.Recordset

    Hope it helps...

    • Marked as answer by jshot Monday, May 14, 2018 3:05 PM
    Monday, May 14, 2018 2:59 PM

All replies

  • Hi,

    Just a guess but try changing this line:

    Dim rs As Recordset

    into this:

    Dim rs As DAO.Recordset

    Hope it helps...

    • Marked as answer by jshot Monday, May 14, 2018 3:05 PM
    Monday, May 14, 2018 2:59 PM
  • DBGuy,

    that exactly worked. Im no longer getting the runtime error. Can you please explain to me why that works here and not just simply when I call it.

    Monday, May 14, 2018 3:08 PM
  • Hi,

    Glad to hear it worked for you. My guess for the reason why it worked is you probably have a reference to ADODB in your project but the code you're using is for DAO. If the reference to ADODB is higher in priority to DAO, Access will create an ADODB object. So, when you execute:

    Dim rs As Recordset,

    You ended up with an ADODB object. But when you then execute:

    Set rs = db.OpenRecordset(...

    You're asking Access to assign a recordset into a DAO object; hence, you get a type mismatch error because your rs object is actually a ADODB object.

    Hope it makes sense...

    Monday, May 14, 2018 3:17 PM
  • DBGuy,

    that exactly worked. Im no longer getting the runtime error. Can you please explain to me why that works here and not just simply when I call it.


    You have both DAO and ADO in the References of your project. These two libraries use objects with the same name but different syntax and structure. That is why you need to fully qualify the object name, in order to select the correct library. Otherwise, by default the library of the highest priority in the References will be used. In this case I would suspect it's ADO and not DAO, so your DAO OpenRecordset method would be attempting to return results to an ADO Recordset instead of a DAO Recordset, resulting in a type mismatch.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 14, 2018 3:18 PM
  • Thanks DbGuy and Paul, that actually makes a lot of sense. As I'm still trying to understand all the syntax, it gets frustrating. Thanks for clarifying.
    Monday, May 14, 2018 3:30 PM
  • Thanks DbGuy and Paul, that actually makes a lot of sense. As I'm still trying to understand all the syntax, it gets frustrating. Thanks for clarifying.

    You're welcome. Paul and I were happy to assist. Good luck with your project.
    Monday, May 14, 2018 3:30 PM