Filling an Array

[JazzBass]

Hi,
I'm using a crosstab query to get some values. What I would like to do is fill the array with the values so I can use the array for a MSChart. The problem I'm having is I don't quite know how to declare the array.

If data comes back, I know it will have at least 2 fields. But it can have more than that and that's what I don't understand how to account for when declaring/dimensioning the array.

I'll keep working on it, but if you could please help, please do so.

Thanks,
JazzBass

[Hannes]

Try the following and feel free to ask again when it doesn't work. I haven't worked with arrays lately so I might miss a detail:

declare a variant array without specifying(--is that how one is supposed to write specifying?--) how many fields or dimensions it will have.

Dim vararray()

when you actually run the query find out how many fields you will need and in the next line do

reDim vararray(xx) where xx is the number of fields you need.
If there is data in the vararray already and you don't want to loose it add 'preserve' after reDim(no qoutes).

Note that you can only redim the last dimension of an array, after you have "really" declared it, i.e. after you have given it a certain number of dimensions/fields.

example:

dim vararray()
...

redim vararray(3,5)
...
redim preserve vararray(3,8)

this will NOT work:

dim vararray
...
redim vararray (3,5)
...
redim preserve vararray(5,5)

the other option is to declare the array for the max fields expected but I think that's a real waste of ram.

Hope that helps

Hannes

[JazzBass]

Thanks,
I think I got the array part done. Now it's just trying to make it work with the MSChart. Do you know how I can get this to work with the following data:

Year    Mandan    SaltLake   Whiting
1999      1          2         3
2000      2          4         5
2001      4          4         6
2002      5          6         8
2003      4          4         5
2004      5          5         5
2005      5          6         6

This data is my recordset I'm trying to work with.

What I need to do is make the Year the x axis and add
the other columns as a chart columns.

This is my code to populate the array:

numcount = rs.RecordCount
iFieldCount = rs.Fields.Count
Select Case numcount
    Case Is <> 0
        ReDim arrValues(1 To numcount, 1 To iFieldCount)
        For i = 1 To numcount
            arrValues(i, 1) = " " & rs("Year")
            
            For b = 2 To iFieldCount
                arrValues(i, b) = rs(b - 1)
            Next b
            rs.MoveNext
        Next i

    Case Else
        MSChart1.TitleText = "No results for " & cmbCompany.Text
        ReDim arrValues(0 To 0, 1 To 2)
        arrValues(0, 1) = " " & "No results"
        arrValues(0, 2) = 0
End Select

I guess I'm back to the drawing board. Any idea how to get this to work with the chart?

Thanks for your help.

JazzBass