Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Access database engine cannot find recordset

I define a DAO recordset and shows up in "locals" window and when I hover over its fields lower in the code it has values as exoected, but if I want to count the records, I get an error message as below. What is wrong?

I want to count the total number of records in the recordset. (Stops are there for debugging.)

Set rstIncomes = db.OpenRecordset(rstIncomesSQL, dbOpenSnapshot)
Stop

If DCount("*", "rstIncomes") = 0 Then
   MsgBox "rstIncomes has No records to show"
   Stop
   GoTo Proceed
Else
   MsgBox "rstIncomes has " & DCount("*", "rstIncomes") & "records."
   Stop
End If

Open in new window

error messageerror message
0
Fritz Paul
Asked:
Fritz Paul
1 Solution
 
MacroShadowCommented:
DCount can only be used on a table or query, not on a recordset. For a recordset you should use the RecordCount property, as illustrated in the following code snippet:
Dim rstIncomes As DAO.Recordset

Set rstIncomes = db.OpenRecordset(rstIncomesSQL, dbOpenSnapshot)

With rstIncomes
    .MoveLast
    .MoveFirst
    MsgBox "The rstIncomes has " & .RecordCount & " records!"
End With

rstIncomes.Close
Set rstIncomes = Nothing

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Or if you simply want a Count of the items:

Dim rst AS DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT COUNT(*) AS RecCount FROM YourTable WHERE blah blah blah")

Msgbox "Record count: " & rst("RecCount")
0
 
Fritz PaulAuthor Commented:
Thanks for quick and accurate reply.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now