Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA/DAO problems (2000 SR-1)

    I'm jumping into VBA/DAO code for the first time and I'm having a couple of problems:

    -- I'm trying to add a RunCode statement to a Macro, but the Expression Builder won't paste in the procedure that I want. I checked if it would paste in other procedures, and it does.

    -- I've tried manually testing the procedure code (using F8) but I run into an error when I try to open a record set. It seems like it won't recognize the Database objects. I hover my cursor over the Database object variables and nothing appears. The error is run-time error 13 (type mismatch). I know it sees the database because the Forms code lines are producing the output I want. Below is the code leading up to the error:

    ****************************

    Dim db1 As Database
    Dim db2 As Database

    Dim rs1 As Recordset
    Dim rs2 As Recordset

    Dim bDate As Date
    Dim eDate As Date
    Dim rNum As String

    Set db1 = CurrentDb
    Set db2 = CurrentDb

    'Get beginning date, ending date, release number from Archive form

    bDate = Forms![frmArchive]!lstStartDate.Value
    eDate = Forms![frmArchive]!lstEndDate.Value
    rNum = Forms![frmArchive]!lstRelease.Value

    Set rs1 = db1.OpenRecordset("CorrectionCRs")

    **********************

    Thanks,
    Bob

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

    Re: VBA/DAO problems (2000 SR-1)

    Make sure that you have a reference to the Microsoft DAO 3.6 Object Library in Tool | References... (in the Visual Basic Editor.)

    I recommend prefixing DAO in the declarations to avoid ambiguity - for Database it is not really essential, but Recordset is both an object type in both DAO and ADO, so it is better to avoid confusion.

    Dim db1 As DAO.Database

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset

    I don't see a good reason to declare two database objects if you're going to set both of them to CurrentDb.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA/DAO problems (2000 SR-1)

    The DAO naming convention made a difference. Thanks.

    On the other issue, do you know why Expression Builder isn't letting me paste the procedure in a macro? I've tried manually entering the name of the procedure in the RunCode line in the macro, but all that does is produce this error: "The object doesn't contain the Automation object 'name of procedure'.

    Bob

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA/DAO problems (2000 SR-1)

    Is the procedure a function? If it's not, you can't call it in a macro.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA/DAO problems (2000 SR-1)

    That was the problem. When I changed it to a function, it worked OK. Thanks.

Posting Permissions

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