Solved

Access database engine cannot find recordset

Posted on 2014-09-22
3
291 Views
Last Modified: 2014-09-22
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
Comment
Question by:Fritz Paul
3 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 40336219
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
 
LVL 84
ID: 40336226
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
 

Author Closing Comment

by:Fritz Paul
ID: 40336233
Thanks for quick and accurate reply.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question