CODEWITHSUNDEEP
ms-accessvba
user7867367
user7867367

Reputation:

Access VBA: Type mismatch on CurrentDb.OpenRecordset

In the following code I get the compile error Type mismatch when I am giving a value to vcount.

The code I use is:

Dim SQL As String
Dim vcount As Integer

SQL = " SELECT count(*) FROM [Data Processing List];"

vcount = CurrentDb.OpenRecordset(SQL)

enter image description here

Upvotes: 1

Views: 1085

Answers (2)

Rene
Rene

Reputation: 1093

Some alternative approaches.

Use TableDefs to count the number of rows within a specific table:

Dim vcount As Integer

vcount = CurrentDb.TableDefs("[Data Processing List]").RecordCount

Use DCount:

Dim vcount As Integer

vcount = DCount("*", "[Data Processing List]")

There's more methods. Have a look at this quite interesting article that discusses several methods including performance results for small versus medium sized tables:

MS Access – VBA – Get Record Count

Upvotes: 1

Erik A
Erik A

Reputation: 32642

CurrentDb.OpenRecordset returns a recordset, not a value.

If you want to access the value of a field of the recordset, you can do so by using the .Value property of the desired field in the fields collection:

vcount = CurrentDb.OpenRecordset(SQL).Fields(0).Value

Upvotes: 1

Related Questions