dynamic arrays

  • Thread starter Thread starter Hugh Welford
  • Start date Start date
H

Hugh Welford

Hi

I am trying to populate a dynamic array using redim at function level with
the code

Set theset = thedb.OpenRecordset("medications")
Call populatetablearray(theset.RecordCount)

Function populatetablearray(tablecount)
Dim thedb As Database
Set thedb = CurrentDb()
Dim theset2 As Recordset
Set theset2 = thedb.OpenRecordset("medications")
ReDim tablearray(tablecount )
For I = 1 To tablecount
tablearray(I) = theset2(0)
Debug.Print tablearray(I)
theset2.MoveNext
Next I
End Function

The debug print shows the array being populated as i expect, and a
?tablearray(6) within the intermediate window shows the correct entry, but
as soon as I exit from the function aceessing the array e.g. ?tablearray(6)
from within the intermediate window gives a "error 9 subscript out of range"
error message.

Can any one tell me what is wrong and what I should do to correct it.

Thanks Hugh
 
With just a quick look, it appears that tablearray is defined just within
the function, as soon as you exit it will go back to the default dim
(variant). You should be able to tell this by looking in the "locals" window
of the debugger.

decdav
 
Hi Doug - thanks for answering.

I have declared the array in a button on-click event procedure. The
following code leads to the function call...

Dim tablearray() As String 'dynamic array
Set theset = thedb.OpenRecordset("medications")
Call populatetablearray(theset.RecordCount)

Hugh
 
If it's defined within a procedure, it only applies within that procedure.

Put that declaration at the very beginning of the file, before any procedure
declarations.

The fact that it works at all makes me suspect that you have not told Access
to force declaration of all variables (it's an option on the Modules tab
under Tools | Options). A sign that you have told it to is that there will
be a line Option Explicit at the beginning of each module. If that line's
not there, add it: while it may seem to be a pain having to explicitly
declaring every variable, it'll save you literally hours in debugging.
 
this is going to seem like a really dumb question. but
all the coding you've done, where exactly have you put
it. that's my problem. i can write the code, i know what
to do, but i don't know where to enter it!
 
thanks Doug

Douglas J. Steele said:
If it's defined within a procedure, it only applies within that procedure.

Put that declaration at the very beginning of the file, before any procedure
declarations.

The fact that it works at all makes me suspect that you have not told Access
to force declaration of all variables (it's an option on the Modules tab
under Tools | Options). A sign that you have told it to is that there will
be a line Option Explicit at the beginning of each module. If that line's
not there, add it: while it may seem to be a pain having to explicitly
declaring every variable, it'll save you literally hours in debugging.
Click to expand...
 
thanks dave

Dave DeCoursey said:
With just a quick look, it appears that tablearray is defined just within
the function, as soon as you exit it will go back to the default dim
(variant). You should be able to tell this by looking in the "locals" window
of the debugger.

decdav
Click to expand...
 
Back
Top