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.
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.
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.
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.
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.
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:
Code:
Option Compare Database
Option Explicit
Function getFormAndControl()
Debug.Print Screen.ActiveControl.Parent.Name, Screen.ActiveControl.Name
End Function
And use like
Code:
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.
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.
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.
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?
Retouching the code of the class module that I had in my databases with what is proposed here, it would look like this:
Code:
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.
Attachments
Detectar cambios entre formulario principal y subformulario.accdb
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?