Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    im wanting to store items in a 2 dimensional array.

    as my code goes to the next record, the result is the previous record values are reset and left as empty. see screen capture.

    perhaps another set of eyes maybe to see the error in my code.

    many thanks

    diana




    Code:
    Dim x As Long
    Dim arrStaff()
    
    i = 0
    x = 0
    
    
    While Not objRecordSet.EOF
    
        intUAC = objRecordSet.Fields("userAccountControl")
    
        If intUAC And ADS_UF_ACCOUNTDISABLE Then
    
         ' account is disabled, ignore it.
    
    
         Else
    
            On Error Resume Next
    
            strADLogon = "": strADLogon = objRecordSet.Fields("sAMAccountName")
    
             'data filtering
            lngValidPayrollNumber = Val(strADLogon)
    
            If lngValidPayrollNumber <> 0 Then
    
                strADLogon = "": strADLogon = objRecordSet.Fields("sAMAccountName")
                strFirstname = "": strFirstname = objRecordSet.Fields("givenName")
                strLastname = "": strLastname = objRecordSet.Fields("sn")
                strInitials = "": strInitials = objRecordSet.Fields("initials")
                strDisplayName = "": strDisplayName = objRecordSet.Fields("displayName")
    
    
                'store in array
                ReDim Preserve arrStaff(i, 4)
            
                arrStaff(i, 0) = strADLogon
                arrStaff(i, 1) = strFirstname
                arrStaff(i, 2) = strLastname
                arrStaff(i, 3) = strInitials
                arrStaff(i, 4) = strDisplayName
            
                i = i + 1   
    
           End If
    
          End If
    
        objRecordSet.MoveNext
    
     Wend
    Attached Images Attached Images

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts
    An alternative approach is to have the dimension of the array that you want to be able to ReDim be the last dimension, and do an initial ReDim that sets it to the maximum amount you'd ever have. Then do a Final ReDim Preserve at the end that shrinks it down to the actual number of stored records.

    I think I may have made all the appropriate tweaks to your code (below), but didn't test it.

    Code:
    Dim x As Long
    Dim arrStaff()
    
    i = 0
    x = 0
    ReDim arrStaff(4, 1000) 'The 2nd no. should be as big as you'd ever need.
    
    
    While Not objRecordSet.EOF
    
        intUAC = objRecordSet.Fields("userAccountControl")
    
        If intUAC And ADS_UF_ACCOUNTDISABLE Then
    
         ' account is disabled, ignore it.
    
    
         Else
    
            On Error Resume Next
    
            strADLogon = "": strADLogon = objRecordSet.Fields("sAMAccountName")
    
             'data filtering
            lngValidPayrollNumber = Val(strADLogon)
    
            If lngValidPayrollNumber <> 0 Then
    
                strADLogon = "": strADLogon = objRecordSet.Fields("sAMAccountName")
                strFirstname = "": strFirstname = objRecordSet.Fields("givenName")
                strLastname = "": strLastname = objRecordSet.Fields("sn")
                strInitials = "": strInitials = objRecordSet.Fields("initials")
                strDisplayName = "": strDisplayName = objRecordSet.Fields("displayName")
    
    
                'store in array
            
                arrStaff(0, i) = strADLogon
                arrStaff(1, i) = strFirstname
                arrStaff(2, i) = strLastname
                arrStaff(3, i) = strInitials
                arrStaff(4, i) = strDisplayName
            
                i = i + 1   
    
           End If
    
          End If
    
        objRecordSet.MoveNext
    
     Wend
    
    ReDim Preserve arrStaff(4, i - 1)

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks paul, & st3333ve


    sorry - but im still confused aboud defining a multi-dimensional array

    you recommended...

    ReDim arrStaff(4, 1000) 'The 2nd no. should be as big as you'd ever need.


    the total records returned are more than 3000.
    and each record has 5 fields (possibly more)

    therefore would defining the array be...

    ReDim arrStaff(3000, 5)

    or put another way

    ReDim arrStaff(UBound, 5) - i get an error on this syntax


    diana

  4. #4
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Diana View Post
    thanks paul, & st3333ve


    sorry - but im still confused aboud defining a multi-dimensional array

    you recommended...

    ReDim arrStaff(4, 1000) 'The 2nd no. should be as big as you'd ever need.


    the total records returned are more than 3000.
    and each record has 5 fields (possibly more)

    therefore would defining the array be...

    ReDim arrStaff(3000, 5)

    or put another way

    ReDim arrStaff(UBound, 5) - i get an error on this syntax


    diana
    A limitation with ReDim Preserve is that you can only ReDim the last dimension. That's why I flipped your array so the first dimension was the 0-4 dimension and the 2nd dimension was the record number (or actually, you know, record number - 1). The idea is that you do an initial ReDim (nothing to Preserve yet) that sizes the array so that it's as big as it could possibly need to be. So, in your case, if you might have as many as (but no more than) 4000 records, you could ReDim arrStaff(4, 4000). Then do the While/Wend that puts the values into the array. And then, at the end (not as a necessary step, but as a step that will make the code more resource-efficient during the time when the array remains in memory and gets accessed for various purposes), you can do a final ReDim Preserve where you shrink the 2nd dimension down to actual size, using whatever i (or, as you've structured your loop, i - 1) turned out to be.

    If you'll know, before you start your While/Wend loop, exactly how many records are going to end up in the array, you don't need to do this two-step where you first set the maximum size and then you shrink it at the end. But if you'll know the array's size right from the start, you don't need the ReDim Preserve approach at all, so I assumed the max-size-then-shrink approach made sense.

    Note that it would be possible to write your code more like you initially did, and do a separate ReDim Preserve within the loop that expanded the array by one as you added each record's data. (You'd still need to flip the dimensions like I did because, again, you can only resize the 2nd dimension if you're going to use Preserve.) But my understanding is that this is considered a really poor approach because doing a ReDim Preserve is resource-intensive, so code that does it over and over will be slow code.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks st3333ve

    diana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •