Populate array with a DAO recordset data in VBA MS ACCESS

Question

What am I trying to make

I want a loop which removes the storagelocation of a product if it does not fit in his storagelocation. In this loop I want to save the artikelDetail_ID's in an array. After the loop ends I want to be able to see all the artikelDetail_ID's that got deleted and put into the array during the loop.

When the code runs it'll delete 6 records.

My solution

Dim verwijderdeID() As Variant
Dim lengte As Integer
lengte = 0
With recordset_opslag_detail_info
    Do Until recordset_opslag_detail_info.EOF
        If (huidige_doos_max_aantal - huidige_doos_inhoud >= 0) Then
            Exit Do
        End If
        ReDim verwijderdeID(lengte)
        verwijderdeID(lengte) = recordset_opslag_detail_info!ArtikelDetail_ID
        lengte = lengte + 1
        huidige_doos_inhoud = huidige_doos_inhoud - recordset_opslag_detail_info!Voorraadperartikel
        .Edit
        !tmea_opslag_locatie = Null
        .Update
        recordset_opslag_detail_info.MoveNext
    Loop
    End With

Dim i As Integer
For i = 0 To UBound(verwijderdeID)
    MsgBox verwijderdeID(i)
Next

Problem

When my code finds more then one record it'll only safe the last record in the array. If I as per example try to populate the array with 6 values from the recordset only verwijderID(5) will have a value.

Is what I'm trying to do impossible in VBA?

Redim deletes values in an array, Redim Preserve keeps the values. I think that should solve your problem. Some extra info: docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/… — Beek
Note that Redim Preserve with a continually increasing array is an antipattern. It will be a lot slower than necessary, since it will reallocate and copy the array for each iteration. Use a Collection instead, which doesn't have this problem. — Erik A
If my datasets are always less then 10 records. Will there be big time advantage if I use Collectioninstead of ReDim? — daveyvdweide

Taazar Taazar · Accepted Answer · 2020-01-29T14:36:59

Use Redim Preserve when the array you are changing the size of contains data you want to keep.

Redim on it's own will also change the size but it will also wipe all data in the array.

You can read more about it here.

Following this you want to change your line:

ReDim verwijderdeID(lengte)

to

ReDim Preserve verwijderdeID(lengte)

Populate array with a DAO recordset data in VBA MS ACCESS

1 Answers