VBA/DAO problems (2000 SR-1)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote