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
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
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)?
Also there are numerous records in the array, don't I have to reference the index like array(x,y)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strTheYear = arrAssessors(0)
strTheState = arrAssessors(1)
strTheAssessor = arrAssessors(2)
MsgBox (strTheAssessor & strTheYear & strTheState)