Solved Detect the change of a control from the main form to the subform, and vice versa

[zelarra821]

Hello.

I would like to detect using VBA when I change a control:

• From subform to main form.
• From main form to subform.

I have tried events from one and the other and I can't find the key.

I have made this small example, to which I ask you not to try to understand the logic of the name of the fields and the values entered, please. I just want to show the design that interests me.

Thank you so much.

 

[CJ_London]

not clear what you mean by 'change a control'. You mean you change a value? or move from one control to another? something else?

There are no events on your form so zero idea what you are trying to achieve

After 700+ posts, you should be able to formulate a question that someone can answer

basic code to reference the other form

to reference a control on the main form from a subform - parent.nameofcontrol

to reference a control on a subform from the main form subformcontrolname.form.nameofcontrol

 

[zelarra821]

Ok. Sorry. I want a msgbpx when I move from a main form control to another control of the subform, and another msgbox from a control of the subform to another control of the main form.

There are no more events because I don't know which one I have to use.

Thanks.

 

[CJ_London]

and what is the msgbox about? each way?

And is this from any control to any control, or from specified controls

Be clear about what you want

best gues at the monent is to use the subform control events

Private Sub TChildTable_Enter()

    MsgBox "Leaving main form"

End Sub


Private Sub TChildTable_Exit(Cancel As Integer)

    MsgBox "Going to main form"

End Sub

 

[zelarra821]

A msgbox that says you have moved to..., each way.

And it doesn't matter the control

I am not able to suppose that questions to answer in my first post. Sorry

 

[CJ_London]

I edited my post with a simple example

 

[zelarra821]

Ok, thanks. Solved.

 

[Pat Hartman]

This is going to really annoy the users.

 

[CJ_London]

I agree

 

[zelarra821]

Hello. I have adapted the code to a class module, as I need this for a more complex module.

Now, in this case that I am attaching, I have added a second subform.

I can tell if it goes from one subform to another using a MsgBox and the ActiveControl property.

However, I need to know that the user has clicked on any element of the main form (a control or a section such as the header or detail).

If the user clicks on the header or Detail of the main form, there is an "On Click" event on the Header and Detail of the main form. On the contrary, if you click on any control, such as a TextBox or a Combo, I have no way of knowing that you have clicked there.

Thank you so much.

 

[CJ_London]

Really difficult to provide relevant advice without knowing the objective and I don’t want to get dragged into a guessing game as to the purpose

so one way is to have the same code in every relevant control’s click event (or perhaps enter or got focus events)

There are ways to cut down on the amount of code - using a public function or a class for example but that very much depends on objective and purpose

 

[zelarra821]

Hello. Thanks for answering. I wanted to show a summarized example of what I want to do, because if I show you the entire code, it is more than likely that you will not understand what I want to do. My goal is to activate and deactivate the scroll bars and change the ribbon depending on whether the active control is a Textbox with rich text. Thus, I have created a class module that now works from the main form to the subform, or the subforms that are in the form. The way to do this is by declaring a class for each of the subforms in the main form. Then this afternoon I'll give you what I've done.

 

[Josef P.]

Then why do you need to know if a control has been changed?
Isn't it enough to "teach" the textboxes with richt text to change the scrollbar and the ribbon?

Simple example to get infos from selected controls at one place see attached file.

BTW: "database.accdb" is a 'great' file name - there you know immediately what is to be seen in it.

 

[Edgar_]

You could use

Option Compare Database
Option Explicit

Public WithEvents mForm As Form
Private WithEvents mDetalle As Section

Public Sub Inicializar(frm As Form)
    Set mForm = frm
    Set mDetalle = mForm.Section(0)
   
    mDetalle.OnClick = "[Event Procedure]"


    Dim ctl As Control
    For Each ctl In mForm.Controls
        If TypeName(ctl) = "TextBox" Then
            ctl.OnClick = "=getFormAndControl()"
        End If
    Next ctl
End Sub

Private Sub mDetalle_Click()
    Debug.Print "Detalle", mDetalle.Parent.Name
End Sub

And have a module like:

Option Compare Database
Option Explicit

Function getFormAndControl()
    Debug.Print Screen.ActiveControl.Parent.Name, Screen.ActiveControl.Name
End Function

And use like

Option Compare Database
Option Explicit

Public MyFormDetector1 As New clsFormDetector
Public MyFormDetector2 As New clsFormDetector
Public MyFormDetector3 As New clsFormDetector

Private Sub Form_Load()

    MyFormDetector1.Inicializar Me
    MyFormDetector2.Inicializar Me.TChildTable.Form
    MyFormDetector3.Inicializar Me.FChildForm2.Form
End Sub

You can get creative and detect if a textbox is using rich text or whatever.

 

[Josef P.]

Isn't it enough to "teach" the textboxes with richt text to change the scrollbar and the ribbon?

I looked at this a little more closely and now understand (at least I think I do) why it doesn't work with just the textbox alone: When you move from one subform to another, the control in the 1st subformualr doesn't lose focus. => you also have to react on SubForm.Exit.

Attached is an example with an event collector. Maybe this matches.

 

[zelarra821]

Edgar, I stick with your example because of the simplicity of the code.

I have a question.

Is there a way to integrate the getFormAndControl function into the class module itself?

I'm trying to get it to work with the On Click and On Exit Textbox event instead of using that function, but I can't. You can help?

Thank you so much.

 

[Edgar_]

It has to be a module for this workflow. Since we're using classes, you'd have to refer to the class instance here: ctl.OnClick = "=getFormAndControl()" like ctl.OnClick = "=[Form].MyFormDetector1.getFormAndControl()"
But I don't know the syntax or whether that is even possible, even though the debugger shows the instances there, I don't know how to refer to them from the expression.

You could also do something like ctl.OnClick = "=MsgBox(Screen.ActiveControl.Name)", but I don't know what good that would do. I'd suggest using the module. Or maybe someone here knows how to use that expression better than I do.

 

[Josef P.]

Is there a way to integrate the getFormAndControl function into the class module itself?

From your code:

For Each ctrl In mForm.Controls
        If TypeName(ctrl) = "TextBox" Then
            Set mTextbox = ctrl
            
            mTextbox.OnClick = "=getFormAndControl()"
            mTextbox.OnExit = "[Event Procedure]"
                
        End If
    Next ctrl

[...]
Private Sub mTextbox_Click()
    Debug.Print Screen.ActiveControl.Parent.Name, Screen.ActiveControl.Name & " Enter"
End Sub

Private Sub mTextbox_Exit(Cancel As Integer)
    Debug.Print Screen.ActiveControl.Parent.Name, Screen.ActiveControl.Name & " Exit"
End Sub

mTextbox_Click: will never run because OnClick is set to call the function and not "[Event Procedure]".
mTextbox.OnClick = "=getFormAndControl()": This variant works only with global functions or functions in the form class.

mTextbox.OnExit = "[Event Procedure]" sets [Event Procedure] in each textbox, but mTextbox_Exit will only respond to the textbox stored in mTextbox.

Why do you want to integrate getFormAndControl into the class?

Don't design classes randomly , but think about the expected behavior first.
The Debug.Print lines are nice to try out, but what do you want to replace them with?

 

[zelarra821]

Retouching the code of the class module that I had in my databases with what is proposed here, it would look like this:

Option Compare Database
Option Explicit

Private WithEvents mForm As Form
Private WithEvents mTextbox As Access.TextBox
Private WithEvents mHeader As Access.Section
Private WithEvents mDetail As Access.Section

Public Sub Inicializar(frm As Form)
    Set mForm = frm
    
    mForm.MouseWheel = "[Event Procedure]"
    
    Set mHeader = mForm.Section(acHeader)
    Set mDetail = mForm.Section(acDetail)
    
    mHeader.OnClick = "[Event Procedure]"
    mDetail.OnClick = "[Event Procedure]"

    Dim ctrl As Control
    For Each ctrl In mForm.Controls
        If TypeName(ctrl) = "TextBox" Then
            Set mTextbox = ctrl
            
            mTextbox.OnEnter = "[Event Procedure]"
            mTextbox.OnExit = "[Event Procedure]"
        End If
    Next ctrl
End Sub

Private Sub mForm_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
    If mForm.ScrollBars = 0 Then
        Dim i As Long
        If mForm.ActiveControl.ControlType = acTextBox Then
            For i = 1 To Abs(Count)
                SendMessage GetFocus, WM_VSCROLL, IIf(Count < 0, SB_LINEUP, SB_LINEDOWN), 0&
            Next
        End If
    End If
End Sub

Private Sub mHeader_Click()
    If Not mForm.ScrollBars = 2 Then
        mForm.ScrollBars = 2
        mForm.RibbonName = "Database"
    End If
End Sub

Private Sub mDetail_Click()
    If Not mForm.ScrollBars = 2 Then
        mForm.ScrollBars = 2
        mForm.RibbonName = "Database"
    End If
End Sub

Private Sub mTextbox_Enter()
    If mTextbox.TextFormat = acTextFormatHTMLRichText Then
        mForm.ScrollBars = 0
        Dim txt As TextBox
        Set txt = Screen.ActiveControl
        If txt.TextFormat = acTextFormatHTMLRichText Then
            If Not mForm.ActiveControl.Parent.Name = mForm.Name Then
                mForm.Parent.RibbonName = "RichText"
            End If
            mForm.RibbonName = "RichText"
        End If
    End If
End Sub

Private Sub mTextbox_Exit(Cancel As Integer)
    If mTextbox.TextFormat = acTextFormatHTMLRichText Then
        mForm.ScrollBars = 0
        Dim txt As TextBox
        Set txt = Screen.ActiveControl
        If txt.TextFormat = acTextFormatHTMLRichText Then
            If Not mForm.ActiveControl.Parent.Name = mForm.Name Then
                mForm.Parent.RibbonName = "Database"
            End If
            mForm.RibbonName = "Database"
        End If
    End If
End Sub

This code works perfectly for my goal:

I want it, when entering a textbox with rich text, to hide the form's scroll bars so I can use the mouse in the textbox, and show the RichText ribbon. On exit, show the form scroll bars and show the Database ribbon. And this behavior is replicated by a single form, or this form has one or more subforms, regardless of the number of textboxes there are (there may be a form that does not have one, for example), in each of the textboxes of the forms. and subforms.

Except:

In the form that I attach to the example database (FPreciosAlmazara). I don't have the slightest idea why not there. I have verified that creating the textboxes again does work, but since I have to do this in each of the forms I have like this, it gives me something.

Thanks.

 

[Josef P.]

I'll come back to this code again:

Private WithEvents mTextbox As Access.TextBox

[...]

    For Each ctrl In mForm.Controls
        If TypeName(ctrl) = "TextBox" Then
            Set mTextbox = ctrl
        
            mTextbox.OnEnter = "[Event Procedure]"
            mTextbox.OnExit = "[Event Procedure]"
        End If
    Next ctrl

[...]

Private Sub mTextbox_Enter()
[...]
End Sub

Private Sub mTextbox_Exit(Cancel As Integer)
[...]
End Sub

Why do you go through all textboxes, although you can only react to the events of a single textbox in the class?