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: learn.microsoft.com/en-us/dotnet/visual-basic/… — Beek
– Beek, Commented Jan 29, 2020 at 14:18
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
– Erik A, Commented Jan 29, 2020 at 14:37
If my datasets are always less then 10 records. Will there be big time advantage if I use Collectioninstead of ReDim? — daveyvdweide
– daveyvdweide, Commented Jan 29, 2020 at 15:50

2 revs · Accepted Answer · 2020-01-29 14:44:38Z

2

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)

Collectives™ on Stack Overflow

Populate array with a DAO recordset data in VBA MS ACCESS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related