title: VBA Access 2010 extracting array elements as individual dynamically named variables
body: <p>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:</p>
  
  <pre><code>**0**       **1**
  8677229     1
  10289183    2
  11981680    3
  13043481    4
  </code></pre>
  
  <p>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: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.  </p>
  
  <p>Any thoughts or tips? </p>
  
  <p>Edit - 
  The below ended up working, thanks for your help:</p>
  
  <pre><code>Dim Anum() As String
  ReDim Anum(0 To highval)
  Dim Pay() As String
  ReDim Pay(0 To highval)
  </code></pre>