Results 1 to 3 of 3
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Take Recordset And Put Into Array

    I am attempting to take a recordset and put it's contents into an array. My issue is that the array is always showing up empty. If I manually run the query I get 4 results returned, which is what I expect my array to hold.



    Code:
    Dim rs As Recordset
    Dim myArray As Variant
    Dim i As Integer
    Set rs = CurrentDb.OpenRecordset("Select Description From testTable Group By Description Order by Description;")
    While Not rs.EOF
        myArray(i, 1) = rs.Fields("Description")
        rs.MoveNext
        i = i + 1
    Wend

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are creating a 2 dimensional array (i,1) but only using one field, plus you need to set the array size, which you don't always know when a recordset is involved. So you could do something like
    Code:
    Dim rs As dao.Recordset
    Dim myArray() As Variant
    Dim i As Integer
    
    Set rs = CurrentDb.OpenRecordset("qryAuditRule")
    If Not (rs.BOF And rs.EOF) Then
      rs.MoveLast 'necessary to get the record count
      rs.MoveFirst
      ReDim myArray(rs.RecordCount - 1)
    
       Do Until rs.EOF
         myArray(i) = rs.Fields(0)
         rs.MoveNext
         i = i + 1
       Loop
    
    End If
    However, if you have a recordset, why do you want to put it in an array? It seems unnecessary. Also, you want to Set rs = Nothing but that's usually done in an error handling block.
    Last edited by Micron; 02-16-2020 at 05:34 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    recordsets are arrays.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-26-2019, 07:27 PM
  2. Passing a recordset/array from one sub to another?
    By TerraEarth in forum Programming
    Replies: 5
    Last Post: 05-01-2018, 10:11 PM
  3. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  4. Replies: 19
    Last Post: 11-07-2013, 11:10 AM
  5. Replies: 2
    Last Post: 03-08-2012, 12:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums