Pass both grandparent and parent to a procedure

Question

I am using MS Access 365 and want to pass the grandparent and parent form name as string or a form to a procedure where i convert it to a form. Right now it doesn't find the form because the sending forms are within a tabbed form called frmSetup. I don't bind my forms so everything is through vba. One of the procedures I want to send to goes through each control on the from and clears the form fields. I have no problem getting the grandparent and parent names, but I can't figure out how to send it to the procedure so that the form would be found.

*Calling through Sub form*

Set sfrm = Me.Parent
sParent = sfrm.Name
sGrandParent = sfrm.Parent.Name
Dim sReference As String ' using to build name when form is enclosed with tab control

'=[Parent]![SomeControlOnParentForm]
'Forms!frmMainMenu!subFrm.Form!subFrm.Form!chkImport.Visible = True
'sGrandParent = Me.Parent.Parent.Name

Dim rs As New ADODB.Recordset
Dim cn As ADODB.Connection

sReference = "Forms!"
sReference = sReference & sGrandParent & "."
sReference = sReference & sParent
ClearParentForm (sReference)
If Not IsNull(Me.txtSubEmpid.Value) Then

The procedure is...

Public Sub ClearParentForm(ByVal sFormName As String)
On Error GoTo ErrorMessage

Dim sParent As String
Dim cnt As Control
Dim sfrm As Access.Form
Set sfrm = Forms(sFormName)
sParent = sfrm.Name

For Each cnt In sfrm.Controls

    If Not TypeOf cnt Is Label Then
        
        sName = cnt.Name
        If TypeOf cnt Is ComboBox Then
            For i = 0 To cnt.ListCount - 1
                
                    cnt.Selected(i) = False
                    Exit For
                
                i = i + 1
            Next i
            
        ElseIf TypeOf cnt Is TextBox Then
        
            If IsNumeric(cnt) = True Then
            On Error Resume Next
                cnt.Value = 0
            Else
                cnt.Value = ""
            End If
           
        End If
        
    End If
 Next cnt

ErrorMessage:
    Exit Sub
End Sub

Basically I need to send over frmSetup.frmEmployee to the procedure.
I hope this makes since.

I've tried passing the form name as a form, I tried concatenating the forms using Forms! I haven't used Access in about 20 years and up until now it's felt kind of like riding a bike, except this time I fell off. Just don't know or can find what I'm missing.

Answer 1

Your question is a classic example of why I don't use the object names access provides. Access automatically creates them and changes them and they are long and difficult to look up. Further the relative references are a pain as you will find from the link in my comment. Instead use state/ a view model or whatever else you want to call the following procedure:

Add a code module to your database. Unlike the code behind where you write events for each form or report, code modules have global scope by default. You can refer to the public functions and variables in the code module from anywhere and you even get Intellisense support. You can even use these public functions and variables in the expression and query designers just like built in functions though you do have to navigate to the code module functions.

So, to start just make a public variable for each control of interest for each form. then set each unbound form or report control to the appropriate public variable. unbound here means not tied to some table or query field and probably in the header or footer. Usually, you will find you only have a few pieces of state you really need rather than one for every control.

For example, for FormA ControlA add a public variable to the code module called FormAControlA. set ControlA's ControlSource to FormAControlA. In your forms buttonClick and afterUpdate events if you wanted to clear ControlA just set FormAControlA to "" or whatever.

Obviously, to clear FormA on a button click, the button would just call a clear function. the clear function just sets all of FormA's relevant public variables to "".

The next step is to replace the public variables with properties. For instance, add the public functions GetFormAControlA and SetFormAControlA to the control module. Then make FormAControlA private. If you are unfamiliar with the benefits of Properties vs Global Variables you can look that up.

The next step is persisting the state when you close your database by storing the hopefully now private FormAControlA in some settings table. I usually only load the data when I open the database and save the data when I close the database, but it is unlikely that you will notice the difference if you just save and get FormAControlA from a table every time.

To summarize and elaborate I find that most databases have a set of business rules and corresponding state that is best modeled and added to the database using VBA. At that point it is usually best to bind your display's such as forms and reports to the state such as in more modern programming paradigms like MVC and MVVM. Binding the forms and reports to the State helps power up your Access by taking full advantage of its integration with both VBA and SQl. It also happens to fix problems with passing parameters around between forms and reports.