VBA dynamic array assigns only one dimension

Question

I have some strange behavior with a dynamic array. It seems like VBA denies assigning a value to a variable. Here is the code:

Private Sub Report_Load()
    Dim db As Database
    Dim reportArray() As Variant
    Dim structre As Recordset
    Dim columns, rows, i As Integer

    'Open recordset
    Set db = CurrentDb
    Set structure = db.OpenRecordset("SELECT * FROM [tblstructure] ORDER BY [Rank]")

    'Change array size dynamically
    rows = structure.RecordCount
    columns = 4
    ReDim reportArray(rows, columns)

    'Populate array
    i = 0
    Do Until structure.EOF
        reportArray(i, 0) = structure![Name]
        i = i + 1
        structure.MoveNext
    Loop
End Sub

When I open the report I get an error that subscript is out of range. When I debug my code I can see that value of i in the loop is 2, so my array must be smaller that that. When I hover over rows variable I see that its value is 1. So indeed I'm trying to access something that is out of range. But the strange part is that the value of structure.RecordCount is 23. A screenshot:

Even if I use the code like this:

ReDim reportArray(structure.RecordCount, columns)

I get an array of size (1, 4). Why isn't VBA assigning value 23 to variable "rows" or why ReDim assigns the right value to second dimension, but not the first?

You should study the GetRows() method of the DAO.Recordset. — Gustav
– Gustav, Commented Sep 4, 2016 at 17:38

user5886152 · Accepted Answer · 2016-09-04 17:20:38Z

2

It's been a while since I've used Access, but I seem to recall that RecordCount doesn't work properly unless you've actually enumerated the entire resultset doing something like structure.MoveLast first (just remember to do structure.MoveFirst before looping through). Of course, that requires a recordset capable of moving in both directions.

Alternatively, you could put the ReDim in your loop, and just increase it by one each time.

answered Sep 4, 2016 at 17:20

user5886152

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

Comments

teddy2 · Accepted Answer · 2016-09-04 17:25:25Z

1

Read this: https://support.microsoft.com/en-us/kb/105976

CAUSE

For recordsets and snapshots, Microsoft Access does not automatically return the number of records that exist in the recordset. Rather, it returns the number of records accessed.

RESOLUTION

To determine the exact number of records in a recordset or snapshot, use the MoveLast method before checking the RecordCount property.

STATUS

This behavior is by design.

As per microsoft , RecordCount returns the number of records accessed and not the number of total records.

So, in your case, this should work

 structure.MoveLast
 rows = structure.RecordCount
structure.MoveFirst

answered Sep 4, 2016 at 17:25

teddy2

3904 silver badges16 bronze badges

Collectives™ on Stack Overflow

VBA dynamic array assigns only one dimension

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related