Link to home
Start Free TrialLog in
Avatar of keschuster
keschuster

asked on

Access VBA - Assigned and Retriving Values from an Array

Access 2003 VBA

I want to create an array where each index has three elements.  I have the following code to load the array:

DIM arrAssessors(2) as string
   strSQL = "SELECT x, y, x from myTBL"
    rst.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic
    ' Put Data into Array
    initRecords = rst.RecordCount
    arrAssessors = rst.GetRows(initRecords)

Is this correct?


Then how do I retrieve the values?  I have the following code:

    For x = 0 To initRecords - 1
            strTheYear = arrAssessors(0, x)
            strTheState = arrAssessors(1, x)
            strTheAssessor = arrAssessors(2, x)
            MsgBox (strTheAssessor & strTheYear & strTheState)
    next x


           
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image


            strTheYear = arrAssessors(0)
            strTheState = arrAssessors(1)
            strTheAssessor = arrAssessors(2)
            MsgBox (strTheAssessor & strTheYear & strTheState)
 
Avatar of keschuster
keschuster

ASKER

I get an error - "Can't assign to Array" on arrAssessors = rst.GetRows(initRecords)

Also there are numerous records in the array, don't I have to reference the index like array(x,y)?
SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok - how do I iterate through the records and pull out x,y,z into their on vars as I'm attemplting here

  For x = 0 To initRecords - 1
            strTheYear = arrAssessors(0, x)
            strTheState = arrAssessors(1, x)
            strTheAssessor = arrAssessors(2, x)
            MsgBox (strTheAssessor & strTheYear & strTheState)
    next x
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial