MS Access VBA .recordcount returning 0 when records exist, and debug.print returns value

Question

I have an Access table with 10 records and one field of short text. I am using the .recordcount function to return the number of records in this table. Code below:

Dim db as DAO.Database
Dim RS as DAO.Recordset
Dim recCount as Integer
Set db = CurrentDb
Set RS = db.OpenRecordset("Table Name")

RS.MoveFirst
RS.MoveLast
recCount = RS.recordcount

Debug.Print(recCount)

Dim i as Integer
i = 0
RS.MoveFirst
'Option one. Commented out when option two is active and vice verse
Do While i < 10
    Debug.Print(RS(i))
    i = i + 1
Loop

Do While i < 10
    Debug.print(RS![Only Field Name])
    i = i + 1
    RS.MoveNext
Loop

recCount always prints out to be 0. Attempting to print out the records in the recordset will return the first value only of the recordset and nothing else. After reading the first record, the program throws the error "Item not found in collection." I'm unsure of what could be causing this error, as I use the exact same method with another table in another VBA module, which works just fine.

I look at solutions to this elsewhere and the only one I could find was to add a RS.moveFirst and RS.moveLast after opening, however this does not work. I think this is becuase the opened recordset does not actually contain all the records in the table.

Thanks in advance.

This is an endless loop since i is never incremented. You need to move to the last record in order to get the recordcount on a DAO recordset. — Kostas K.
– Kostas K., Commented Jun 6, 2018 at 12:52
@KostasK. Sorry, I have i incremented in my real code. Just threw this together as a quick example to remove unrelated code and shorten variable names and forgot to add that part. Thank you for pointing it out. — jpc
– jpc, Commented Jun 6, 2018 at 12:56

ashleedawg · Accepted Answer · 2018-06-06 13:12:25Z

1

EDIT:

Try this:

Sub Demo_IterateRecords()
    Const tblName = "YOUR TABLE NAME HERE"
    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset(tblName)
    With rs
        .MoveLast
        .MoveFirst

        If MsgBox("Do you want to list all " & .RecordCount & " records?", _
            vbOKCancel, "Confirmation") <> vbOK Then GoTo ExitMySub

        Do While Not .EOF
            Debug.Print .Fields(0), .Fields(1), .Fields(2)
            rs.MoveNext
        Loop

ExitMySub:
        .Close
    End With
    Set rs = Nothing

End Sub

I used .Fields(_) because I'm not sure what your fields are called, but a better way to refer to them would be by name, like:

        Debug.Print !myID, !myEmployeeName, !myEmployeeAddress

Original Answer:

Try this:

RS.MoveLast
RS.MoveFirst
recCount = RS.RecordCount
Debug.Print(recCount)

Access doesn't know how many records there are until you move through them at least once.

If you would have checked the value of RS.RecordCount after your loop, you would have got a number.

Remarks

Use the Recordcount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset–, snapshot–, or forward–only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

Important Note

Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.

^(Source)

See also: MSDN : Recordset.RecordCount Property

edited Jun 6, 2018 at 13:12

answered Jun 6, 2018 at 12:44

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jpc Over a year ago

Please refer back to my question. I attempted this already and it did not work.

jpc Over a year ago

I have tired to put the movefirst/movelast functions after opening the recordset but before calling the .recordcount function, as you have suggested. It still does not work. Even if I move my debug.print statement around, I have a lopp that relies on the result of the recordcount that does not happend becuase the result is still 0 regardless.

jpc Over a year ago

Additionally, the loop with the debug.print statement inside is just to try to print out all the values in the recordset. It will still print just the first then throw an error regardless of if I have movefirst/movelast calls as you recommended

ashleedawg Over a year ago

@jpc Can you share the code where you tried that? I suspect I know the issue. Also, what about after you retrieve the records?

jpc Over a year ago

I edited the original code to be what I tired with the movefirst/movelast calls. Again though, this unfortunately did not work. I even tried moving those statements to other locations to see if anything worked but to no avail.

|

jpc · Accepted Answer · 2018-06-06 13:37:20Z

1

I managed to fix this issue but I have no idea why this worked. Instead of creating a new table and typing in the values for the ten records, I instead used an insert query to put the values I wanted from a query into a table. Using this new table, it worked.

answered Jun 6, 2018 at 13:37

jpc

1394 silver badges17 bronze badges

Comments

Gustav · Accepted Answer · 2018-06-06 13:42:49Z

1

You could list the records while counting:

Set RS = db.OpenRecordset("Table Name")

While Not RS.EOF
    Debug.Print RS![Only Field Name].Value
    i = i + 1
    RS.MoveNext
Loop

Debug.Print i & " records found."

answered Jun 6, 2018 at 13:42

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Comments

Rof · Accepted Answer · 2018-06-07 13:46:33Z

1

Perhaps you could try something similar to this in your sub routine:

Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim recCount As Integer
Set db = CurrentDb
Set RS = db.OpenRecordset("Table Name")
If Not (RS.EOF And RS.BOF) Then
RS.MoveFirst
Do Until RS.EOF = True
RS.MoveNext
Loop
MsgBox ("There are:" & " " & RS.RecordCount & " " & "records in the database")
End If
RS.Close
Set RS = Nothing

edited Jun 7, 2018 at 13:46

answered Jun 7, 2018 at 12:46

Rof

477 bronze badges

Collectives™ on Stack Overflow

MS Access VBA .recordcount returning 0 when records exist, and debug.print returns value

4 Answers 4

EDIT:

Original Answer:

Remarks

Important Note

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

EDIT:

Original Answer:

Remarks

Important Note

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related