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
– galvanize, Commented Jul 11, 2013 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
– Smandoli, Commented Jul 11, 2013 at 15:09

Smandoli · Accepted Answer · 2013-07-09 16:02:36Z

1

Instead of:

ReDim gbl_mv_clsJobPositions(rs.RecordCount)

Use:

ReDim Preserve gbl_mv_clsJobPositions(rs.RecordCount)

answered Jul 9, 2013 at 16:02

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

galvanize Over a year ago

Now size is right, but data in array are different. It looks like array is created independently in both forms.

Community · Accepted Answer · 2017-05-23 12:05:28Z

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, 2017 at 12:05

CommunityBot

11 silver badge

answered Jul 9, 2013 at 15:55

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

1 Comment

galvanize Over a year ago

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.

Smandoli · Accepted Answer · 2013-07-09 16:46:05Z

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 9, 2013 at 16:46

answered Jul 9, 2013 at 16:18

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

Collectives™ on Stack Overflow

Use a dynamic array in multiple forms using Access

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related