RecordSet Clone RecordCount Equals 0?

Question

I'm a bit confused as to why this isn't finding the recordcount. My table "tblDatabase" has 3 records. I want it to the clone the recordset so that we do not actually alter any of the records. We just want to read them as we go through the data and then further go through items that are checked off (in this case, the field "SELECT" is set to True).

Can anyone tell me what I'm doing wrong? RecordCount keeps coming back as 0. Tried using RS1.MoveLast, MoveNext, etc... but it comes back the same. Any help in what I am doing wrong is greatly appreciated!

Set RS1 = CurrentDb.OpenRecordset("tblDatabase").Clone

    RS1.MoveFirst
    Debug.Print RS1.RecordCount

    For i = 1 To RS1.RecordCount
        If RS1.Fields("Select") = True Then

            strRequestNo = strRequestNo & IIf(Len(strRequestNo) = 0, "", ",") & Str(RS1.Fields("Request No"))
            strName = RS1.Fields("Name")

            'Pops up the Approval Dialog for user to Name + Date Approved.
            'The Code should not continue until the form is closed.
            DoCmd.OpenForm "frmClientAuthorization", acNormal, , , acFormEdit, acDialog, strRequestNo & "|" & strClientName


        End If
        RS1.MoveNext
    Next i

Is it the third line (Debug.Print RS1.RecordCount) that returns zero? — Eric Scherrer, Feb 03 '14 at 21:50
Yes, the 3rd line returns 0, however, I have changed the 2nd line to RS1.MoveLast, it still returns 0 for the Debug.Print. — user2296381, Feb 03 '14 at 21:52
Try changing `CurrentDb.OpenRecordset("tblDatabase").Clone` to `CurrentDb.OpenRecordset("tblDatabase", dbOpenSnapshot)`. — Chel, Feb 03 '14 at 22:13

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

Please note that clone does not make a recordset read only. You don't really need that.

If you want a readonly record set I suggest using the RecordsetOptionEnum value of dbReadOnly when opening the recordset:

How to open a record set

RecordsetOptionEnum

With regards to it returning zero record count you need to specify to open a table-type recordset:

'Open a table-type Recordset
 Set rsTable = dbs.OpenRecordset("tblDatabase", dbOpenTable)

Also, here are (in my opinion) two more graceful ways to loop through a recordset:

Example 1

Example 2

HTH,

Eric

edited May 23 '17 at 10:25

Community

1
1

answered Feb 03 '14 at 21:35

Eric Scherrer

3,328
1
19
34

Sorry - I changed Line #1 to: Set RS1 = CurrentDb.OpenRecordset("tblDatabase") However, it still remains the same. I'm assuming that's what you meant with "get rid of the Clone" ? Thank you for the suggestions however. – user2296381 Feb 03 '14 at 22:03
Yes I did, and I was spectacularly wrong. This is going back over ten years so I am a little rusty. Are you sure there is a table named tblDatabase in your DB and it has records in it? Perhaps CurrentDb is not pointing to where you think it is. – Eric Scherrer Feb 03 '14 at 22:10
Maybe you need to specify that you are opening a table: Set rsTable = dbs.OpenRecordset("Table1", dbOpenTable) – Eric Scherrer Feb 03 '14 at 22:18

score 0 · Answer 2 · answered Feb 04 '14 at 02:35

If you use a "for i = ..." then you need to do a rs1.movelast myvar = rs1.recordcount rs1.movefirst for access to know how many records match the recordset.

Use the myvar variable in the for-next loop

The cleaner way to perform the task is with the following:

RS1.MoveLast
Debug.Print RS1.RecordCount

Do Until rs1.EOF = True
    If RS1.Fields("Select") = True Then

        strRequestNo = strRequestNo & IIf(Len(strRequestNo) = 0, "", ",") & Str(RS1.Fields("Request No"))
        strName = RS1.Fields("Name")

        'Pops up the Approval Dialog for user to Name + Date Approved.
        'The Code should not continue until the form is closed.
        DoCmd.OpenForm "frmClientAuthorization", acNormal, , , acFormEdit, acDialog, strRequestNo & "|" & strClientName


    End If
    RS1.MoveNext
Loop

RecordSet Clone RecordCount Equals 0?

2 Answers2