CODEWITHSUNDEEP
vbams-accessrecordset
daveyvdweide
daveyvdweide

Reputation: 117

Populate array with a DAO recordset data in VBA MS ACCESS

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?

Upvotes: 0

Views: 469

Answers (1)

Taazar
Taazar

Reputation: 1505

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)

Upvotes: 2

Related Questions