Passing a recordset/array from one sub to another?

[TerraEarth]

Is there any way to do such a thing? I saw something similar in a video however it does not seem to work for me.

My Recordset Values/Array Values exist and can be debug.print() to verify that they do exist. However, as soon as I jump to the next sub function as follows:

Sub1
'Code to create recordset/array
With rstRecordSet
    Do Until .EOF
       Sub2
     Loop
End With
EndSub
-------------------------------------
Sub2
...
End Sub
------------------------------------

The Recordset/Array gets wiped and no longer exists.
Sub2 is a routine that populates PDF fields using the values from the recordset/array. Any ideas on why this error is occuring and how to solve/get around it?

[June7]

The recordset/array object variable must be declared in a module header. If all procedures are in the same module such as a form then use that module header. If you want to reference the variable from different modules then it must be declared in a general module header. Example:

Array declared in general module called 'Misc':

Option Compare Database
Option Explicit
Global gstrArrayTestMethods() As Variant

Then the array variable is used in several procedures of two forms. Here is one of the uses.

ReDim gstrArrayTestMethods(8, 1)
gstrArrayTestMethods(LabNumIndex, 1) = strLabNum
gstrArrayTestMethods(TestNumIndex, 1) = strTestNum
gstrArrayTestMethods(TimeIndex, 1) = dblTime
gstrArrayTestMethods(ComboIndex, 1) = booCombo
gstrArrayTestMethods(ComboHasDataIndex, 1) = booComboHasData
gstrArrayTestMethods(MethodIndex, 1) = ""
gstrArrayTestMethods(DexIndex, 1) = intDex
gstrArrayTestMethods(MultiplesIndex, 1) = rsTestInfo!MultipleTestsLimit

Here is example of recordset object variable declared in form header:
Option Compare Database
Option Explicit
Public strOldLabNum
Dim cn As ADODB.Connection
Dim rsOldSample As ADODB.Recordset

Then procedure references variable to set it:

Private Sub cbxOldLabNum_AfterUpdate()Dim strPV As String
Set cn = CurrentProject.Connection
Set rsOldSample = New ADODB.Recordset
...
    rsOldSample.Open "SELECT zSampleInformation.* FROM zSampleInformation WHERE LabNum='" & strOldLabNum & "';", cn, adOpenStatic, adLockPessimistic
...
End Sub

That recordset object is then referenced in another procedure without having to use the Set and Open methods.

Be aware that code interruption (such as run-time errors) will cause global variables to lose content. This can make debugging a bit frustrating.

[TerraEarth]

That sounds kind of bizarre, I will try it out. I gave up on the recordset notion a couple of hours ago and instead tried to figure out how to pass an array through to Sub2. I actually managed to do it, if anyone's interested this is how:

Sub1
dim MyArray(#) As String
'Code to create recordset/array
With rstRecordSet
    Do Until .EOF
       'Code to store recordset values into each array slot
       Call Sub2(MyArray)
     Loop
End With
EndSub
-------------------------------------
Sub2(ByRef MyArray() As String)
...
End Sub
------------------------------------

[June7]

Yes, forgot about that approach, never used it. Although vaguely recall exploring. I guess bizarre is a matter of perception and/or what you learned first.

Glad you figured it out.

[Micron]

Likely you could pass any object to any procedure in any module. While I don't see the point in passing things like forms or controls, I know you can pass a record set so I don't see why you couldn't pass an array. You have to declare them as the proper object type. Because they're not variables, scope doesn't apply. I have passed record sets and even Excel objects to functions in modules other than the module that contains the calling code.

[TerraEarth]

Yes, forgot about that approach, never used it. Although vaguely recall exploring. I guess bizarre is a matter of perception and/or what you learned first.

Glad you figured it out.

Yes, thanks for your help. I plan on trying it out with recordsets instead as that's probably a lot more efficient than what I'm doing.