Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: "Out of Range" in MS ACCESS Arrays

    HI ,

    I needed to develop a project in Access.
    I am tring to use an array to get the values of a table.

    I have a table called Currency rates with currency and rate fields.

    I use the following procedure to get the values to an array
    ********************************
    Dim db as database
    dim rs as recordset
    dim curr() as string
    Dim rate() as double
    dim i as integer

    set db=currentDB()
    set rs=db.openRecordset("CurrencyRates")
    i=1
    rs.movefirst
    DO while not rs.eof

    curr(i)=rs.fields("Currency")
    rate(i)=rs.fields("Rate")

    rs.movenext
    i=i+1
    loop

    Me.text1.value=curr(3)
    Me.text2.value=rate(3)

    *********************

    I get a Subscript out of range Error.
    Can anyone help me with the code because i can't find what is wrong.

    Thanks
    Michael

  2. #2
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Using Arrays

    Hi Michael

    I haven't used arrays before but I *think* you need to declare the fact that your array is multi-dimensional. Something like "dim me (1 to 5, 1 to 20) as currency" declares 2 dimensions.

    Also, I think the data type has to be the same and it doesn't look like yours are: string and currency.

    Have you tried looking at the Using Arrays help file?

  3. #3
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Red face Doh!

    Michael. Ignore that post... I clearly didn't read your code properly and see you're creating 2 arrays. Sorry!


    Andy

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What you need to do is figure out the # of records and redim your arrays accordingly ... Try using the RecordCount method to get that value ... Also remember that indexing in VBA is 1's based ... Technically you can use the 0th element but it is not encouraged.

  5. #5
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    HI guys,

    Thanks for r posts,but my problem still stands.

    If my Q was missleading pleas pardon me.

    I just want the code to populate an array using values from a query.

    I am a bit confused with Redim

    Thanks
    Michael

  6. #6
    Join Date
    Nov 2002
    Location
    Wyoming
    Posts
    48
    Originally posted by mickykt
    HI guys,

    Thanks for r posts,but my problem still stands.

    If my Q was missleading pleas pardon me.

    I just want the code to populate an array using values from a query.

    I am a bit confused with Redim

    Thanks
    Michael
    Hi mickykt

    You are declaing dynamic arrays, and as such they have no elements. That is, they cannot yet hold any data. The ReDim statement declares the size of the array.

    Dynamic arrays are used often when you do not no how many elements you need in an array. So. you use ReDim to declare the initial size and to resize the array so that you can add new data.

    I have modiifed your code:
    Dim db as database
    dim rs as recordset
    dim curr() as string
    Dim rate() as double
    dim i as integer
    Dim iRecCount as Integer

    set db=currentDB()
    set rs=db.openRecordset("CurrencyRates")

    rs.MoveLast
    rs.MoveFirst

    iRecCount = rs.RecordCount

    ReDim curr(iRecCount)
    ReDim rate(iRecCount)

    i=1

    For i = 1 To rs.RecordCount

    curr(i)=rs.fields("Currency")
    rate(i)=rs.fields("Rate")

    rs.movenext
    i=i+1
    Next i

    Me.text1.value=curr(3)
    Me.text2.value=rate(3)


    Set rs = Nothing
    db.Close

    This is probably not perfect but should be close.
    Cheers,
    Ken

  7. #7
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    HI ,

    Thanks That was perfect.

    Also how can i get these curr() values to alist box.
    I have a form with a subform,which has a daatasheet with the two fields.How can i populate this subform,with these two arrays.
    If this is not possible how can i get these values to 2 seperate list boxes

    I tried

    For i = 1 To rs.RecordCount

    curr(i) = rs.Fields("Currency")
    rate(i) = rs.Fields("Rates")

    Me.mylist.itemdata(i)=curr(i)

    rs.MoveNext
    i = i + 1
    Next i

    this returns a no value.

    I would appreciate a help.
    Thanks
    Michael

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by mickykt
    HI ,

    Thanks That was perfect.

    Also how can i get these curr() values to alist box.
    I have a form with a subform,which has a daatasheet with the two fields.How can i populate this subform,with these two arrays.
    If this is not possible how can i get these values to 2 seperate list boxes

    I tried

    For i = 1 To rs.RecordCount

    curr(i) = rs.Fields("Currency")
    rate(i) = rs.Fields("Rates")

    Me.mylist.itemdata(i)=curr(i)

    rs.MoveNext
    i = i + 1
    Next i

    this returns a no value.

    I would appreciate a help.
    Thanks
    Michael
    You could change the RowSourceType to a Value List, also change the nuber of columns into 2

    then use a loop smoething like that


    For i = 1 To rs.RecordCount
       curr(i) = rs.Fields("Currency")
       rate(i) = rs.Fields("Rates")
       Me.mylist=curr(i) & ", " & rate(i) & ", "
       MoveNext
       i = i + 1
    Next i

    Good luck

  9. #9
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi guys,

    Can I populate a liatbox directly from a recodset.

    set rs=Currentdb.openrecordset("Select Location from Address where ID= '"& Me.text1.value & "'")
    Do while not rs.eof

    me.Mylistbox=rs.Fields("Location")

    rs.movenext

    loop

    I don't get any error but the list box is empty.also the property s set to value list for the listbox.recordsourcetype.Is there any other property i must chage or any thing else.
    Can anyone advice me on this.

    thanks
    MIchael

  10. #10
    Join Date
    Nov 2002
    Location
    Wyoming
    Posts
    48
    Hi Miike:

    You can populate a list box from a recordset. The way I know how to do it is with a callback function.

    This link should give you a good start:

    The Access Web

    Let me know if you need further assistance.
    Cheers,
    Ken

Posting Permissions

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