Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looping through records (Access 2002)

    I want to loop through a set of records, editing where appropriate but keep hitting an error 13 - type mismatch in my code in the line shown below
    The sample I am using has single table of names

    The code I am using is driven by a button on a simple form as follows

    Private Sub test_Click()
    Dim db As Database
    Dim rst As Recordset

    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset("tblNames", dbopentable) - THIS IS THE PROBLEM LINE
    Do Until rst.EOF
    MsgBox [tblNames].[fldname]
    rst.MoveNext
    Loop
    End Sub

    I have set references in Tools/References to the following
    Visual basic for applications
    MS Access 10.0 object library
    OLE automation
    MS ActiveX data object 2.1 library
    MS DAO 3.6 object library
    MS jet and replication object 2.6 library

    I'd be grateful for any suggestions as to what I'm doing wrong!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through records (Access 2002)

    Because you have references to both DAO and ADO (ActiveX Data Objects), the declaration Dim rst As Recordset is ambiguous: Recordset is both a DAO and an ADO object. Since ADO is above DAO in the list of references, VBA interprets it as an ADO recordset, but you can't use that with the (uniquely DAO) Database object. The solution is to be explicit in your declarations:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Note: Microsoft recommends using CurrentDb instead of DBEngine(0)(0):

    Set db = CurrentDb

    PS: Do you really need the Jet and Replication Objects library? It is rather specialized.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping through records (Access 2002)

    That was the answer - the more I learn the more I realise I need to learn!
    I put in the jet reference because it was shown in a screen dump in an Access book talking about references - I've removed it again
    Thankyou - I will now stop banging my head against the wall

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •