Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Using arrays (XP/2K)

    I am starting to learn arrays. I am stuck on the following bit of code. First of all is this the correct way to do this?
    Second it gets stuck on the line
    " arrIntIdPAyment(intCount) = rst!IDExtendPayment " and I get the error "subscript out of range".
    Any pointers towards general usage of arrays would be helpful
    As usual Thanks


    Dim arrIntIdPAyment() As Variant
    Dim intMaxReceipt As Integer
    Dim rstTblReceipt As Recordset
    Dim IntPayments As Integer
    Dim intCount As Integer
    Dim rst As dao.Recordset

    Set rst = Me.RecordsetClone



    Debug.Print Me.Count
    Debug.Print rst!IDExtendPayment

    IntPayments = Me.Count

    For intCount = 0 To IntPayments

    arrIntIdPAyment(intCount) = rst!IDExtendPayment

    If Not rst.EOF Then
    rst.MoveNext
    End If

    Next

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    Try changing
    IntPayments = Me.Count

    to
    IntPayments = Me.Count -1



    --------------------
    Dim arrIntIdPAyment() As Variant
    Dim intMaxReceipt As Integer
    Dim rstTblReceipt As Recordset
    Dim IntPayments As Integer
    Dim intCount As Integer
    Dim rst As dao.Recordset

    Set rst = Me.RecordsetClone



    Debug.Print Me.Count
    Debug.Print rst!IDExtendPayment

    reDim arrIntIdPAyment(Me.Count ) ' Might need to do this also

    IntPayments = Me.Count - 1

    For intCount = 0 To IntPayments

    arrIntIdPAyment(intCount) = rst!IDExtendPayment

    If Not rst.EOF Then
    rst.MoveNext
    End If

    Next
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using arrays (XP/2K)

    Thanks. It needed both changes. Now though it goes to a nonexistent record at the end. I will work on that next.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    Zave,

    Try this:


    Dim arrIntIdPAyment() As Variant
    Dim intMaxReceipt As Integer
    Dim rstTblReceipt As Recordset
    Dim IntPayments As Integer
    Dim intCount As Integer
    Dim rst As dao.Recordset

    Set rst = Me.RecordsetClone

    Debug.Print Me.Count
    Debug.Print rst!IDExtendPayment
    ReDim arrIntIdPAyment(Me.Count)
    IntPayments = Me.Count

    intCount = 0
    rst.movefirst

    do until rst.eof
    arrIntIdPAyment(intCount) = rst!IDExtendPayment
    rst.movenext
    intCount= intCount+ 1
    Loop
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using arrays (XP/2K)

    Thanks again

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    I noticed you said that you just started to use Arrays. You might find collections a bit easier to use. I used to use Arrays all over, until I discovered how to build classes to hold data within a collection. Collections are MUCH easier to deal with, in most cases, over arrays. If you're interested, I can give you some pointers on how to get started with Classes and Collections.

  7. #7
    Lounger
    Join Date
    Jan 2004
    Location
    Honolulu, Hawaii, USA
    Posts
    47
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    I agree about using Collections. But be clear, Collections don't only contain Objects, they can contain anything which can be addressed as a Variant! There will be occasional gotchas, but that is probably because of access rights.

    Remember, for Zero based Arrays, the max item is one less than the item count! It is true EVERYWHERE not just Access. That is one of the most common novice errors. For 3 items, they are numbered 0, 1, 2. For 1 based Arrays they are numbered 1, 2, 3.

    For Access, learn there is also an Option, Option Base { 0 | 1 } . Option Base 0 is the default! Use it, everyone else does. If you get in the habit of using Base 1 arrays, you will get tripped up again when you borrow and modify someone else's code.

    Also use Option Explicit. Declare your arrays statically if you can: Dim MyArray (255) as Long (or whatever)
    If you can't know ahead how big they will be you may use: Dim MyArray () as Long; Dim ArraySize as Long; ArraySize = rst.Count; Redim MyArray (ArraySize).
    Just resetting ArraySize won't resize your array, you have to actually execute the Redim statement and whatever ArraySize is at the time you do the Redim, that is how many elements you get. Dynamic arrays can take a lot of time to execute. That is why for dynamic arrays, it is better to use a collection. The collection is engineered to grow dynamically.
    That all being said, if you know that your arrays will NEVER get larger than some reasonable size, it is more efficient to statically allocate them. It is the classic case of the tradeoff of space vs speed.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using arrays (XP/2K)

    Thanks Drew. I would be interested.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    True. Hands done, a static array is faster then a collection. However, I rarely use arrays anymore, since there are so many advantages to using collections. The fact that you can index a collection however you'd like is REALLY handy.

  10. #10
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    I was hoping to have time this weekend to write up a short ditty on how to get your feet wet with Classes and Collections, but just couldn't find the time. So I am going to take a stab at it now.

    To begin with, a Class is a specific type of module. You have normal modules, and Class Modules. A normal module (or .bas file for VBers) simply contain subs and functions, and possibly global variables. However, they do not really represent an 'object'. They are really just code repositories. A class module, on the other hand, is a code representation of an object. Class modules can have properties, events, and functions. Forms ARE class modules. So if you have ever put code behind a form (or report), you have written code for a class module. However, creating a raw Class Module can feel like uncharted territory, so let

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Using arrays (XP/2K)

    I've just picked up this thread, and it seems a good place to ask a question that's been niggling for a while.

    I'm very lazy, and often end up using variants filled with the array command when I have some more or less static data to put in an array, because I can't be bothered to type arrFruit(0)="apple", arrFruit(1)="pear" etc

    I appreciate there are a number of drawbacks to my indolent approach, not least when the static element should really be the first "column" in a 2 dimensional array.

    The data in this type of array is usually static (eg a list of objects I need to repeat the same operation on), but I haven't been able to successfully combine static and array.

    I've never come really come across collections. Would they be a more (speed and memory) efficient alternative to my lazy arrays, or offer me a solution to the "static data" array?

    Thanks

  12. #12
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    For a simple list, arrays are usually the hands down choice. However, if the items in the list could use more 'definition', then classes and collections are the way to go. For example, if you wanted to represent a deck of cards, if all you wanted were the 'names' of the cards, an array would work fine. But if you wanted a deck of 'cards', where the cards new their suit, their value, etc, then a collection full of classes would be the best way to go.

    Drew

  13. #13
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using arrays (XP/2K)

    Sorry I did not respond I was away for almost 2 weeks. I will check this out tomorrow, I am sure this will be helpful.
    Thanks

  14. #14
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using arrays (XP/2K)

    Okay, have fun with it!

Posting Permissions

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