CODEWITHSUNDEEP
ms-accessvba
Phesago
Phesago

Reputation: 90

MS-Access RecordCount Returns a valid number, but .GetRows only pulls one record

Ok I am trying to dynamically get recordCount and pass that to .GetRows but it doesnt work as it only pulls in one records into the array. If I just statically put a number into the .GetRows method it works fine, but this is obviously not ideal.

This Works

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Client", dbOpenDynaset, dbSeeChanges)
aRR = rs.GetRows("random number")
For i = 0 To rs.RecordCount - 1
    For j = 0 To rs.Fields.Count - 1
        Debug.Print ; aRR(j, i)
    Next j
Next i

This does not

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Client", dbOpenDynaset, dbSeeChanges)
With rs
    rs.MoveLast
    Debug.Print ; rs.RecordCount
    Q = rs.RecordCount
    aRR = rs.GetRows(Q)
End With
For i = 0 To rs.RecordCount - 1
    For j = 0 To rs.Fields.Count - 1
        Debug.Print ; aRR(j, i)
    Next j
Next i

I have tried multiple things I have found on the web but clearly I must be missing something? Is there an easy approach to this or do i need to requery with a DISTINCT clase, and pass the return value within that record set to a new variable?

Upvotes: 1

Views: 536

Answers (2)

Steve W
Steve W

Reputation: 476

Like Phesago mentioned, Access will only return the recordcount based on which record in the recordset it's looking at. As a general practice when working with recordsets, I always begin with the following template:

Private Sub CreateRecordset()
    Dim rs As Recordset
    Dim sql As String

    sql = "SELCT * FROM tblSomeTable"

    Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

    With rs
        If Not .EOF And Not .BOF Then
            .MoveLast
            .MoveFirst

            Dim i As Integer

            For i = 0 To rs.RecordCount - 1
                'do whatever actions desired



            Next

        End If
    End With
End Sub

Upvotes: 0

jSebestyén
jSebestyén

Reputation: 1806

GetRows also uses the recordset's pointer. With rs.MoveLast you put that pointer to the last row. That's why only one row gets returned. Addrs.MoveFirst after setting Q to resolve this.

Upvotes: 3

Related Questions