RecordCount now shows an error (1 Viewer)

Gkirkup

Registered User.
Local time
Today, 15:23
Joined
Mar 6, 2007
Messages
616
I have a long established program that was running in Access 2010 with no problems. Now we have moved to Office 365 (which I think is Access 2019) and we have a new problem.
The code is 'NumRecs = rsLotBid.RecordCount'. And the word RecordCount comes up with an error, 'Compile error: Type mismatch'. Has RecordCount changed in Access 2019? Any suggestion of how to remove this error message? Presumably I can change NumRecs to be the same type as RecordCount, if I knew what type that is?

Robert
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
18,962
Hi Robert. Go above that line in your code and look for something like:
Code:
Dim rsLotBid As Recordset
If you find it, change it to this:
Code:
Dim rsLotBid As DAO.Recordset
Hope that helps...
 

Gkirkup

Registered User.
Local time
Today, 15:23
Joined
Mar 6, 2007
Messages
616
Hi Robert. Go above that line in your code and look for something like:
Code:
Dim rsLotBid As Recordset
If you find it, change it to this:
Code:
Dim rsLotBid As DAO.Recordset
Hope that helps...
DBGuy: Thank you, I am making some progress. I know nothing about DAO coding so must have copied this segment from somewhere long ago.
First the change to the declarations. They previously read like this:
DIM cn as new ADODB.Connection
DIM rsLotBid as new ADODB.RecordSet.
I changed these to DAO, and also removed the 'new' as that produced an error.

The code now runs until it gets to this line:
rsLotBid.Open strSQL, cn, adopenstatic, adlockoptimistic

The 'Open' produces a compile error, 'Method or data member not found'.

Robert
 

Ranman256

Well-known member
Local time
Today, 18:23
Joined
Apr 9, 2015
Messages
4,102
or use:
iCount =Dcount("*","qsMyQry")

no recordset needed.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
18,962
DBGuy: Thank you, I am making some progress. I know nothing about DAO coding so must have copied this segment from somewhere long ago.
First the change to the declarations. They previously read like this:
DIM cn as new ADODB.Connection
DIM rsLotBid as new ADODB.RecordSet.
I changed these to DAO, and also removed the 'new' as that produced an error.

The code now runs until it gets to this line:
rsLotBid.Open strSQL, cn, adopenstatic, adlockoptimistic

The 'Open' produces a compile error, 'Method or data member not found'.

Robert
Hi. Since I didn't see your entire procedure, I made a wrong assumption based on what I have seen in the past. However in your case, what I suggested doesn't really apply. So now, I suggest you put all the old code back the way it was and simply go to Tools > References and make sure you have a reference set for ADO.

PS. It should something similar to this: Microsoft ActiveX Data Objects 6.1 Library

Hope that helps...
 

Gkirkup

Registered User.
Local time
Today, 15:23
Joined
Mar 6, 2007
Messages
616
Hi. Since I didn't see your entire procedure, I made a wrong assumption based on what I have seen in the past. However in your case, what I suggested doesn't really apply. So now, I suggest you put all the old code back the way it was and simply go to Tools > References and make sure you have a reference set for ADO.

PS. It should something similar to this: Microsoft ActiveX Data Objects 6.1 Library

Hope that helps...
DbGuy:
Thank you. I will do that. I am new to Access 2019 / Office 365 and for some reason cannot find Tools / References. I looked on the File menu. Where should I be looking?

Robert
 

bastanu

AWF VIP
Local time
Today, 15:23
Joined
Apr 13, 2010
Messages
1,173
Hi Robert, have you looked at the link I gave you?
 

strive4peace

AWF VIP
Local time
Today, 17:23
Joined
Apr 3, 2020
Messages
856
hi @Gkirkup,

> "cannot find Tools / References"

this is done from the VBA window

since ADO and DAO both have Recordset, if you're using ADO, use this:

Dim rsLotBid As ADODB.Recordset
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:23
Joined
May 7, 2009
Messages
16,393
I changed these to DAO, and also removed the 'new' as that produced an error.
your code is using ADO, so don't change anything (if you don't know).
also pay attention to the other suggestions, it is more likely they are in the
correct path.
 

Users who are viewing this thread

Top Bottom