Use a dynamic array in multiple forms using Access

Question

With single variable everything works fine, problems appear with dynamic array.

There is a module with global variables:

Option Compare Database
Option Explicit

Global gbl_numberOfPositions As Integer
Global gbl_numberOfDisciplines As Integer
Global gbl_mv_clsJobPostions() As clsJobPostion

Public Sub Init_Globals()
    gbl_numberOfPositions = 0
    gbl_numberOfDisciplines = 0
    ReDim gbl_mv_clsJobPostions(0)
End Sub

In the first form dynamic array is defined

Private Sub Form_Load()

    Call Init_Globals
End Sub

Private Sub InitCBox()
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
ReDim gbl_mv_clsJobPositions(rs.RecordCount)
rs.MoveFirst
i = 1
Do While (Not rs.EOF)
    Set gbl_mv_clsJobPositions(i) = New clsJobPostion
    gbl_mv_clsJobPositions(i).InitializeMe _
        rs![ID Job Position], rs![ID Position], rs![ID Discipline]
    i = i + 1
    rs.MoveNext
Loop
Debug.Print UBound(gbl_mv_clsJobPositions)
End Sub

Then second one is loaded:

Private Sub Form_Load()
    Debug.Print UBound(gbl_mv_clsJobPostions)
End Sub

However two different are returned. In the second case it is zero.

So my question is how to pass dynamic arrays between forms?

Yes, everything works fine :). I corrected all spelling mistakes, and preserve everywhere and it works! — galvanize, Jul 11 '13 at 14:06
Good! Please click to "Accept" the most useful answer, for my happiness, and also to help the next person with this need. — Smandoli, Jul 11 '13 at 15:09

score 1 · Answer 1 · answered Jul 09 '13 at 16:02

1

Instead of:

ReDim gbl_mv_clsJobPositions(rs.RecordCount)

Use:

ReDim Preserve gbl_mv_clsJobPositions(rs.RecordCount)

answered Jul 09 '13 at 16:02

Smandoli

6,919
3
49
83

1

Now size is right, but data in array are different. It looks like array is created independently in both forms. – galvanize Jul 09 '13 at 16:12

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

From another SO post:

Public and Global are nearly identical in their function, however Global can only be used in standard modules, whereas Public can be used in all contexts (modules, classes, controls, forms etc.) Global comes from older versions of VB and was likely kept for backwards compatibility, but has been wholly superseded by Public.

edited May 23 '17 at 12:05

Community

1
1

answered Jul 09 '13 at 15:55

Smandoli

6,919
3
49
83

1

What's more if I write ReDim gbl_mv_clsJobPostions(4) in Init_Globals() then from the second form load sub I recieved 4 as a size od an array. – galvanize Jul 09 '13 at 16:03

Smandoli · Answer 3 · 2013-07-09T16:46:05.357

0

You seem to have two different spellings, and that may be causing the problem:

gbl_mv_clsJobPostions
gbl_mv_clsJobPositions

Also, I'm wondering why you use this:

Global gbl_mv_clsJobPostions() As clsJobPostion
                                  -------------

Why not use As String, or variant, or some other standard variable type?

edited Jul 09 '13 at 16:46

answered Jul 09 '13 at 16:18

Smandoli

6,919
3
49
83

Use a dynamic array in multiple forms using Access

3 Answers3