• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3679
  • Last Modified:

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


           
0
keschuster
Asked:
keschuster
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:

            strTheYear = arrAssessors(0)
            strTheState = arrAssessors(1)
            strTheAssessor = arrAssessors(2)
            MsgBox (strTheAssessor & strTheYear & strTheState)
 
0
 
keschusterAuthor Commented:
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)?
0
 
Arthur_WoodCommented:
small change:

DIM arrAssessors() as string  ' do not preset the dimensions, they will be set when the GetRows method is executed.
   strSQL = "SELECT x, y, x from myTBL"
    rst.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic
    ' Put Data into Array
    initRecords = rst.RecordCount
    arrAssessors = rst.GetRows(initRecords)

AW
0
 
keschusterAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
here is an example from Help using DAO

Sub GetRowsX()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim strMessage As String
   Dim intRows As Integer
   Dim avarRecords As Variant
   Dim intRecord As Integer

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = dbsNorthwind.OpenRecordset( _
      "SELECT FirstName, LastName, Title " & _
      "FROM Employees ORDER BY LastName", dbOpenSnapshot)

   With rstEmployees
      Do While True
         ' Get user input for number of rows.
         strMessage = "Enter number of rows to retrieve."
         intRows = Val(InputBox(strMessage))

         If intRows <= 0 Then Exit Do

         ' If GetRowsOK is successful, print the results,
         ' noting if the end of the file was reached.
         If GetRowsOK(rstEmployees, intRows, _
               avarRecords) Then
            If intRows > UBound(avarRecords, 2) + 1 Then
               Debug.Print "(Not enough records in " & _
                  "Recordset to retrieve " & intRows & _
                  " rows.)"
            End If
            Debug.Print UBound(avarRecords, 2) + 1 & _
               " records found."

            ' Print the retrieved data.
            For intRecord = 0 To UBound(avarRecords, 2)
               Debug.Print "  " & _
                  avarRecords(0, intRecord) & " " & _
                  avarRecords(1, intRecord) & ", " & _
                  avarRecords(2, intRecord)
            Next intRecord
         Else
            ' Assuming the GetRows error was due to data
            ' changes by another user, use Requery to
            ' refresh the Recordset and start over.
            If .Restartable Then
               If MsgBox("GetRows failed--retry?", _
                     vbYesNo) = vbYes Then
                  .Requery
               Else
                  Debug.Print "GetRows failed!"
                  Exit Do
               End If
            Else
               Debug.Print "GetRows failed! " & _
                  "Recordset not Restartable!"
               Exit Do
            End If
         End If

         ' Because using GetRows leaves the current record
         ' pointer at the last record accessed, move the
         ' pointer back to the beginning of the Recordset
         ' before looping back for another search.
         .MoveFirst
      Loop
   End With

   rstEmployees.Close
   dbsNorthwind.Close

End Sub

Function GetRowsOK(rstTemp As Recordset, _
   intNumber As Integer, avarData As Variant) As Boolean

   ' Store results of GetRows method in array.
   avarData = rstTemp.GetRows(intNumber)
   ' Return False only if fewer than the desired number of
   ' rows were returned, but not because the end of the
   ' Recordset was reached.
   If intNumber > UBound(avarData, 2) + 1 And _
         Not rstTemp.EOF Then
      GetRowsOK = False
   Else
      GetRowsOK = True
   End If

End Function
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now