declaration of global array

[cjac]

Afternoon all,
I want to declare an array to hold a varying amount of strings which is dependant on results in a recordset. The problem is that I want the array to be global so I can pass the contents onto other procedures yet I will only know how many elements I need at runtime. If I redim the var in the procedure which executes the sql I can set the number of elements by querying my recordsets recordcount, however, the contents will obviously only exist in the array whilst this procedure is being run - ie. as soon as I move into the next procedure the contents of the array will be erased.
How do I get around this? Any ideas?

Cheers
cjac

 

[DarenNM]

Hi cjac

I don't think this is the case. If your array is declared globally and you change the dimensions and populate it, the data will be there for all procedures.

I'll just try this to make sure....

Yes, I declared an array in a module and used a subroutine in a form to resize it and fill it with data. I then used a subroutine on another form to display the contents.

How have you declared the array?

Regards

Daren


Must think of a witty signature

 

[cyberbiker]

Have you thought of using a collection?

Whe I started programming VB, my experience with structured programming lead me to believe arrays were the best way to go.

But after many difficulties, I finally figurred out that collections are often superior.

Read up on collections in help.

Terry (cyberbiker)

 

[AndyTWI]

Either an array or collection could be used, and both can be declared as Public variables in .bas modules.

 

[cyberbiker]

Yes, but you do not have to redim a collection.
Also, from what I can determine, Collections are more efficient (although some truly smart person may be able to prove me wrong on this).

Depending on recordcount to redim an array could be problematical.

There are many threads in these forums referencing problems when using recordcount.
I tend to struggle sometimes and feel it is best to reduce potential problems from the start.

However, after rereading the initial post, I realize that I was not addressing the posted question.

Apologies.

Terry (cyberbiker)

 

[vb5prgrmr]

In bas module you can...
[tt]
Dim MyArray() As Varient
Dim MyCollection As Collection
Dim MyDictionary As Dictionary 'need ms scripting object and this is faster than a collection in accessing elements
[/tt]

Then in Form1...
[tt]
Dim SQL As String, RecCnt As Double
SQL = "Select Count(MyField) As TheCount From MyTable ...
'execute dao/ado/rdo query to return number of records from table
RecCnt = ado/rdo/dao.field("TheCount").Value
ReDim MyArray(RecCnt)
'...
[/tt]
There is a reason I am pointing out the ability to get a count from what you are selecting instead of using the recordcount property, and the reason is because depending upon the circumstances the recordset recordcount can return -1 (in case of rdo) or 1 (in case of ado/dao).

Good Luck

 

[cjac]

Thanks to all for the responses. I have found you can actually redim a global array in one procedure and then use the array in another procedure without loss of contents. So I can:
ReDim myref(adomyRS.RecordCount) to get the number of elements I need, then:
For i = 1 To adomyRS.RecordCount
myref(i) = adomyRS.Fields!acct
adomyRS.MoveNext
Next i
I can then use myref(i) in other procedures to reference against the content.
As for the recordcount property being inaccurate - I find that if you use client side cursor this works fine.

Cheers again.
cjac