How to populate a dynamic array in MS Access?

Question

I have a dynamic array that I want to append values to. The number of values to be appended is not fixed

I was trying to do something like this:

Dim array() As Integer
ReDim Preserve array(UBound(array)+1)
bulkJob(UBound(array) + 1) = Me.ID

I get subscript out of range error at ReDim Preserve array(UBound(array)+1). Is there a way to do this?

I am surprised you can even run the code. I get syntax error on the Dim line. Array() is an intrinsic function. Use something else for your array name, like: aryInt. You ReDim the array but I don't see any code to populate the array. — June7
– June7, Commented Apr 17, 2019 at 2:02
UBound has no value so of course get out of bound error. Certainly can be done but not clear what you are trying to do. Where do the values come from? What is bulkjob? — June7
– June7, Commented Apr 17, 2019 at 2:18
You could also use a Collection if you want a dynamic array-like structure — BankBuilder
– BankBuilder, Commented Apr 17, 2019 at 2:23

Gustav · Accepted Answer · 2019-04-17 09:55:28Z

1

Not quite clear what you are trying to do, but this could get you some ideas:

Public Function BuildJobs(Id As Integer)

    Static bulkJob()    As Integer
    Dim Upper           As Integer

    On Error Resume Next
    Upper = UBound(bulkJob) + 1
    On Error GoTo 0

    ReDim Preserve bulkJob(Upper)
    ' Fill in value.
    bulkJob(Upper) = Id

    ' Do something.
    Debug.Print UBound(bulkJob), bulkJob(Upper)

End Function

"Restart" the array like this:

ReDim bulkJob(0)
bulkJob(0) = 0

answered Apr 17, 2019 at 9:55

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

How to populate a dynamic array in 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