Automation 'constants'

[pdanes]

When I want an event to call a function rather than the standard 'Event Procedure', I use the syntax =MyFunction(Param1; Param2;...) in the box for that event. I sometimes want to pass my own constants, but sometimes I'd like to pass some information from the form, like =MyFunction(ActiveControl.Name). This works great, but I've had trouble with getting some information that I want. Is there a list of terms that can be specified in these calls? I'm not even sure what term to use when searching for this. When I try something like Button, in an attempt to pass which button the user clicked, I get a non-trappable error message saying that the 'Object does not contain the automation object Button.' Searching for Access automation objects did not help. I would like to know what all I can use here.

 

[arnelgp]

if you are using your MyFunction on the click event, you do not need to pass anything, just modify
your function to check if the "current control" is a Command Button, eg:

Public Function MyFunc()
    Dim ctl As Control
    On Error Resume Next
    Set ctl = Screen.ActiveControl
    If Err Then
        Exit Function
    End If
    If TypeOf ctl Is CommandButton Then
        Debug.Print "command button", ctl.Name
    End If
End Function

 

[moke123]

If I need info from the form I don't pass it in the function call, I use screen.activecontrol or screen.activeform in the function itself.

for instance If I want the value of the field Text2 I'll use something like

Private Function MyFunction()
MsgBox Screen.ActiveForm.Text2
End Function

or

Private Function MyFunction()
MsgBox Screen.ActiveControl.Parent.Text2
End Function

 

[pdanes]

if you are using your MyFunction on the click event, you do not need to pass anything, just modify
your function to check if the "current control" is a Command Button, eg:

Public Function MyFunc()
    Dim ctl As Control
    On Error Resume Next
    Set ctl = Screen.ActiveControl
    If Err Then
        Exit Function
    End If
    If TypeOf ctl Is CommandButton Then
        Debug.Print "command button", ctl.Name
    End If
End Function

Thank you, but ActiveControl.Name was just an example. I know I don't always NEED to pass a parameter - sometimes there are other ways to do it. But the point is, sometimes I WANT to pass a parameter, and I would like to know what all values are available to be passed. That was what I was asking.

 

[pdanes]

If I need info from the form I don't pass it in the function call, I use screen.activecontrol or screen.activeform in the function itself.

for instance If I want the value of the field Text2 I'll use something like

Private Function MyFunction()
MsgBox Screen.ActiveForm.Text2
End Function

or

Private Function MyFunction()
MsgBox Screen.ActiveControl.Parent.Text2
End Function

Yes, there are all sorts of other possibilities. Thank you for your response, but I am not looking for other ways to achieve this. I am specifically trying to find out what values are available that can used in the scenario I described. ActiveControl is clearly one. I would like to know all the others, and if there is some list to reference, or if those available have some categorical grouping with a name, like maybe 'Form event constants' or some such.

 

[pdanes]

To make clear what I am after in THIS specific case - I am capturing the KeyDown and KeyUp events. I am using a function, because I have a huge number of controls on the form - several hundred. (It's a matrix, so please don't give me a hard time about my form being too complicated - it's not.) I don't want a separate event procedure for every control and every key action, so I am doing it with a function in the event call box. But I would like to know which key was pressed. That is available in the KeyDown and KeyUp event procedures, but I have not found a way to pass it via a function. And there have been other cases of similar things I want to pass. Sometime I have found an Access term or expression that can be used, sometimes not, so I am looking for some authoritative list of such possibilities.

 

[The_Doc_Man]

That is available in the KeyDown and KeyUp event procedures, but I have not found a way to pass it via a function.

In the context of using the "=function(x,y)" method for event handling, you say you have not found a way to pass it via a function. Pass what thing via a function, and to where?

Can't say I've ever actually seen a list of what can and cannot be passed, because that list is available through the help web-pages one function at a time. I guess the "can't be passed" has to do with the environment to which this passage will occur. You can pass the name of a parameter to something that will stay in the same context and thus will be using the same symbol/routine library. But for example, if you are passing in a named symbol via an SQL string, remember that VBA and SQL are in different memory spaces. You might find that SQL cannot look up the name and VBA doesn't necessarily translate it if it was in quotes at the time, so you have to pre-translate it via concatenation as you build a dynamic SQL string.

 

[pdanes]

In the context of using the "=function(x,y)" method for event handling, you say you have not found a way to pass it via a function. Pass what thing via a function, and to where?

Can't say I've ever actually seen a list of what can and cannot be passed, because that list is available through the help web-pages one function at a time. I guess the "can't be passed" has to do with the environment to which this passage will occur. You can pass the name of a parameter to something that will stay in the same context and thus will be using the same symbol/routine library. But for example, if you are passing in a named symbol via an SQL string, remember that VBA and SQL are in different memory spaces. You might find that SQL cannot look up the name and VBA doesn't necessarily translate it if it was in quotes at the time, so you have to pre-translate it via concatenation as you build a dynamic SQL string.

"But I would like to know which key was pressed. That is available in the KeyDown and KeyUp event procedures, but I have not found a way to pass it via a function."

The event procedure has the parameter KeyCode, but that same parameter is not available to be passed via a function, or at least, not that I have been able to discover.

 

[MajP]

I do not know if there is a cleaner way but this is how I do it. Maybe someone can recommend a smarter way.

Build a custom class and custom collection class that holds your instantiated objects of your custom class. Lets call the class CommonControl and the collection class CommonControls

You can then trap any events (mouse down, key down, click, etc.) in CommonControl. What is tricky is that your CustomControl class has to have with events variables for all the type of controls because generic access control has no events. So you can pass into the class a "Control" and assign it to the correcct local with events variable based on the controltype.

Instantiate a with events variable of your custom collection and have the CustomControls raise custom events. This way you only need one variable to instantiate.

Put a tag in your controls that you want to add to the collection
Loop your controls and add to the custom collection. Provide each CommonContro a reference to the parent collection. (I think that violates principals of encapsulation, but so be it.)

Now when any of the controls that are part of the custom collection react to one of the events you added they call a method in the custom collection class to raise a custom event.

Sounds like a lot but not really if you done something like this before.
If interested I have an example of doing this.

 

[MajP]

Looking at my example I called it a ControlArrayItem and a ControlArray for the custom collection class.
May not be the smartest approach, but it does work.

I only added ability for Labels and Textboxes, but you can just duplicate the code for more control types.
I only added events for MouseDown, MouseMove, MouseUp, KeyPress, KeyDown, KeyUp. You can add more, but these are the ones that you cannot easily do with a function

Option Compare Database
Option Explicit

Private WithEvents m_TextBox As Access.TextBox
Private WithEvents m_Label As Access.Label
Private m_Control As Access.Control
Private m_ParentControlArray As ControlArray


Public Sub Initialize(Ctrl As Access.Control, ParentControlArray As ControlArray)
 ' On Error GoTo ErrHandler
  Set Me.Control = Ctrl
  Set Me.ParentControlArray = ParentControlArray
  'You can add More Events and more controls here
  'Here is the deal, a generic access control has no events you have to cast it to an actual control
 
  Select Case Ctrl.ControlType
     Case acLabel
       Set m_Label = Ctrl
       m_Label.OnClick = "[Event Procedure]"
       m_Label.OnMouseDown = "[Event Procedure]"
       m_Label.OnMouseMove = "[Event Procedure]"
       m_Label.OnMouseUp = "[Event Procedure]"
    Case acTextBox
      Set m_TextBox = Ctrl
      m_TextBox.OnClick = "[Event Procedure]"
      m_TextBox.OnMouseDown = "[Event Procedure]"
      m_TextBox.OnMouseMove = "[Event Procedure]"
      m_TextBox.OnMouseUp = "[Event Procedure]"
      m_TextBox.OnKeyUp = "[Event Procedure]"
      m_TextBox.OnKeyDown = "[Event Procedure]"
      m_TextBox.OnKeyPress = "[Event Procedure]"
    Case acListBox
      'Add code here for other controls and other events
    Case acComboBox
     End Select
  Exit Sub
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 Sub
Public Property Get Control() As Access.Control
    Set Control = m_Control
End Property
Public Property Set Control(ByVal objNewValue As Access.Control)
    Set m_Control = objNewValue
End Property

'This seems bizarre construct, but the item needs to have a reference to the collection it is in
Public Property Get ParentControlArray() As ControlArray
    Set ParentControlArray = m_ParentControlArray
End Property

Public Property Set ParentControlArray(ByVal objNewValue As ControlArray)
    Set m_ParentControlArray = objNewValue
End Property



'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Event Handler if Label ---------------------------------------------------------------------------------

Private Sub m_Label_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
 Me.ParentControlArray.CA_MouseDown Me.Control, Button, Shift, x, y
End Sub
Private Sub m_Label_MouseMove(Button As Integer, Shift As Integer, x As Single, y As Single)
 Me.ParentControlArray.CA_MouseMove Me.Control, Button, Shift, x, y
End Sub
Private Sub m_Label_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
  Me.ParentControlArray.CA_MouseUp Me.Control, Button, Shift, x, y
End Sub

'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Event Handler if TextBox ---------------------------------------------------------------------------------
Private Sub m_TextBox_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
 Me.ParentControlArray.CA_MouseDown Me.Control, Button, Shift, x, y
End Sub
Private Sub m_TextBox_MouseMove(Button As Integer, Shift As Integer, x As Single, y As Single)
 Me.ParentControlArray.CA_MouseMove Me.Control, Button, Shift, x, y
End Sub
Private Sub m_TextBox_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
  Me.ParentControlArray.CA_MouseUp Me.Control, Button, Shift, x, y
End Sub
Private Sub m_TextBox_KeyDown(KeyCode As Integer, Shift As Integer)
 Me.ParentControlArray.CA_KeyDown Me.Control, KeyCode, Shift
End Sub
Private Sub m_TextBox_KeyPress(KeyAscii As Integer)
   Me.ParentControlArray.CA_KeyPress Me.Control, KeyAscii
End Sub

Private Sub m_TextBox_KeyUp(KeyCode As Integer, Shift As Integer)
    Me.ParentControlArray.CA_KeyUp Me.Control, KeyCode, Shift
End Sub

'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Add Event Handlers Here for Other Controls ----------------------------------------------------------------

and the collection

Option Compare Database
Option Explicit

Private m_ControlArray As Collection
Public Event MouseDown(TheControl As Access.Control, Button As Integer, Shift As Integer, x As Single, y As Single)
Public Event MouseMove(TheControl As Access.Control, Button As Integer, Shift As Integer, x As Single, y As Single)
Public Event MouseUp(TheControl As Access.Control, Button As Integer, Shift As Integer, x As Single, y As Single)
Public Event KeyDown(TheControl As Access.Control, KeyCode As Integer, Shift As Integer)
Public Event KeyPress(TheControl As Access.Control, KeyAscii As Integer)
Public Event KeyUp(TheControl As Access.Control, KeyCode As Integer, Shift As Integer)


Public Function Add(TheControl As Access.Control) As ControlArrayItem
  'create a new Pet and add to collection
  Dim NewControlArrayItem As New ControlArrayItem
  NewControlArrayItem.Initialize TheControl, Me
  m_ControlArray.Add NewControlArrayItem, TheControl.Name
  Set Add = NewControlArrayItem
End Function

Public Sub Add_ControlArrayItem(TheControlArrayItem As ControlArrayItem)
  'I also add a second Add to allow you to build the object and then assign it
   m_ControlArray.Add TheControlArrayItem, TheControlArrayItem.ControlArrayItem.Name
End Sub

Public Property Get Count() As Long
  Count = m_ControlArray.Count
End Property

'Public Property Get NewEnum() As IUnknown
'    'Attribute NewEnum.VB_UserMemId = -4
'    'Attribute NewEnum.VB_MemberFlags = "40"
'    'This is allows you to iterate the collection "For Each pet in pets"
'   Set NewEnum = m_Pets.[_NewEnum]
'End Property

Public Property Get Item(Name_Or_Index As Variant) As ControlArrayItem
  'Attribute Item.VB_UserMemId = 0
  'Export the class and uncomment the below in a text editer to allow this to be the default property
  'Then reimport
  Set Item = m_ControlArray.Item(Name_Or_Index)
End Property

Sub Remove(Name_Or_Index As Variant)
  'remove this person from collection
  'The name is the key of the collection
  m_ControlArray.Remove Name_Or_Index
End Sub

Public Property Get ToString() As String
  Dim strOut As String
  Dim i As Integer
  For i = 1 To Me.Count
    strOut = strOut & Me.Item(i).ControlArrayItem.Name & vbCrLf
  Next i
  ToString = strOut
End Property
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Class Methods to Raise Common Events ---------------------------------------------------------------------------------

Public Sub CA_MouseDown(TheControl As Control, Button As Integer, Shift As Integer, x As Single, y As Single)
 RaiseEvent MouseDown(TheControl, Button, Shift, x, y)
End Sub
Public Sub CA_MouseMove(TheControl As Control, Button As Integer, Shift As Integer, x As Single, y As Single)
 RaiseEvent MouseMove(TheControl, Button, Shift, x, y)
End Sub
Public Sub CA_MouseUp(TheControl As Control, Button As Integer, Shift As Integer, x As Single, y As Single)
  RaiseEvent MouseUp(TheControl, Button, Shift, x, y)
End Sub
Public Sub CA_KeyDown(TheControl As Access.Control, KeyCode As Integer, Shift As Integer)
 RaiseEvent KeyDown(TheControl, KeyCode, Shift)
End Sub

Public Sub CA_KeyPress(TheControl As Access.Control, KeyAscii As Integer)
 RaiseEvent KeyPress(TheControl, KeyAscii)
End Sub

Public Sub CA_KeyUp(TheControl As Access.Control, KeyCode As Integer, Shift As Integer)
  RaiseEvent KeyUp(TheControl, KeyCode, Shift)
End Sub
'----------------------------------------------- All Classes Have 2 Events Initialize and Terminate --------
Private Sub Class_Initialize()
'Happens when the class is instantiated not related to the fake Initialize method
'Do things here that you want to run on opening
  Set m_ControlArray = New Collection
End Sub

Private Sub Class_Terminate()
  'Should set the object class properties to nothing
  Set m_ControlArray = Nothing
End Sub

 

[pdanes]

Looking at my example I called it a ControlArrayItem and a ControlArray for the custom collection class.
May not be the smartest approach, but it does work.

Hm, that looks interesting. Far more involved than what I am trying to do, but it looks useful. It will take me some study, though. Thank you.

 

[MajP]

Far more involved than what I am trying to do, but it looks useful.

If you can find a simpler approach then please post back. Writing the class was a little involved but using it is simple.

Private WithEvents CA As ControlArray
'------------------------------------------------------- Load Control Array ------------------------------------------------------------------
Private Sub Form_Load()
  Dim ctl As Access.Control
  Set CA = New ControlArray
  For Each ctl In Me.Controls
    If ctl.Tag = "CA" Then
      CA.Add ctl
    End If
  Next ctl
End Sub

Now you simply capture the events and do whatever you want.

Private Sub CA_KeyDown(TheControl As Control, KeyCode As Integer, Shift As Integer)
  MsgBox "KeyDown from control: " & TheControl.Name & " KeyCode: " & Chr(KeyCode) & " Shift: " & Shift
End Sub

Private Sub CA_KeyPress(TheControl As Control, KeyAscii As Integer)
 MsgBox "KeyPress from control: " & TheControl.Name & " KeyAscii: " & Chr(KeyAscii)
End Sub

Private Sub CA_KeyUp(TheControl As Control, KeyCode As Integer, Shift As Integer)
  MsgBox "Keyup from control: " & TheControl.Name & " KeyCode: " & Chr(KeyCode) & " Shift: " & Shift
End Sub

Private Sub CA_MouseDown(TheControl As Control, Button As Integer, Shift As Integer, x As Single, y As Single)
  MsgBox "Mouse down from control: " & TheControl.Name & " Button: " & Button & " Shift: " & Shift & " X: " & x & " Y: " & y
End Sub

Private Sub CA_MouseMove(TheControl As Control, Button As Integer, Shift As Integer, x As Single, y As Single)
  Debug.Print "Mouse Move from control: " & TheControl.Name & " Button: " & Button & " Shift: " & Shift & " X: " & x & " Y: " & y
End Sub

Private Sub CA_MouseUp(TheControl As Control, Button As Integer, Shift As Integer, x As Single, y As Single)
  MsgBox "Mouse up from control: " & TheControl.Name & " Button: " & Button & " Shift: " & Shift & " X: " & x & " Y: " & y
End Sub

The other additional events that pass a parameter from the application would be
before update
Dirty
Exit
undo

these all pass a Cancel argument. So you may want to add those.

All other parameter-less events can be handled with a common function so no need to include those in the class. But of those only event that I ever needed to cancel would be the beforeupdate.

 

[MajP]

I made the above code in response to the following thread and you may want to look at @arnelgp solution which may provide a simpler approach.

Control Arrays

In days gone by you could use a wonderful object called the Control Array in VB. This would you allow you to, say, have 30 buttons on the form all called "Command" but indexed from 0 to 29. This meant that you could trap any event for all 30 buttons with one procedure (eg: Sub...

www.access-programmers.co.uk

 

[MarkK]

Here's a way you can solve this problem without a single monolithic class for all controls. It wraps each control type in an interface.