Same Sub for several Events ???? (1 Viewer)

[ksor]

I have a form with 15-20 textboxes showing the name of a person.


I want to be able to RIGHT-click on one of them to show more detailed infos of the person behind the name.


The way I show the details is working nicely BUT it don't look right to have the EXACT same code for each of the MouseDown events for all the textboxes:


Here is ONE example for a textbox named txtFarfarfar:

Private Sub txtFarfarfar_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If ErAngivet(Controls(ActiveControl.Name).Tag) Then MouseDown Controls(ActiveControl.Name).Tag, Button, Shift, X, Y
End Sub



I have this code for EACH of the 15-20 textboxes where I cal my OWN MouseDown event where the showing of the details in another form happens.


IF IT HAD BEEN C# I just would have called my OWN mouseDown for every textbox - is there a better way ?

 

[Ranman256]

cant you just put a button, click it, and show the detail subform?

subform.visible = true

 

[theDBguy]

Hi. You can certainly create a single routine to perform the dirty work, but you'll still have to wire it to all the controls you want to initiate the process. I can't believe it would be any different with C#. If you want to automate the "wiring" process, you can create a class and then initialize the form controls on form load to "auto-wire" itself. But even then, I believe it still requires prior setup, which doesn't eliminate touching each and every control you want to automate for an event procedure.

 

[isladogs]

Do you really need around 20 separate textboxes?
Suggest replace them all with a single listbox or combo box.
User selects a name to open the details form.

 

[ksor]

Hi. You can certainly create a single routine to perform the dirty work, but you'll still have to wire it to all the controls you want to initiate the process. I can't believe it would be any different with C#. If you want to automate the "wiring" process, you can create a class and then initialize the form controls on form load to "auto-wire" itself. But even then, I believe it still requires prior setup, which doesn't eliminate touching each and every control you want to automate for an event procedure.

The problem in Access is you ONLY can choose ONE event method when you click the MouseDown event combobox - in C# you can choose all of you methods :banghead:

 

[ksor]

cant you just put a button, click it, and show the detail subform?

subform.visible = true

The right-click must be "wired" to the specific textbox because each of the textboxes had the PersonID in the Tag field ... and it's not a fluide process if you have to click a button too

 

[Uncle Gizmo]

Ah! Me bad! I didn't read this post either! I see you've already done it! Post deleted and hang my head in shame!

 

[theDBguy]

Here's an article on wiring event procedures. Just in case anyone is interested.


Cheers!

 

[Uncle Gizmo]

Hi Uncle Gizmo,

No sure exactly what you're trying to say but this is the very definition of "wiring" an event I was talking about. Have a look at this blog article to make sure we're on the same page.

Cheers!

Yes, I see! I got the wrong end of the stick, my apologies to all.

 

[theDBguy]

The problem in Access is you ONLY can choose ONE event method when you click the MouseDown event combobox - in C# you can choose all of you methods :banghead:

Yes; but in VBA, and probably C# as well, there's nothing stopping you from calling other events from the one just fired.

 

[theDBguy]

Yes, I see! I got the wrong end of the stick, my apologies to all.

Hi Uncle Gizmo,

Sorry for the confusion. I edited my post, so it probably added more to the mix up.

 

[MajP]

IF IT HAD BEEN C# I just would have called my OWN mouseDown for every textbox - is there a better way ?

In general yes, but not for your exact example. In vba an event procedure can only trap a single event not like you can do in C# or .net

Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click, Button2.Click, Button3.Click, Text5.click  
'Event-handler code here.  
End Sub

However in VBA you can have a function handle many events. Unfortunately for your example this will not work, because of the parameters. But in general write a function (has to be a function).

Private Function CommonClick()
  Dim ctrl As Access.Control
  Set ctrl = Me.ActiveControl
  MsgBox "I was clicked by " & ctrl.Name
End Function

Now in design view select all your textboxes. In the onclick event type in =CommonClick().
That function will handle all the click events for the controls.

 

[ksor]

In general yes, but not for your exact example. In vba an event procedure can only trap a single event not like you can do in C# or .net

Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click, Button2.Click, Button3.Click, Text5.click  
'Event-handler code here.  
End Sub

However in VBA you can have a function handle many events. Unfortunately for your example this will not work, because of the parameters. But in general write a function (has to be a function).

Private Function CommonClick()
  Dim ctrl As Access.Control
  Set ctrl = Me.ActiveControl
  MsgBox "I was clicked by " & ctrl.Name
End Function

Now in design view select all your textboxes. In the onclick event type in =CommonClick().
That function will handle all the click events for the controls.

This is ecactly my MouseDown method, I think !


I'll right away tr it out !


THX !!!!!!!!!!

 

[MajP]

This is ecactly my MouseDown method, I think !

Unfortunately I do not think so. You will not be able to get the buttons and shift. I can get the X, Y though

#If VBA7 Or Win64 Then
  Public Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
#Else
  Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
#End If

Public Type POINTAPI
  X As Long
  Y As Long
End Type
Public Function GetX() As Long
  Dim n As POINTAPI
  GetCursorPos n
  GetX = n.X
End Function
Public Function GetY() As Long
  Dim n As POINTAPI
  GetCursorPos n
  GetY = n.Y
End Function

Private Function CommonClick()
  Dim ctrl As Access.Control
  Dim X As Long
  Dim Y As Long
  
  Set ctrl = Me.ActiveControl
  X = GetX
  Y = GetY
  MsgBox "I was clicked by " & ctrl.Name & " X " & X & " Y " & Y

End Function

 

[isladogs]

ksor
I suggested a really simple alternative to avoid all this code back in post #4.
Whilst this is an interesting intellectual challenge, I'd be interested to know why you can't just do what I suggested.

 

[MajP]

On the continuing academic exercise, I wrote this code a long time ago, but have never really used. If you wanted to trap the mouse down and get the arguments you could use these classes.

Class commonControl

Option Compare Database
Option Explicit

'Class Module Name: CommonControl
'Developed by: MajP
'
'Purpose: This Class Module along with the CommonControls collection allows you to build a
'pseudo control array that will react to one or more events.  I only demonstrated using
'Text boxes, List boxes, Combo boxes, and Labels.  I only demonstrated the OnClick Event and
'the BeforeUpdate event. You can easily add more controls and events by following the code.

'How to use:
'1. Place this code in a CLASS (NOT A STANDARD MODULE) module named "CommonControl"
'2. You can use this code without the custom collection, but it has little utility
'3. Read the instructions for the CommonControls custom collection and place the
'CommonControls class in a CLASS module called "CommonControls"
'4. Place your common event procedures in a standard module or modify the event procedures within
'below code
'5. The example below is one way to use the class. For the controls you want to react to events
'place a ? mark in the tag propery. Do not enclose in parentheses.  Then on the form
'
'************************ Form Code Start *****************************************************
'Option Compare Database
'Option Explicit
'Dim ccControls As New CommonControls
'Private Sub Form_Load()
'  On Error Resume Next
'  Dim ctl As Access.Control
'  For Each ctl In Me.Controls
'    If ctl.ctlType = acTextBox Or ctl.ctlType = acLabel Or ctl.ctlType = acListBox Or ctl.ctlType = acComboBox Then
'      If ctl.Tag = "?" Then
'        ccControls.Add ctl, ctl.Name
'      End If
'    End If
'  Next ctl
'End Sub
'************************ Form Code End *******************************************************
'
'************************ Class Code Start ****************************************************

Private WithEvents mLabel As Access.Label
Private WithEvents mTextBox As Access.TextBox
Private WithEvents mlistBox As Access.ListBox
Private WithEvents mComboBox As Access.ComboBox
Private WithEvents mcheckBox As Access.CheckBox
Private mControl As Access.Control

Private mName As String
Public Property Get CommonControl() As Access.Control
  Set CommonControl = mControl
End Property
Public Property Set CommonControl(ByVal ctlControl As Access.Control)
  On Error GoTo ErrHandler
  Set mControl = ctlControl
  'More Events and more controls could be added here
  Select Case ctlControl.ControlType
     Case acLabel
     Set mLabel = ctlControl
     mLabel.OnClick = "[Event Procedure]"
    Case acTextBox
     Set mTextBox = ctlControl
     mTextBox.OnClick = "[Event Procedure]"
     mTextBox.BeforeUpdate = "[Event Procedure]"
     mTextBox.OnChange = "[Event Procedure]"
     mTextBox.OnGotFocus = "[Event Procedure]"
     mTextBox.OnLostFocus = "[Event Procedure]"
     mTextBox.OnMouseDown = "[Event Procedure]"
    Case acListBox
     Set mlistBox = ctlControl
     mlistBox.OnClick = "[Event Procedure]"
     mlistBox.BeforeUpdate = "[Event Procedure]"
    Case acComboBox
     Set mComboBox = ctlControl
     mComboBox.OnClick = "[Event Procedure]"
     mComboBox.BeforeUpdate = "[Event Procedure]"
  End Select
  Exit Property
ErrHandler:
   'Not sure why 459 (does not support events"
   'or 91 (object not set) errors are thrown. I think it has to do
   'with using a generic Access.Control object
   If Not (Err.Number = 459 Or Err.Number = 91) Then
      MsgBox ("Error: " & Err.Number _
            & " " & Err.Description _
            & " " & Err.Source)
   End If
   Resume Next
End Property

Private Sub mTextBox_Click()
  Call commonClickProcedure(mTextBox)
End Sub
Private Sub mTextBox_BeforeUpdate(Cancel As Integer)
  Call commonBU_Procedure(mTextBox)
End Sub

Private Sub mComboBox_Click()
  Call commonClickProcedure(mComboBox)
End Sub

Private Sub mComboBox_BeforeUpdate(Cancel As Integer)
  Call commonBU_Procedure(mComboBox)
End Sub

Private Sub mLabel_Click()
  Call commonClickProcedure(mLabel)
End Sub
Private Sub mListBox_Click()
  Call commonClickProcedure(mlistBox)
End Sub
Private Sub mlistBox_BeforeUpdate(Cancel As Integer)
  Call commonBU_Procedure(mlistBox)
End Sub

Public Property Get Name() As String
  Name = mName
End Property

Public Property Let Name(ByVal strName As String)
  mName = strName
End Property
Private Sub mTextBox_MouseDown(Button As Integer, shift As Integer, x As Single, y As Single)
  Call CommonMD_Procedure(mTextBox, Button, shift, x, y)
End Sub

'*********************************** Class Code End ********************************************
Private Sub mTextBox_GotFocus()
  Call commonGF_Procedure(mTextBox)
End Sub

Class CommonControls

Option Compare Database
Option Explicit

'Class Module Name: CommonControls
'Developed by: MajP
'
'Purpose: This Class Module is the collection class for the object class "CommonControls"
'The collection allows you to build a pseudo control array that will react to one or more events.

'How to use:
'1. Place this code in a CLASS (NOT A STANDARD MODULE) module named "CommonControls"
'2. Read the instructions for the CommonControl class
'
'************************ Class Code Start ****************************************************

Private mCommonControls As New Collection
Public Function Add(ctlControl As Access.Control, ctlName As String) As CommonControl
   Dim newCommonControl As CommonControl
   Set newCommonControl = New CommonControl
   Set newCommonControl.CommonControl = ctlControl
   newCommonControl.Name = ctlName
   mCommonControls.Add Item:=newCommonControl, Key:=ctlName
   Set Add = newCommonControl
End Function
Public Property Get count() As Integer
   count = mCommonControls.count
End Property
Public Property Get Item(ByVal index As Variant) As CommonControl
   Set Item = mCommonControls(index)
End Property
Public Sub Remove(index As Variant)
   mCommonControls.Remove (index)
End Sub

Private Sub Class_Initialize()
  'MsgBox "class Intialized"
End Sub

Private Sub Class_Terminate()
 Set mCommonControls = Nothing
End Sub
Public Sub Clear()
    Set mCommonControls = New Collection
End Sub

In a standard module put in your common events

Public Function commonClickProcedure(ctl As Access.Control)
  'put your click event here
  MsgBox "Click " & ctl.Name
End Function
Public Function commonBU_Procedure(ctl As Access.Control)
  'put your before update event here
  MsgBox "Before Update " & ctl.Name & " " & ctl.Value
End Function
Public Function commonGF_Procedure(ctl As Access.Control)
  MsgBox "Got Focus " & ctl.Name & " " & ctl.Value
End Function

Public Sub CommonMD_Procedure(ctl As Access.Control, Button As Integer, shift As Integer, x As Single, y As Single)
  MsgBox ctl.Name & " Button " & Button & " Shift " & shift & " X " & x & " Y " & y
End Sub

Now on your form load the controls into the commonccontrols object

Private Sub Form_Load()
  On Error Resume Next
  Dim ctl As Access.Control
  For Each ctl In Me.Controls
    If ctl.ctlType = acTextBox Or ctl.ctlType = acLabel Or ctl.ctlType = acListBox Or ctl.ctlType = acComboBox Then
      If ctl.Tag = "?" Then
        ccControls.Add ctl, ctl.Name
      End If
    End If
  Next ctl
End Sub

Now you can trap all of the mousedown events.

 

[MajP]

The code in the form should look something like this. Need the variable for the commoncontrol object

Option Compare Database
Option Explicit
Public ccControls As New CommonControls
Private Sub Form_Load()
  On Error Resume Next
  Dim ctl As Access.Control
  For Each ctl In Me.Controls
    If ctl.ctlType = acTextBox Or ctl.ctlType = acLabel Or ctl.ctlType = acListBox Or ctl.ctlType = acComboBox Then
      If ctl.Tag = "?" Then
        ccControls.Add ctl, ctl.Name
      End If
    End If
  Next ctl
End Sub

 

[MajP]

In this demo there are 26 controls and they are acting on 5 events. This would normally require about 100 event procedures. There is 0.

 

[ksor]

ksor
I suggested a really simple alternative to avoid all this code back in post #4.
Whilst this is an interesting intellectual challenge, I'd be interested to know why you can't just do what I suggested.

Because it's a tree-like structure of text boxes showing a person and his ancestors.

 

[ksor]

In this demo there are 26 controls and they are acting on 5 events. This would normally require about 100 event procedures. There is 0.

THX for all the code and your time !


I'll now try to see if I can implement it in my form