Event for Multiple Controls 3

[deduct]

I have a form with five command buttons. I would like to be able to associate VBA code with a GROUP of those buttons, and not have to duplicate the code in each individual command button's on click event [yeah, I could just execute the procedure for each button, but...].

Just wondering if there is a way to designate a group of, say, a certain three of the buttons so that I can specify a procedure that they execute (when clicked) in addition to the specific code for each button?

I only need to execute this procedure once, and only when any one of the three command buttons is clicked.


(Access 2003)

Karl

 

[dinger2121]

Can't you just put it in a function. check out this link -

http://www.fontstuff.com/vba/vbatut01.htm

this is for excel, but can easily be adapted for access.

Sorry if I misunderstood what is needed.

 

[deduct]

Yes, I can put the code in a procedure (or function), but then I have to code "calling" of the procedure on each of the command button's on click event. I am looking for an easy way to specify that any buttons that are in this "group" will run this code (prodedure) as well as any code for that specific button.

It may be more trouble than just coding the "call" to the procedure, but I was just wondering if it is possible within Access.

Karl

 

[Ed2020]

The short answer is "no".

I think you are referring to control arrays, which you can create in VB, but not in MS Access (as far as I am aware).

Ed Metcalfe.

Please do not feed the trolls.....

 

[missinglinq]

Ed is correct.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SgtJarrow]

You can code your function to accept one arguement, and pass the name of the control that was clicked. This way you would only have one function to update when necessary. But you will have to put the function call in each button/control's click event though.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PWise]

try this

on the propty sheet for all three command buttons click event put =fuctionname()

and in the fuction

select case Me.ActiveControl.Name
Case "command1"

Case "command2"


Case "command3"

end select

 

[MajP]

I am going to have to disagree. I have described several times on this site how to do this, but I need to write a FAQ. You most definately can do this, but it takes a little code. You have to build a class module to trap your events. Here is an example for demonstration purposes.

thread705-1437513
I can build a form with five labels on it and nothing but the following code

Option Compare Database
Private click1 As New ClickLabel
Private click2 As New ClickLabel
Private click3 As New ClickLabel
Private click4 As New ClickLabel
Private click5 As New ClickLabel
Private Sub Form_Load()
  Set click1.ClickLabel = Me.Label0
  click1.RecordID = 1
  Set click2.ClickLabel = Me.Label1
  click2.RecordID = 2
  Set click3.ClickLabel = Me.Label2
  click3.RecordID = 3
  Set click4.ClickLabel = Me.Label3
  click4.RecordID = 4
  Set click5.ClickLabel = Me.Label4
  click5.RecordID = 5
End Sub

I also have a custom class module called "ClickLabel" with the following code

Option Compare Database
Option Explicit

Private WithEvents mLabel As Access.Label
Private mRecordID As Long

Public Property Get RecordID() As Long
  RecordID = mRecordID
End Property
Public Property Let RecordID(ByVal lngRecordID As Long)
  mRecordID = lngRecordID
End Property
Public Property Get ClickLabel() As Access.Label
  Set ClickLabel = mLabel
End Property
Public Property Set ClickLabel(ByVal lblClickLabel As Access.Label)
  Set mLabel = lblClickLabel
  mLabel.OnClick = "[Event Procedure]"
  mLabel.OnMouseUp = "[Event Procedure]"
 End Property
Private Sub mLabel_Click()
  'run code here
  MsgBox "You clicked label " & mLabel.Name
  If mLabel.ForeColor = vbRed Then
    mLabel.ForeColor = vbGreen
  Else
    mLabel.ForeColor = vbRed
  End If
End Sub
Private Sub mLabel_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  MsgBox "You moused up from " & mLabel.Name & "It is associated with record" & mRecordID
End Sub

Without any event procedures on my form, if I click on a label it will change from red to green (and back) and the name of the label is presented in a message box. If I mouse up the name of an assigned record is shown on a msg box.


Going further I could build a custom collection making it a little easier to instantiate my variables. Using this idea I have a demo where I can load 100 text boxes on a form, react to every text box event, and do all of this in no more than 20 lines of code. Once the class is written it can be used on any form for as many controls as you want.

 

[greg28818]

MajP

Very helpful. I've looked high and low in the past, only to discover that control arrays did not exist in VBA. Saw other explainations on how to do it but thought to much work. Yours very easy.

I was interested in the demo you mentioned at the bottom of your reply. Could you post that? I'm trying to learn more by writing my own March Madness bracket manager. I have 127 labels and as I click on the different labels I want the team to populate another label. I think your solution is perfectly suited to this.

Is there any savings on coding by using textbox or command button as opposed to a label?

Thanks again for the excellent info.

 

[MajP]

No savings.
1) Build a custom class as demonstrated to react to the events.
2) Now you have to build a custom collection that you fill with custom controls. The code is about as much as the above custom class. I am not at home until next Friday, to send the code.
3) Now you put something in the tag property of all the controls like a "?". Then loop the controls and put them in your custom collection like so.

public myControls as myCollection

public sub form_onOpen
dim cntrl as access.control
for each cntrl in me.controls
if cntrl.tag = "?" then
myCollection.add cntrl
end if
next cotrl
end sub

Now all 127 controls with a ? in the tag will react to one or more of the same events.
If you want a good explanation get the Access Desktop Developers Handbook (Litwin and Getz).

 

[MajP]

This is probably a little too late for you to use, but I finally got around to building a FAQ on how to do this.

FAQ702-6904

The code is well encapsulated so the user really does not have to write any code. Just copy and paste.

 

[deduct]

Thank you. Star.

Karl