Using Scripting Dictionary to store Objects in MS Access to avoid circular references and allow forms to know their owner objects

Question

I’m building an Access database with classes e.g clsOrder, clsCustomer etc which manage the interface with tables. These classes create instances of forms when displaying their data. I found that once the execution of code was within one of these forms I couldn’t refer to the parent object that created it (so is there a better way of doing this? would be part of my question).

To deal with this I’m using a scripting dictionary to store instances of classes with a key using the ID of the class and a unique identifier for the class (e.g Order-3265). I then store a reference to the owner object in the form itself.

So when an object is created and its ID is known it puts a pointer to itself in the dictionary and gives that pointer to its form (hope that’s clear enough).

This then allows the form to interact with its owner class.

I’m using another class clsManager to do the adding of items to the Dictionary or retrieval or removal (with destruction).

Examples of classes - seriously cut down..

clsManager:

Public WorkingObjects As New Scripting.Dictionary

Public Function AddWorkingObject(key As String, ObjectType As Object) As Boolean
    If Me.WorkingObjects.Exists(key) Then
        Me.WorkingObjects.Remove key
        Me.WorkingObjects.Add key, ObjectType
    Else
        Me.WorkingObjects.Add key, ObjectType
    End If
End Function

Public Function GetWorkingObject(key As String) As Object
    If Me.WorkingObjects.Exists(key) Then
        Set GetWorkingObject = Me.WorkingObjects(key)
    Else
        Set GetWorkingObject = Nothing
    End If
End Function

Public Function DestroyObject(obj As Object) As Boolean
Dim key As String
    If Not obj Is Nothing Then
        key = obj.DictionaryKey
        If Me.WorkingObjects.Exists(key) Then
            Me.WorkingObjects.Remove (key)
            Set obj = Nothing
            If obj Is Nothing Then
                Debug.Print key & " destroyed"
            Else
                Debug.Print obj.DictionaryKey & " NOT destroyed"
            End If
        End If
        Set obj = Nothing
    End If
End Function

clsQuote:

Option Compare Database
    Option Explicit    

'use a form using an instance of this class to control manipulation of Quote records
'Loading and saving set default values if a null value is detected    

Private Const scTABLE As String = "tblQuote"    

Private intID As Long 'unique identifier
Private intCustomerID As Long
Private intSiteID As Long
Private rsQuoteTotalValues As DAO.Recordset
Private oCustomer As clsCustomer
Const ObjectType = "Quote-"
Private oEditForm As Form_frmQuote    

Property Get EditForm() As Form_frmQuote
    Set EditForm = oEditForm
End Property    

Property Get ID() As Long
    ID = intID
End Property
Property Let ID(QuoteID As Long)
    intID = QuoteID
    Me.EditForm.ID = QuoteID
End Property    

Property Get Customer() As clsCustomer
    Set Customer = oCustomer
End Property    

Property Let CustomerID(ID As Long)
    intCustomerID = ID
    oCustomer.Load (ID)
    EditForm.SiteID.RowSource = oCustomer.AddressSQL
    EditForm.SiteID.Requery
    EditForm.ContactID.RowSource = oCustomer.ContactsSQL
    EditForm.ContactID.Requery
    EditForm.CustomerID = ID
End Property    

Property Get DictionaryKey() As String
    DictionaryKey = ObjectType & CStr(Me.ID)
End Property
'END PROPERTIES//////////////////////////////////    

Public Sub DisplayForm(Visibility As Boolean)
    With Me.EditForm
        .Visible = False
        .subFrmQuoteSectionsSummary.SourceObject = ""
        If Visibility = True Then
            ...some stuff...
            .Visible = True
        End If
    End With
End Sub    

Public Function Load(ID As Long) As Boolean
'On Error GoTo HandleError
Dim RS As DAO.Recordset
Dim sQry As String
    Load = False
    If Nz(ID, 0) <> 0 Then
        sQry = "SELECT * FROM " & scTABLE & " WHERE ([ID]=" & ID & ");"
        Set RS = Manager.DB().OpenRecordset(sQry, dbOpenForwardOnly)
            With RS
                If .RecordCount = 0 Then
                    MsgBox "Cannot find Quote with ID = " & ID, vbCritical
                    GoTo Done
                End If
                Me.ID = Nz(!ID, 0)
                Me.CustomerID = Nz(!CustomerID, 0)
                Manager.AddWorkingObject Me.DictionaryKey, Me
                Me.EditForm.SetOwnerObject (Me.DictionaryKey)
                .Close
            End With
        Set RS = Nothing
        Load = True
    End If
Done:
    Exit Function
HandleError:
    MsgBox "Error in Customer Load: " & vbCrLf & Err.Description, vbCritical
    Resume Done
End Function    

Private Sub Class_Initialize()
    Debug.Print "Quote class initialized"
    Set oCustomer = New clsCustomer
    Set oEditForm = New Form_frmQuote
    Me.ID = 0
    Set oQuoteTidier = New clsClassTidier
    Me.DisplayForm (False)
End Sub    

Private Sub Class_Terminate()
    Set oCustomer = Nothing
    Set oEditForm = Nothing
    Debug.Print "Quote class terminated"
End Sub

From the EditForm:

Option Compare Database
Option Explicit    

'necessary for the object to have a reference to its owner in this manner to prevent circular reference
Private OwnerObject As clsQuote    

Public Function SetOwnerObject(OwnerKey As String) As Boolean
    SetOwnerObject = False
    Set OwnerObject = Manager.GetWorkingObject(OwnerKey)
    SetOwnerObject = True
End Function    

Private Sub cmdClose_Click()
    OwnerObject.EditForm.Visible = False
    Manager.DestroyObject OwnerObject
    DoCmd.Close acForm, Me.Name
End Sub

Each business object class (like ClsOrder) has an editForm instance which is loaded and hidden until required and a up to 3 DAO Recordsets that it keeps open.

I think all references to the business objects that are interrelated are pointers to the objects in the dictionary.

My problem is error 3035 exceeding system resources. I’ve checked objects are destroyed when not in use but repeatedly opening and closing objects gets me to error 3035.

So the question is- am I just asking Access to do stuff it can’t or would better programming fix it?

Please share a minimal reproducible example. Note that adding an object to a dictionary creates a strong reference and doesn't really solve reference loops. See This blog on RubberDuckVBA about weak references. Creating reference loops and memory leaks when working with references to forms is real easy in Access, but without the full code it's hard to tell what you're doing. — Erik A
– Erik A, Commented Feb 14, 2020 at 9:38
Thanks Erik. Some code added so you can see what I'm trying to achieve. Many thanks for the pointer to RubberDuck. Really I'm after an answer to "am I mad trying to do it this way?" — Mark Emms
– Mark Emms, Commented Feb 14, 2020 at 10:29
an editForm instance which is loaded and hidden until required is this required? why don't you make a new edit form if you need to edit/show data rather than keeping something hidden? — Krish
– Krish, Commented Feb 14, 2020 at 10:36
Well, perhaps a bit misguided, since IMO using a global-state dictionary to avoid reference loops is more likely to cause than to solve problems. Personally, I sometimes use circular references between forms and classes, and solve this problem by unreferencing the object in the Form_Unload event of the form, that fires when a user closes the form, but before the form object is actually destroyed. Keep the KISS principle in mind, I don't know the exacts of your project but you might be overcomplicating things. — Erik A
– Erik A, Commented Feb 14, 2020 at 10:38
By the way Erik that RubberDuckVBA link is excellent - thanks. — Mark Emms
– Mark Emms, Commented Feb 14, 2020 at 10:50

Albert D. Kallal · Accepted Answer · 2020-02-14 16:55:13Z

I see ZERO reasons to write all that code. Why not let a form handle all of this? Remember, each form is in fact a "class" instance. You can even launch multiple copies of a single form, each with their own code, own data and each instance of the SAME form can operate 100% independent of other working copies of that same form.

If you attempting to look at this problem and wanting to have a class object for a form, then just use the form object - that's what it does for you!

I see zero benefits from writing all that code. While .net has the dataset manager and system (and now the very similar entity framework, this is MUCH done since .net does not have data bound forms.

In Access, each form is in fact a class object. And that includes any public sub or function for that form (so functions become methods of that form, and public vars become properties of that form). In addition to the bound form having a truckload events, these events work as actions against any data editing. So, unlike most systems, you have "on change" event, before update event, after update event. So, by simply adoptiing a bound form, then you get: A class object is automatic created for you. You can have multiple instances of that class, and hence multiple instances of that same form open at the same time. You get all of those data events that can be used for verifiction of data input (or have the user not save the record until such time your critera is met. You have full use of all data columns, even if controls are NOT placed on the form bound to those columns. So, you even get intel-sense for all of the data columns - that is you map.

I am not aware that there is some big huge circular reference problem here. This is like stubbing your toe, but then going to the doctor for some huge open heart by-pass operation. So to go on some huge massive coding spree, and chew up huge amounts of developer dollars for some "rare" issue of some kind of rare and un-seen circular reference issue is essentially a huge wild goose chase that will only have you chewing up huge amounts of developer code and time when NONE is required at all.

I mean, if you have say 3 instances of the SAME form open? Then how does the code know and refernce what insance of that form? Well, the EXACT same approac used in typical OO programming can and should be used here. That approach means you don't HARD CODE the forms! name or referances in code EVER. You never want to do this.

So, if you are in a sub form, and need to referacne say data or controls in the parent form?

You could do this:

strLastName = forms!frmCustomer!LastName

In above, we have hard coded the forms name. You don't want to do that.

In that subform, the correct way to write this code is:

strLastName = me.Parent.form!LastName

Now, note how the above referances the parent form. So, that code will work EVEN if we have 3 copies of the frmCustomer active at the same time. You can full refernce ANYTHING in a form by its object "instance". So, in JavaScrip, or c#, you often see "this.SomProperty" as a refeance to that object.

In access, you can do the same thing, and use "me". Or me.Parent.From to reference the parent form. So, as a general approach here, you should NEVER have to hard code forms reference. If you take this approach, then all issues of circular referencing will not only be eliminated, but then you are using a classic and traditional approach to object programming, and object referencing. While Access is not full OO, it certainly follows a lot of OO design concepts, and how forms work in Access are most certainly instances of a object.

Attempting to write truckloads of code when the forms object model already exists as a "single" class object instance of that form makes no sense, and is not required, and the road you going down will likely hamper and reduce your abilities to deal with the fantastic instance of that form you already have.

As noted, the form already has the dictionaly and columns attached, and Access EVEN generates the members for your automatic. The result is you can reference any column of the table that the form is bound to with

me.LastName me!LastName

While the above two formats are allowed, the first (me + dot + column name) is in fact a member of the forms class. You will find that if you use code to set the forms data source, then often these members are NOT generated for you, and thus you have to use the ! (bang) to reference columns from the table for that form.

So, I don't grasp while you attempting all that extra code when the form has all of the abilities you are asking for in a class object.

Wow....plenty of information there. Thank you. In fact it's all stuff I basically know and what you're telling me is simply - don't be silly, make your class belong to the form rather than doing it the other way round I think? I can see the sense in this. What I was aiming for was a situation where all of the "business logic" was taken out of the form, my mistake is the way I thought about it from what you are saying. No need for the class to be owner of the form...
if I make the clsQuote a variable within the form can I refer to the Form via Me.Parent from within the clsQuote object? if not then I'm back to square 1....
Yes, you can. But the form is already a class, so little need exists to add a class to that form's code module - it already is a class with full use of the data as strong typed and with intel-sense. You would not use "me.Parent", but in fact just use "me" like "this" in c# or say JavaScript. I often in the forms on-open event do this: frmPrevious = Screen.ActiveForm. The result is now any code in the current form can refer to the calling form that opened the form. I can go frmPrevious.Requery, or frmPrevioius!LastName, or whatever any place in that form as a result.

Collectives™ on Stack Overflow

Using Scripting Dictionary to store Objects in MS Access to avoid circular references and allow forms to know their owner objects

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related