DoCmd.Close Current Form from Control Wrapper (WithEvents) Class Hard-Crashes Access-CodePudding

The command:

DoCmd.Close acForm, lstrParentName, acSaveNo

in a combo box wrapper class (i.e., a class module that assigns a combo box object to a combo box variable declared WithEvents) consistently hard-crashes Access and I can't figure out why.

Context:

The form is bound to a form wrapper class (clsFrm), which binds its controls to control-specific control wrapper classes (e.g., clsCbo).
clsCbo sinks events for bound combo boxes, including OnDblClick.
clsCbo binds a secondary combo box wrapper that encapsulates functionally related code.
clsCbo.mCbo_DblClick() calls a nav procedure (nSuper()) that opens another form and closes the current one with DoCmd.Close acForm, lstrParentName, acSaveNo, which causes Access to hard crash / fail / involuntarily shut down.

What's baffling is:

The nav procedure runs without error and hits its exit procedure.
The source / originating form closes and all its form and control wrappers hit their Class_Terminate() procedures without error.
The destination form wrapper hits its binding method and subforms begin to load.
Closing the form manually by clicking on its window's "X" button in the upper-right causes no difficulty.

But still, Access crashes. Nevertheless:

Comment out the DoCmd.Close command and no problem.
Skip binding the secondary combo box wrapper and no problem.

Any ideas what's up?

nSuper() is as follows:

Private Sub nSuper()
'   Method to navigate from cboSuper~ID to the entity form.

'   Dependencies:
'   -   mstrPath declared in Class_Initialize()
'   -   stdObjectTests.IsSubform()

'   Dependants:
'   -   mCbo_DblClick

    On Error GoTo ErrorHandler
    
200     Dim cboMe As ComboBox
210     Dim frmMe As Access.Form
220     Dim intPK As Long
230     Dim strDest As String
240     Dim strEnt As String
250     Dim strFld As String
260     Dim strPK As String
270     Dim strRS As String
280     Dim strWHERE As String

'       Derive the filter criterion from the control:
290     Set cboMe = mCbo
310     intPK = cboMe.Value

'       Derive Entity from Form.RecordSource:
320     Set frmMe = mParent
350     strRS = frmMe.RecordSource
370     strEnt = Right(strRS, Len(strRS) - 3)

'       Compose the destination Form.Name from Entity:
400     If strEnt = "Party" Then
410         strDest = "frmFirm"
420     Else
430         strDest = "frm" & strEnt
440     End If

'       Compose the filter:
460     strPK = strEnt & "ID"
480     strFld = "tbl" & strEnt & "." & strPK
500     strWHERE = strFld & " = " & intPK

510     If strDest = frmMe.Name Then
'       Re-filter the current form if it is the entity form:
520         frmMe.Filter = strWHERE
530         frmMe.FilterOn = True

540     Else
'       Otherwise, open the entity form:
550         DoCmd.OpenForm strDest, acNormal, , strWHERE

'       Close the current form or its parent:
'       -   Either may crash Access:
560         If IsSubform(frmMe) Then
570             DoCmd.Close acForm, frmMe.Parent.Name, acSaveNo
580         Else
590             DoCmd.Close acForm, frmMe.Name, acSaveNo
600         End If
610     End If

ExitProcedure:
913     Close lintFF
914     Set lFso = Nothing
    Set cboMe = Nothing
    Set frmMe = Nothing
    Exit Sub
ErrorHandler:
890     Resume ExitProcedure
End Sub 'nSuper()

CodePudding user response：

A solution emerged earlier today.

This could be either a coding error or a bug in either the Access.Form object or garbage collection / IUnknown.

The source of the problem was having the primary combo box wrapper class bind the control to a secondary wrapper class (which exists to encapsulate functionally related code). Then, doing DoCmd.Close acForm, lstrParentName, acSaveNo hard-crashed Access. The failure didn't occur when the secondary class was not bound.

On Form.Close, object references all were expressly cleared in Termination event procedures but apparently, the secondary class' control reference persisted long enough that the combo box may have outlived its form. How a Form object can close while items continue to exist in its Controls collection is beyond me, but I digress. The result was to hard-crash Access, notwithstanding that:

all references to the secondary class were cleared (so garbage collection ought to have cleared its object references in any event, while destroying the object);
its Class_Terminate() procedure was triggered and its combo box reference was expressly cleared there.

Note, of course, that objects are passed ByRef (i.e., by reference) in VBA by default.

The solution is to sink Form.Close in the secondary class and clear the class' combo box reference there because doing it in Class_Terminate() requires waiting for garbage collection to cascade through other objects, which is too late to prevent Access from failing. Note that attempting to clear the secondary class' combo box reference from anywhere within the primary class also fails.

Specifically:

In both classes, declare a Form object variable WithEvents and assign the combo box' parent to it. Yes, it is especially odd that the secondary class cannot sink its bound object's parent's events without the primary class also binding the parent because all of the objects are passed by reference, and because regardless, that object's Parent property ought to be immutable, but this is the case in practice.
Nothing is necessary in the primary class beyond assigning the Form object variable. It is not necessary, or even effective, to sink Form.Close in the primary class.
Sink Form.Close in the secondary class and clear its combo box variable there.

My experimental test classes are as follows:

The primary class:

'wecCboTestPrimary

Option Compare Database
Option Explicit

Private WithEvents mCbo As ComboBox
Private WithEvents mParent As Access.Form
Private mwecCboTestSecondary As New wecCboTestSecondary
Private mstrName As String

Private Sub Class_Initialize()
    mstrName = "wecCboTestPrimary"
    Debug.Print mstrName & ".Class_Initialize()"
End Sub

Private Sub Class_Terminate()
    Debug.Print vbTab & mstrName & ".Class_Terminate()"

'   Fails to avoid an Access hard crash:
'   Set mwecCboTestSecondary.BoundCbo = Nothing

    Set mwecCboTestSecondary = Nothing
    Set mParent = Nothing
    Set mCbo = Nothing
    Debug.Print vbTab & mstrName & ".Class_Terminate().End"
End Sub

Public Property Get BoundCbo() As ComboBox
    Debug.Print mstrName & ".PPG BoundCbo()"
    Set BoundCbo = mCbo
End Property

Public Property Set BoundCbo(lCbo As ComboBox)
    Debug.Print mstrName & ".PPS BoundCbo()"
    
'   Bind the control:
    Set mCbo = lCbo
    
'   Sink its events:
    mCbo.OnDblClick = "[Event Procedure]"

'   Bind the control's parent:
    Set mParent = lCbo.Parent
    
'   Sinking parent events is unnecessary to avoid an Access hard crash:
    'mParent.OnClose = "[Event Procedure]"

'   Bind the control to the secondary combo box wrapper class:
    Set mwecCboTestSecondary.BoundCbo = lCbo
    
    Debug.Print mstrName & ".PPS BoundCbo()" & vbTab & mCbo.Name
    
End Property

Private Sub mJustCloseMe()
    Debug.Print vbCrLf & vbTab & mstrName & ".JustCloseMe()" & vbTab & mCbo.Name & vbCrLf
    
    Dim lstrParentName As String
    lstrParentName = mCbo.Parent.Name
    
    'Hard crashes Access absent Goldilocks:
    DoCmd.Close acForm, lstrParentName, acSaveNo
    
End Sub

Private Sub mCbo_DblClick(Cancel As Integer)
    Debug.Print mstrName & ".mCbo_DblClick()" & vbTab & mCbo.Name
    mJustCloseMe
End Sub

'Private Sub mParent_Close()

    'Fails to print:
    'Debug.Print mstrName & ".mParent_Close()" & vbTab & mCbo.Name
    
    'Fails to avoid an Access hard crash (any or in combination):
    'Set mwecCboTestSecondary.BoundCbo = Nothing
    'Set mwecCboTestSecondary = Nothing
    'Set mParent = Nothing
    
'End Sub

'End wecCboTestPrimary

The secondary class:

'wecCboTestSecondary

Option Compare Database
Option Explicit

Private WithEvents mCbo As ComboBox
Private WithEvents mParent As Access.Form
Private mstrName As String

Private Sub Class_Initialize()
    mstrName = "wecCboTestSecondary"
    Debug.Print mstrName & ".Class_Initialize()"
End Sub

Private Sub Class_Terminate()
    Debug.Print vbTab & vbTab & mstrName & ".Class_Terminate()"
    
'   Fails to avoid an Access hard crash:
'   Set mCbo = Nothing

    Set mParent = Nothing
    
    Debug.Print vbTab & vbTab & mstrName & ".Class_Terminate().End"
End Sub

Public Property Get BoundCbo() As ComboBox
    Debug.Print mstrName & ".PPG BoundCbo()"
    Set BoundCbo = mCbo
End Property

Public Property Set BoundCbo(lCbo As ComboBox)
    Debug.Print mstrName & ".PPS BoundCbo()"

'   Bind the control:
    Set mCbo = lCbo

'   Bind the control's parent:
    Set mParent = lCbo.Parent

'   Sink the parent's events:
    mParent.OnClose = "[Event Procedure]"
    
    Debug.Print mstrName & ".PPS BoundCbo()" & vbTab & mCbo.Name
    
End Property

Private Sub mParent_Close()
    Debug.Print mstrName & ".mParent_Close()"
    
'   Goldilocks:
    Set mCbo = Nothing
    
End Sub

'End wecCboTestSecondary