MS Access VBA using ReDim Preserve to increase the size of an array when needed( as in within a button click event handler method or within a loop )

I'm new to Microsoft Office Professional Plus 2013 Access.

I am developing an application using:

-Microsoft Office Professional Plus 2013 Access

I my VBA Editor, I have the following Class Module:

Option Explicit
Option Compare Database

Private cntrollingPersonFullNameProp As String
Private cntrollingPersonIsNameAddressProvidedProp As String
Private cntrollingPersonIsDOBProvidedProp As String
Private cntrollingPersonIsTaxResidenceProvidedProp As String
Private cntrollingPersonIsControllingPersonTypeProvidedProp As String
Private cntrollingPersonIsSignedAndDatedProp As String

Public Property Get CntrollingPersonFullName() As String
    CntrollingPersonFullName = cntrollingPersonFullNameProp
End Property

Public Property Let CntrollingPersonFullName(lCntrollingPersonFullName As String)
    cntrollingPersonFullNameProp = lCntrollingPersonFullName
End Property

Public Property Get CntrollingPersonIsNameAddressProvided() As String
    CntrollingPersonIsNameAddressProvided = cntrollingPersonIsNameAddressProvidedProp
End Property

Public Property Let CntrollingPersonIsNameAddressProvided(lCntrollingPersonIsNameAddressProvided As String)
    cntrollingPersonIsNameAddressProvidedProp = lCntrollingPersonIsNameAddressProvided
End Property

Public Property Get CntrollingPersonIsDOBProvided() As String
    CntrollingPersonIsDOBProvided = cntrollingPersonIsDOBProvidedProp
End Property

Public Property Let CntrollingPersonIsDOBProvided(lCntrollingPersonIsDOBProvided As String)
    cntrollingPersonIsDOBProvidedProp = lCntrollingPersonIsDOBProvided
End Property

Public Property Get CntrollingPersonIsTaxResidenceProvided() As String
    CntrollingPersonIsTaxResidenceProvided = cntrollingPersonIsTaxResidenceProvidedProp
End Property

Public Property Let CntrollingPersonIsTaxResidenceProvided(lCntrollingPersonIsTaxResidenceProvided As String)
    cntrollingPersonIsTaxResidenceProvidedProp = lCntrollingPersonIsTaxResidenceProvided
End Property

Public Property Get CntrollingPersonIsControllingPersonTypeProvided() As String
    CntrollingPersonIsControllingPersonTypeProvided = cntrollingPersonIsControllingPersonTypeProvidedProp
End Property

Public Property Let CntrollingPersonIsControllingPersonTypeProvided(lCntrollingPersonIsControllingPersonTypeProvided As String)
    cntrollingPersonIsControllingPersonTypeProvidedProp = lCntrollingPersonIsControllingPersonTypeProvided
End Property

Public Property Get CntrollingPersonIsSignedAndDated() As String
    CntrollingPersonIsSignedAndDated = cntrollingPersonIsSignedAndDatedProp
End Property

Public Property Let CntrollingPersonIsSignedAndDated(lCntrollingPersonIsSignedAndDated As String)
    cntrollingPersonIsSignedAndDatedProp = lCntrollingPersonIsSignedAndDated
End Property

In the Form code file,

Dim cntrollingPersonsArray()  As CntrollingPerson


Private Sub AddControllingPersonBtn_Click()
     Dim cntrlPerson As New CntrollingPerson
    cntrlPerson.CntrollingPersonFullName =  …….
    cntrlPerson.CntrollingPersonIsNameAddressProvided =  …..

  ReDim Preserve cntrollingPersonsArray(UBound(cntrollingPersonsArray)+ 1)   
cntrollingPersonsArray(UBound(cntrollingPersonsArray)) = cntrlPerson 

 End Sub

The application throws the:

'91' Object variable or With block variable not set

at the following line

cntrollingPersonsArray(UBound(cntrollingPersonsArray)) = cntrlPerson

I've tried a bunch of different code modifications

ReDim Preserve cntrollingPersonsArray(UBound(cntrollingPersonsArray))

ReDim Preserve cntrollingPersonsArray(0 to UBound(cntrollingPersonsArray))

 ReDim Preserve cntrollingPersonsArray(1 to UBound(cntrollingPersonsArray))

Could someone please show me what steps to take in order to correct the aforementioned problem?

Solution

Use a collection object instead of an array. All your problems are solved!

EXAMPLE:

Option Explicit

Private cntrollingPersons As New Collection

Private Sub AddControllingPersonBtn_Click()
    Dim cntrlPerson As New CntrollingPerson
    cntrlPerson.CntrollingPersonFullName = ""
    cntrlPerson.CntrollingPersonIsNameAddressProvided = ""

    cntrollingPersons.Add cntrlPerson
End Sub