Runtime Error 13 type mismatch

[LisaB]

Can anyone tell me why this code is getting the runtime error. I am using
the same code in another database that uses the exact same tables
--------------------------------------------

Public Function IdentifyCurrentUser()
'Figure Out Who the Current user is and set the Global Staff ID

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset("tblStaff", dbOpenDynaset, dbSeeChanges)
******this line is highlighted during the debug

If SourceRS.RecordCount Then
SourceRS.MoveFirst

Do Until SourceRS.EOF

If SourceRS!UserName = CurrentUser Then
GLBL_StaffID = SourceRS!StaffID
End If
SourceRS.MoveNext
Loop
End If
SourceRS.Close
End Function

 

[Chris Nebinger]

It's related to your other problem.

By default, Microsoft Access 2000+ uses ADO for data
connections. Access 97 used DAO. The code you have below
is for DAO.


You can certainly use both at one time, but both ADO and
DAO have a Recordset Object, as well as Field object. If
you are going to use one of those objects, you have to
explicitly declare which one you want to use.

Dim SourceRs as DAO.Recordset

The rest of the code will be okay IF you added a reference
to use DAO.

Now, any other place you have a recordset in code, you
have to declare which library you want to use.

Dim SourceRS As ADODB.Recordset ' Uses ADO
Dim SourceRS As DAO.Recordset ' Uses DAO


Chris Nebinger

 

[Dirk Goldgar]

Can anyone tell me why this code is getting the runtime error. I am
using the same code in another database that uses the exact same
tables --------------------------------------------

Public Function IdentifyCurrentUser()
'Figure Out Who the Current user is and set the Global Staff ID

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset("tblStaff", dbOpenDynaset,
dbSeeChanges) ******this line is highlighted during the debug

If SourceRS.RecordCount Then
SourceRS.MoveFirst

Do Until SourceRS.EOF

If SourceRS!UserName = CurrentUser Then
GLBL_StaffID = SourceRS!StaffID
End If
SourceRS.MoveNext
Loop
End If
SourceRS.Close
End Function

What is the declaration of SourceRS? Most likely you have written

Dim SourceRS As Recordset

and not

Dim SourceRS As DAO.Recordset

and you have a reference to the ADO Object Library and either don't have
reference to the DAO Object Library, or the ADO reference is higher in
the references list.

 

[LisaB]

Thank you both. I was already referencing the DAO 3.6 Object Library
however my dim statements looked like the following:

Global TheDB As Database
Global SourceRS As Recordset
Global QDef As QueryDef

I added the DAO. as you both suggested and it now works.

However, I am confused why this code works without the DAO. in a database
that was converted to Access 2000 from Access 97 but does not work in a
newly created Access 2000 database.

 

[LisaB]

Thank you both. I was already referencing the DAO 3.6 Object Library
however my dim statements looked like the following:

Global TheDB As Database
Global SourceRS As Recordset
Global QDef As QueryDef


I added the DAO. as you both suggested and it now works.

However, I am confused why this code works without the DAO. in a database
that was converted to Access 2000 from Access 97 but does not work in a
newly created Access 2000 database.

 

[Douglas J. Steele]

It will work without the disambiguation if there's only a reference to DAO,
or if the reference to DAO is higher in the list of references than the ADO
reference.

 

[Dirk Goldgar]

Thank you both. I was already referencing the DAO 3.6 Object Library
however my dim statements looked like the following:

Global TheDB As Database
Global SourceRS As Recordset
Global QDef As QueryDef

I added the DAO. as you both suggested and it now works.

However, I am confused why this code works without the DAO. in a
database that was converted to Access 2000 from Access 97 but does
not work in a newly created Access 2000 database.

In the database that was converted, there was only a reference to DAO;
none to ADO (ActiveX Data Objects). When you created a new database in
Access 2000, though, a reference to the ADO library was added by
default, and you later added a reference to DAO. But the ADO reference
remained and was higher in the list than the DAO reference, giving it
higher priority. Your declaration of SourceRS "As Recordset" was
ambiguous, because there's a Recordset object defined in both libraries,
so Access chose the version in the library with the higher priority --
ADO.

You can fix this problem by any of the following:

(a) If you're not planning to use ADO in this database, ever, just
remove its reference.

(b) Move the DAO reference higher in the References list than ADO. Then
all ambiguous declarations will use the DAO definition by default, and
you'll have to be specific in your declaration if and when you want to
use the ADO definition.

(c) Disambiguate your declarations for these object by qualifying the
object type with the library identifier; e.g.,

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

Because these problems have become so common, I think it's best to take
option (c), at least for all new code.

 

[Chris Nebinger]

In the converted database, only DAO is referenced. In a
newly created database, only ADO is referenced.

With only one reference, Access searches the available
libraries for a Recordset object. It only finds one, so
everything is fine.

When you have 2 referenced, Access stops searching the
moment it finds a Recordset object, presumably the ADO
recordset object.

It is a good habit to explicitly declare which library has
the object you want to use. Doing this will help Access
find the objects that you want to use faster. It will
also prevent future problems from cropping up like you
currently have. Finally, it helps document your database
better.


Chris Nebinger

 

[Guest]

Can anyone assist me with this? I get error 13 when this line tries to execute!

With PartMaster
Do Until .Cells(2, 1).Offset(Inc, 0) = ""
If .Cells(2, 1).Offset(Inc,
Application.Match(Val(ReleaseSelect(Inc1)), .Range(.Cells(1, 1), .Cells(1,
100)), "0") - 1) <> "X" Then
PartsNotReleased(Inc1) = PartsNotReleased(Inc1) + .Cells(2,
1).Offset(Inc, 0) + Chr(0)
End If
Inc = Inc + 1
Loop
End With

 

[Dirk Goldgar]

Can anyone assist me with this? I get error 13 when this line tries
to execute!

With PartMaster
Do Until .Cells(2, 1).Offset(Inc, 0) = ""
If .Cells(2, 1).Offset(Inc,
Application.Match(Val(ReleaseSelect(Inc1)), .Range(.Cells(1, 1),
.Cells(1, 100)), "0") - 1) <> "X" Then
PartsNotReleased(Inc1) = PartsNotReleased(Inc1) +
.Cells(2, 1).Offset(Inc, 0) + Chr(0)
End If
Inc = Inc + 1
Loop
End With

If you're executing this code in Access, the Application object has no
Match method. I don't program Excel much, but my guess is that's an
Excel method and you need to use an Excel application object to qualify
the call.