Calculator.. type mismatch error when closing?

[Brook]

good day all,

I found a calculator for an access db and copied it to my existing db, it
opens fine and everything functions fine except when I try to close the form
I get the following error:

Run-time error "13":

Type Mismatch

when I click the debug button, it goes to the code for the form and
specifically to the *** area...



Private Function ChangeProperty(strPropName As String, varPropType As
Variant, varPropValue As Variant) As Boolean
'Changes the database property passed to the datatype and value passed.
'If the property does not exist an error is generated and the property is
created.

Dim dbMdb As Database, prp As Property
Const conPropNotFoundError = 3270
Set dbMdb = CurrentDb
On Error GoTo Change_Err
dbMdb.Properties(strPropName) = varPropValue
ChangeProperty = True
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then 'prop not found
***** Set prp = dbMdb.CreateProperty(strPropName, varPropType,
varPropValue)
dbMdb.Properties.Append prp
Resume Next
Else
'unknown error
ChangeProperty = False
Resume Change_Bye
End If
End Function

Does anyone have any ideas suggestions on what might be causing this?

Thanks,

BRook

 

[Douglas J. Steele]

Let me guess. You're using Access 2000 or 2002. You've added a reference to
DAO (or else Dim dbMdb As Database would have caused an error), but you
didn't remove the reference to ADO when you did that.

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO property, you'll need to
use Dim prp as DAO.Property (to guarantee an ADO recordset, you'd use Dim
prp As ADODB.Property)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Brook]

Douglas,

you are correct... How do I remove the reference to ADO? Will removing the
ADO reference interfere with my remaining DB?

I really just exported the calculator from the downloaded DB to my DB.

Thanks,

Brook

 

[Douglas J. Steele]

Whether removing the ADO reference will cause problems depends on whether or
not you're using ADO anywhere else in your database. Open the References
dialog and remove the reference to the Microsoft ActiveX Data Objects 2.x
Library then do a compile on your database (under the Debug menu). If it
compiles cleanly, then odds are you're not using ADO, and you can safely
keep the reference out. Otherwise, leave it in and ensure you disambiguate
all declarations of the objects I listed below.

There's nothing wrong with having both ADO and DAO in the same database. I
do it all the time when I've got applications that talk to both Jet (.MDB)
and SQL Server. (DAO is designed specifically for Jet databases, so is
almost always more efficient.)

 

[Brook]

to be on the safe side, how would I configure my DB to accept both references
like you have done?

Brook

 

[Douglas J. Steele]

As I said, if you have both references set, you need to disambiguate all
declarations for Connection, Error, Errors, Field and Fields objects.

For example, to ensure that you get a DAO property, you'll need to use Dim
prp as DAO.Property (to guarantee an ADO recordset, you'd use Dim prp As
ADODB.Property)

 

[Brook]

Thank you very much,

I was able to get everything working properly...

Brook

 

[Douglas J. Steele]

You're welcome.

Just thought I'd point out that I did an incomplete copy-and-paste job when
listing the objects you need to disambiguate. The complete list of objects
which exist in both the DAO and ADO models is Connection, Error, Errors,
Field, Fields, Parameter, Parameters, Property, Properties and Recordset.
(it was correct when I first listed it in this thread!)

 

[Brook]

Douglas,

I couldn't figure out how to "disambiguate" so I just created a new "close"
form button as a work around.

Here is the code for the original close button that was giving me the error:

Private Sub cmdClose_Click()
Dim boolRet As Boolean
boolRet = ChangeProperty("calcDisplay", dbDouble, Val(txtDisplay.Caption))
boolRet = ChangeProperty("calcMem", dbDouble, Val(lblMem.Caption))
boolRet = ChangeProperty("calcTempStore", dbDouble,
Val(lblTempStore.Caption))
DoCmd.Close acForm, Me.Name
End Sub

If you have any suggestions, I'm open.

Also, do you know of any good articles on how to "disambiguate"?

Thanks,

Brook

 

[Douglas J. Steele]

Disambiguation simply means to explicitly include the name of the class when
you declare the object or when you refer to a method or property of an
object.

Use Dim rst As DAO.Recordset or Dim rst As ADODB.Recordset as opposed to Dim
rst As Recordset.

The "refer to a method or property of an object" means that rather than
using Left(MyString, 4), you could use VBA.Left(MyString, 4), since the Left
function comes from the VBA library.

That's all. So for this particular problem, you need to change the one line
of code in ChangeProperty from

Dim dbMdb As Database, prp As Property

to

Dim dbMdb As DAO.Database, prp As DAO.Property

(Okay, it's not 100% necessary to use DAO.Database, since there isn't a
Database object in any other of the "common" references. However, there's
nothing wrong with it either.)

 

[Brook]

Douglas...

Thanks for explaining this further... Now I understand what you meant by
your first post...

Everything works great...

Brook