Control Arrays

[ddcessex]

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 Command_Click(Index) ) for each event.

Can anyone tell me how to emulate this in Access VBA?

Thanks

 

[CJ_London]

write a public function in a module (called say btnClick)

note that in the function you refer to the button as screen.activecontrol

then in your form, highlight all the buttons and type into the click event (where you would normally see '[event procedure]')

=btnClick()

 

[jdraw]

Don't know for sure. Did some googling and found this. Hope it's helpful.

http://stackoverflow.com/questions/23522230/creating-a-class-to-handle-access-form-control-events

There is a link within that post re M$oft
http://support.microsoft.com/?kbid=234907


OOOPs. I see CJ has given a more focused response.

 

[MarkK]

You can also set this programmatically by setting the OnClick property of the control, so if you have 30 command buttons and you want to Assign each one to call a function AND pass ITSELF into that function, do . . .

dim cmd as Access.CommandButton
dim name as string

for i = 0 to 29
   name = "cmd" & i
   me.controls(name).OnClick = "=CommandClick([" & name & "])"
next

And the function that will be called . . .

Function CommandClick(cmd as Access.CommandButton)

In this way you can programmatically alter or remove event handling in VBA.

 

[ddcessex]

write a public function in a module (called say btnClick)

note that in the function you refer to the button as screen.activecontrol

then in your form, highlight all the buttons and type into the click event (where you would normally see '[event procedure]')

=btnClick()

OH Thank you so much!

It took a little sorting but eventually got this to work - this is so helpful, thank you.

You showed me two things: first the structure of how to trap an event for multiple objects and secondly that one can set the event of more than one object at a time.

I will use this in many of my forms and will always be indebted to you.

THANK YOU

Raymond

 

[MarkK]

Wow, very kind words. Glad to be able to help,

 

[ddcessex]

write a public function in a module (called say btnClick)

note that in the function you refer to the button as screen.activecontrol

then in your form, highlight all the buttons and type into the click event (where you would normally see '[event procedure]')

=btnClick()

CJ This is so great and thanks again. I have a question which is puzzling me...

OK so I'm trapping the click event on multiple objects and would like to pass a property of the control that was actually clicked (the Tag property to be precise) to the function somewhat in the fashion...

=btnClick(TheButtonClicked.Tag)

A guru answer would be much appreciated.

Thanks in advance

ps. The objective is simple in that I am trapping the click event of each of the Field Labels in a continuous form where the labels are placed in the Header and the Fields are placed in the Detail. I'm trying to devise a simple interface whereby the user can identify which column is to be used when he later wishes to find records in that column. Each label has it's Tag property set so that the module function can decide what to do when a particular label is clicked - hope this makes sense

 

[CJ_London]

you either pass the control as suggested by mark or use the screen.activecontrol object

so within your btnclick code

dim mystr as string
mystr=screen.activecontrol.tag

 

[ddcessex]

Just a short note to thank you guys for tirelessly helping others.

I have been developing for some 16 years and never known how helpful your responses are.

A BIG THANK YOU on behalf of myself and all others who are busy learning and don't have the time with thanks

 

[wengang1]

Hi. I see that this was over 6 years ago, but I stumbled across it today. I have successfully used the solution posted by MarkK before ( me.controls(name).OnClick = "=CommandClick([" & name & "])"). But now I'm stumped. I need to do a MouseUp event on the array items and capture which Button was clicked. The normal click/mouseup event would tell which button, but by sending the event to a function, how/where do I insert the part that captures which Button was clicked? I don't think I can just pass Button to the function as a parameter because it wouldn't mean anything (it would be a null value). Thanks for any help here.

 

[theDBguy]

Hi. I see that this was over 6 years ago, but I stumbled across it today. I have successfully used the solution posted by MarkK before ( me.controls(name).OnClick = "=CommandClick([" & name & "])"). But now I'm stumped. I need to do a MouseUp event on the array items and capture which Button was clicked. The normal click/mouseup event would tell which button, but by sending the event to a function, how/where do I insert the part that captures which Button was clicked? I don't think I can just pass Button to the function as a parameter because it wouldn't mean anything (it would be a null value). Thanks for any help here.

Hi. Welcome to AWF!

Consider posting a sample db to demonstrate the problem.

 

[CJ_London]

The normal click/mouseup event would tell which button, but by sending the event to a function, how/where do I insert the part that captures which Button was clicked?

I don't believe you can use that method, you would need to create a class and assign the button to it.

@MajP is our expert on classes - he has probably posted an example somewhere

Just found this which may be of help

Creating Pseudo Custom Controls with Class Modules

This demo is in response to a recent thread on creating "custom controls" https://www.access-programmers.co.uk/forums/threads/is-it-possible-to-create-custom-controls.320996/ Unlike other development environments access does not have a way to make a user defined or custom control. However, but...

www.access-programmers.co.uk

 

[MajP]

I actually have an example where I trap a couple hundred image controls, mouse move, mouse up, mouse down. The problem is that it is inside a very complicated form. Something I doubt you seen done in Access before. This form allows you to "dynamically" place images on a form and drag and drop them into position. Supposed to allow you to create restaurant floor layouts.
If I have time I will chop this down to be a generic "control array".
If @wengang1 you post your db, I can provide a "less generic" solution to solve your problem. It takes a lot more time to make a generic solution that works for all cases.

See example of trapping the mouse events in a class, but as I said it is hard to see how that works since there is so much other code to do other things in the form.

Class to move about 20 pictures

Hello, I'm building a kind of floor plan. For this I placed 50 image objects on a form. I am able to display certain images (for example a table for 4 persons or a table for 8 persons) on a selected image object. What I want is that these image objects can be moved with MouseDown/MouseMove...

www.access-programmers.co.uk

 

[CJ_London]

Actually, thinking about it, I have a class for providing some datasheet functionality to a continuous form (primarily move and resize columns), a .accde example here

continuous form that allows users to resize and reorder columns like a datasheet

I developed this because datasheets have some useful functionality, but limitations as well - among them Datasheets can't show controls such as buttons, images, attachments, etc Datasheets can't show a record over multiple rows within the detail section Datasheets don't have header and footer...

www.access-programmers.co.uk

I'll see if I can strip out the relevant parts tomorrow

 

[MajP]

Here is a control Array. I only coded it to handle Labels and Textboxes and to handle Mouse Move, Mouse Up, and Mouse Down. If you want more types of controls and more types of event, you have to add more repetitive code in the class. Unfortunately access requires you to repeat the code for each type of control.

You declare a variable of ControlArray then load it with your controls. Then you can trap the events of the ControlArray.

'Declare a variable
Private WithEvents CA As ControlArray

'Load your controls
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
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Trap the Array Events ---------------------------------------------------------------------------------


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

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

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

FYI, I call it control array, but it is really a custom collection.

 

[MajP]

I have updated the above class to create a "Control Array" that handles MouseUp, MouseDown, MouseMove, KeyUp, KeyDown, KeyPress. Works for labels and textboxes. These are the important events I can think of because they have returned parameters that you usually need to work with.
Keycode, button, shift, x, y, keyascii
The normal solution as others previously mentioned for building a single function as an event handler does not work in this case.
If you want to handle additional controls or events you unfortunately need to replicated the code within the class for each type of control. This is because you cannot trap events of a generic access control because a generic Control exposes no events. Events are defined in the subclasses for each specific control type.

Again, this is only needed in the cases where the event is parameterized. If not simply build a common function.

 

[arnelgp]

i also found

But now I'm stumped. I need to do a MouseUp event on the array items and capture which Button was clicked.

i found a simple code, does not need for a custom Class.
but this time using MouseDown event.