How do I reference / point towards a Field in another Form / SubForm? (1 Viewer)

[Cark]

I have the attached database which we can use as an example of what I am trying to achieve.

I have a main form called FrmOverview which has a TabControl item inside of it which has 2 Tabs - General Information and Reliability.

When a user creates a new record, the user has to input details in the General Information Tab and then the user moves onto the Reliability Tab to fill in data on that Tab.

I am working towards using a red/green rectangle to identify whether anything has been inputted into the Reliability Tab (currently triggered by Reliability PartID matching the General Information PartID (identified with the SubCoverInformation) using the following code on the FrmOverview OnCurrent Event and TabCtl12 OnChange Event:

Private Sub Form_Current()

        If Nz(Me.FrmSubReliability.Form!PartID, 0) = _
           Nz(Me.FrmSubCoverInformation.Form!PartID, 0) And _
           Trim(Me.FrmSubReliability.Form!Title & "") <> vbNullString _
        Then
            Me.Form!ReliabilityProgrammePresent = -1
            Me.Form!ReliabilityProgress.BackColor = RGB(33, 255, 44)
        Else
            Me.Form!ReliabilityProgrammePresent = -0
            Me.Form!ReliabilityProgress.BackColor = RGB(255, 43, 43)
        End If

End Sub

Private Sub TabCtl12_Change()

        If Nz(Me.FrmSubReliability.Form!PartID, 0) = _
           Nz(Me.FrmSubCoverInformation.Form!PartID, 0) And _
           Trim(Me.FrmSubReliability.Form!Title & "") <> vbNullString _
        Then
            Me.Form!ReliabilityProgrammePresent = -1
            Me.Form!ReliabilityProgress.BackColor = RGB(33, 255, 44)
        Else
            Me.Form!ReliabilityProgrammePresent = -0
            Me.Form!ReliabilityProgress.BackColor = RGB(255, 43, 43)
        End If

End Sub

However for my code to work properly, I need to have another bit of code in the FrmSubReliabilityProgramme Form which does a similar thing, but references the Me.FrmSubReliability.Form!PartID and Me.FrmSubCoverInformation.Form!PartID portions in a different way so that it works.

What modifications do I need to make to allow this to work in the FrmSubReliabilityProgramme OnCurrent Event?

 

[The_Doc_Man]

This article should help.

https://support.office.com/en-us/ar...her-form-59d593fb-13a7-4058-b8b6-6b24b5b14400

There is a part in there that people tend to get wrong. I'll extract that reference and high-light the part that folks often miss.

=[Forms]![main form name]![name of the subform control on the main form].[Form]![control name on the subform]

People want to touch the form by its name, but in the case of the sub-form, you want to reference the form through the subform control that brings it into the main form. You don't (in this case) CARE what the sub-forms's actual name happens to be.

 

[Cark]

So for my example would the below in the Form Current Event for FrmSubReliabilityProgramme work?

Private Sub Form_Current()

        If Nz(Forms!FrmOverview!FrmSubReliability.Form!PartID, 0) = _
           Nz(Forms!FrmOverview!FrmSubCoverInformation.Form!PartID, 0) And _
           Trim(Forms!FrmOverview!FrmSubReliability.Form!Title & "") <> vbNullString _
        Then
            Forms!FrmOverview!ReliabilityProgrammePresent = -1
            Forms!FrmOverview!ReliabilityProgress.BackColor = RGB(33, 255, 44)
        Else
            Forms!FrmOverview!ReliabilityProgrammePresent = -0
            Forms!FrmOverview!ReliabilityProgress.BackColor = RGB(255, 43, 43)
        End If

End Sub

Because it looks as though it is working

 

[The_Doc_Man]

Yes, should be OK - but you can save yourself some typing in this specific case: If this reference is being made from the main form then you could also do this:

Instead of Forms!FrmOverview!ReliabilityProgrammePresent = -1 use Me.ReliabilityProgrammePresent = -1. Instead of Forms!FrmOverview!FrmSubCoverInformation.Form!PartID, use Me.FrmSubCoverInformation.Form!PartID.

Less typing and lets you use the Me. construct, which makes for better IntelliSense results. Also, in the (admittedly rare) case that you had the current form open twice in the same session, this would prevent the code from picking the wrong instance of the form. The Me. construct ALWAYS points to the current form or report regardless of whether more than one copy is open in the same session. More precisely, it always points within the Class module of the form where it is being used.

Note that Me. does NOT work when issued from within general modules. It is a Class module concept.

 

[Cark]

Noted. In this particular instance that I am looking at, it is on the FrmSubReliability so I need to use the long format.

Also after some further troubleshooting/testing, it doesn't look as though my earlier code is working... I have been testing it on the On Current Event, After Update Event and Before Update Event and none of them have worked... Any idea which Event I should use? I want the bar underneath Reliability to go green once I have entered something into the Title Text Box and pressed enter to confirm the entry.

 

[The_Doc_Man]

Which event you use would not affect the required format of how you make the reference, but the location of the code would be huge.

What I showed you would be based on referring to the field on the sub-form from code in the class module of the main form. If you are not going in that direction, then the problem becomes more complex. Before we go far afield here, please clarify where this code is running: In the main form's class code or in the sub form's class code. Because they DO NOT OVERLAP.

As to the other implied question, you have a problem in that any events that fire for your SubReliability sib-form are only visible in the class module of the sub-form. I.e. events are local to the thing where they occur. So when you change the contents of Title (which is in the sub-form) there are no events in the main form for that change. Fortunately, the word "Title" isn't a keyword, so at least that isn't an issue.

You might need to alter your way of thinking about how you want to trigger this event.

 

[Cark]

Yes this code is running in the Sub Form Class Code for FrmSubReliability.

Private Sub Form_Current()

        If Nz(Forms!FrmOverview!FrmSubReliability.Form!PartID, 0) = _
           Nz(Forms!FrmOverview!FrmSubCoverInformation.Form!PartID, 0) And _
           Trim(Forms!FrmOverview!FrmSubReliability.Form!Title & "") <> vbNullString _
        Then
            Forms!FrmOverview!ReliabilityProgrammePresent = -1
            Forms!FrmOverview!ReliabilityProgress.BackColor = RGB(33, 255, 44)
        Else
            Forms!FrmOverview!ReliabilityProgrammePresent = -0
            Forms!FrmOverview!ReliabilityProgress.BackColor = RGB(255, 43, 43)
        End If

End Sub

The goal is to get the object ReliabilityProgrammePresent, in the FrmOverview Form, to change when the Text Box Title is changed in FrmSubReliability which is the Sub Form.

Maybe adding it to an Event directly on the Text Box Title is the best way of going about it? I am just cautious if I end up having 10 or so Text Boxes on FrmSubReliability.

 

[The_Doc_Man]

If the event is in the sub-form, there are ways to handle signals to other contexts, usually involving setting a flag in a public area in a general module or using a TempVars flag method, but if you are willing to play with a direct "touch" then consider this approach as the simplest of the lot.

If you have a parent/child relationship, you can exploit the sub-form's .Parent property to find a control on the parent. See, for example, this article.

https://docs.microsoft.com/en-us/office/vba/api/access.subform.parent

While syntactically it is correct, using Forms!xxxx!xxxxx (in place of Me. or other ways of pointing to the right place) is a lot of work.

In the sub-form event code, Me points to the sub-form context. Me.Parent typically points to the form that is the parent of the thing referenced by Me, and that .Parent link will usually be a form. (But for reports, it can be a report.) Read the article carefully first because depending on what you reference, the .Parent link could SOMETIMES reference something that isn't a form, depending on the structure of the parent form. I'm not going to swear on this one, but if the sub-form control is in a tab control, you might get the tab.

EDIT: I see I got distracted before I finished this. My dear wife had a sudden chore.

If you handle the .LostFocus or .AfterUpdate event in the sub-form, setting a public flag isn't enough. It also requires you to handle a second event in the parent form, such as would happen if you did a .SetFocus to a control in the parent form (leading to a control.GotFocus and a form.Enter). Going directly via the .Parent might be closer to what you want here because other than doing a one-time verification that it points correctly, Me.Parent is easy to understand and to manage, and is NEVER confused about where it points.