VBA Access 2010 extracting array elements as individual dynamically named variables

Question

I am working through an Access 2010 VBA script. I am pulling information from a temporary table that may have 1 to 10 records in it. I am using GetRows to build a two-dimensional array that looks like this, for example:

**0**       **1**
8677229     1
10289183    2
11981680    3
13043481    4

I have tested the array function by debug print, and the array does contain values. I want to split out the array values dynamically into variables for later use. In this example, I would like the 0 column to produce 4 variables named Anum(0) through Anum(3), and the 1 column to produce 4 variables named Pay(0) through Pay(3). However, I keep getting a "Error 9, subscript out of range" where indicated below:

Dim db As Database
Dim rs As Recordset
Dim PayAcct As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Anum, Pay FROM PayPerAcct")
Dim highval As Integer
Dim i As Integer
i = 0

While Not rs.EOF
   PayAcct = rs.GetRows(10)
Wend
highval = UBound(PayAcct, 2)

Dim Anum() As Variant
Dim Pay() As Variant

For i = 0 To highval
    'error occurs on the below line
    Anum(i) = CStr(PayAcct(0, i))
    Pay(i) = CStr(PayAcct(1, i))
Next i

When I manually define Anum and Pay variables and use the Cstr(PayAcct(1,0)) operation, it passes the expected value from the array to the variable, so I don't think that is the problem.

I suspect I am assigning Anum() and Pay() the wrong dimension, because I have seen similar example code in Excel VBA where defining those variables as Range works. I have tried defining Anum() and Pay() as Range (which doesn't work, because this is Access VBA), Variant, and Object.

Any thoughts or tips?

Edit - The below ended up working, thanks for your help:

Dim Anum() As String
ReDim Anum(0 To highval)
Dim Pay() As String
ReDim Pay(0 To highval)

I am curious of your use of arrays. Unlike Excel, Access maintains a readily available indexed data structure called a table where you easily filter/search/condition values by IDs. I feel your end solution can be handled with a SQL query where the Jet/ACE engine is used instead of holding data objects in memory. — Parfait
– Parfait, Commented Nov 28, 2015 at 16:55
I am sure you are correct. I haven't programmed anything in more than 20 years, and I am trying to figure out VBA as I go. The end result of this procedure is to establish variables (Anum variables) to be tested against values in another array, keeping the one-to-one connection with Pay variables, so that I can insert the associated Pay value in a web form when the Anums match. I couldn't figure out how to do it with the arrays themselves, and I couldn't figure out how to keep a connection between the Anum and associated Pay value in SQL, which is why I headed down this path. — Tom Houlihan
– Tom Houlihan, Commented Nov 28, 2015 at 19:13
You see, your needs can be handled with join SQL query. Use recordset here and inner join on Pay field of other array's recordset. Your Anum variables could just be WHERE conditions. — Parfait
– Parfait, Commented Nov 28, 2015 at 21:46
I wasn't aware that you could make a recordset out of data presented in web form. Looks like I have a lot more learning to do. — Tom Houlihan
– Tom Houlihan, Commented Nov 29, 2015 at 2:46

Andre · Accepted Answer · 2015-11-28 16:41:20Z

2

Dim Anum() As Variant

declares a dynamic array that hasn't any elements yet, so you can't assign values to it. It needs a ReDim to use it.

But since you already know how many elements you need, what you want is:

Dim Anum(0 To highval) As Variant

or if you know that you will only store strings in it,

Dim Anum(0 To highval) As String

answered Nov 28, 2015 at 16:41

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

1 Comment

Tom Houlihan Over a year ago

I ended up dimensioning and then redimming the variables, as appended to my question above. Thank you again for your help.

Collectives™ on Stack Overflow

VBA Access 2010 extracting array elements as individual dynamically named variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related