Howto change the event function name (1 Viewer)

carlton

New member
Local time
Today, 22:17
Joined
Oct 30, 2018
Messages
14
I have a database form design which has 85 labels for user option selection. Can I setup a single event subroutine that can be called every time a label is clicked (i.e. all the label.click events point to the same function) without having a routine for each one. I really don't want 85 separate functions mostly doing the same thing.
TIA
Carlton.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
Yes
Create a function not a procedure. Highlight all of the the controls at one time
In the on click event remove [Event Procedure] and put
=YourFunctionName()

This function will fire when any label is clicked
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
Now if you want it to do something specific for each label, you would need to convert them all to textboxes so that they can be the active control. Then you could do something like

Public Function MyFunction()
dim ctl as access.control
set ctl = me.activecontrol
msgbox ctl.Name
end function
 

Micron

AWF VIP
Local time
Today, 18:17
Joined
Oct 20, 2018
Messages
3,476
Create a function not a procedure
A function is a procedure. You probably meant sub. Both are procedures, although without knowing what the OP wants to do why would it have to be a function and not a sub?


Sorry, don't get this one either.
Now if you want it to do something specific for each label, you would need to convert them all to textboxes
You can use either the click or double click on any un-attached label and pass its name to the procedure and do whatever is needed. Why would they have to be textboxes when you can pass the control itself to the procedure and use any of its properties?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
A function is a procedure. You probably meant sub. Both are procedures, although without knowing what the OP wants to do why would it have to be a function and not a sub
I do not know why, never made sense to me. However, it has to be a function and not a sub routine even though you cannot return anything.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
You can test this easily. Build a form and drop a bunch of labels on the form. Select them all and type in the onclick
=HelloWorld()

In the form's code put in

Code:
Public Function HelloWorld()
  MsgBox "HelloWorld"
End Function

'Public Sub HelloWorld()
'  MsgBox "HelloWorld"
'End Sub

You click on any label and get the message.
Now comment out the top and uncomment the bottom. It will fail with the message cannot find macro or function.
 

Micron

AWF VIP
Local time
Today, 18:17
Joined
Oct 20, 2018
Messages
3,476
However, it has to be a function and not a sub routine even though you cannot return anything.
Disagree. The following sub (label click event) has two calls.

Code:
Private Sub Label7_Click()
labelMessage Me.Label7.Caption 'pass caption as string
labelPass Me.Label7 'pass control as control
End Sub
The standard module has both called procedures as subs:
Code:
Public Sub labelMessage(strCaption As String)
MsgBox strCaption
End Sub

Public Sub labelPass(ctl As Control)
MsgBox ctl.Caption
End Sub
One accepts the control name as a string, the other accepts the control as an object. Either message presents the control (label) caption.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
You are missing the whole point. Read the original thread. There are no specific event handlers. Instead of 82 event handlers the user would only have to write a single function.

In my demo I have 100 labels on the form. The only code on the whole form is
Code:
Public Function HelloWorld()
  MsgBox "HelloWorld"
End Function
The one function handles all events.
 

June7

AWF VIP
Local time
Today, 14:17
Joined
Mar 9, 2014
Messages
5,423
I have done something like what MajP describes. It must be a function procedure because event properties cannot call subroutines without the [Event procedure] connection. Instead of 8 Click event subs, there is one function called in each Click event property, the number in the argument is of course different for each: =HandleButtonClick(1)

The function:

Code:
Private Function HandleButtonClick(intButton As Integer)
btnClose_Click
Select Case intButton
    Case 1
        DoCmd.OpenForm "AddEditProject"
    Case 2
        DoCmd.OpenForm "AddEditSpecifications", , , , , , "Administration"
    Case 3
        DoCmd.OpenForm "EditSubmit"
    Case 4
        DoCmd.OpenForm "AddEditMaterialType"
    Case 5
        DoCmd.OpenForm "AddEditTest"
End Select
End Function
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:17
Joined
Feb 19, 2013
Messages
16,553
@Micron, think your are missing the point. With maj's solution there is no form vba page (unless you want one for other reasons)

against the event in the property window, instead of having [Event Procedure] which references the form vba page, you put =myFunction() which references the module vba pages - in this case a public function called myFunction.

So as maj says Select them all and type in the onclick =HelloWorld()

but a problem if you want to pass the control name - but solvable in the function by using screen.activecontrol - but this only works for controls that can receive the focus - and labels can't receive the focus. Hence the suggestion they need to be textboxes

to pass the label name you would probably need to pass two parameters, the form and the name of the label. And for 85 labels probably want a bit of disposable vba to populate the event property.

Incidentally, bound labels do not have any events - they take them from the control they are bound to. Unbound labels do have the basic click and mouse events
 

Micron

AWF VIP
Local time
Today, 18:17
Joined
Oct 20, 2018
Messages
3,476
I debated on whether or not to completely edit this post or leave it as is. I chose the former as it didn't really add to anything positive to the discussion. For future readers, let's just say I let things going on around here affect my response, which is not cool. It wouldn't have helped if I re-read the original post as suggested because it still didn't click that the desire was to not have 85 calls to the same single procedure. More or less saying the code couldn't be a procedure didn't help with my obstinance either. Not so much as a lesson learned as it is a reminder if you get my meaning. Hopefully this will stand as public contrition and that I can point to my many posts at AccessForums.net as being more characteristic of how I usually respond.



Maybe I should take a break.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:17
Joined
Sep 21, 2011
Messages
14,048
Doesn't the = sign imply a returned value, hence a function.?

You can test this easily. Build a form and drop a bunch of labels on the form. Select them all and type in the onclick
=HelloWorld()

In the form's code put in

Code:
Public Function HelloWorld()
  MsgBox "HelloWorld"
End Function

'Public Sub HelloWorld()
'  MsgBox "HelloWorld"
'End Sub

You click on any label and get the message.
Now comment out the top and uncomment the bottom. It will fail with the message cannot find macro or function.
 

June7

AWF VIP
Local time
Today, 14:17
Joined
Mar 9, 2014
Messages
5,423
There is no requirement for a Function procedure to actually return a value to the calling entity. See my example in post 9.

Actually, if not specified, I expect the function returns a default value. Did a test by calling a function from Immediate window

Function test()
'Do nothing
End Function

?test()


?test() = ""
True

?test() = 0
True

?IsEmpty(test())
True

?IsNull(test())
False
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:17
Joined
Jan 20, 2009
Messages
12,849
I'm not missing the point, you are. You said it had to be a function - I said no it doesn't. You wrote back and said it has to be a function even though you don't/can't return anything. I said no it doesn't here's proof. Whether or not it's one function or one sub makes no difference. It doesn't have to be a function.

What version of Access are you using? Only functions can be called in Event Expressions at least up to Access 2010 and I doubt that has changed since.

However your "proof" suggests you are barking up the wrong tree.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:17
Joined
Jan 20, 2009
Messages
12,849
There is no requirement for a Function procedure to actually return a value to the calling entity.

A function without a defined return will return Empty which is the uninitialized form of Variant.

You can test this with IsEmpty().
 

June7

AWF VIP
Local time
Today, 14:17
Joined
Mar 9, 2014
Messages
5,423
To your corners!

Yes, each Click event sub can call another routine (function or sub) which can reduce the number of lines of code. This might be satisfactory for OP even though there would still be 85 Click subs. However, if the desire is to completely eliminate 85 subs, the only way is to call function directly from each control's event property. The OP didn't clearly state this was goal, just that didn't want 85 nearly identical subs.

So everyone is right! And OP has options.


Galaxiom, apparently I edited my previous post after you read it.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
(i.e. all the label.click events point to the same function)
I may have interpreted that wrong. I assumed that was what the OP currently has (85 events calling the same procedure), not that is what they want. Of course you can have multiple event procedures call a single procedure. I just could not imagine anyone would want to write 85 Event Procedures, thus I interpreted to mean they want a single procedure to handle all of the events.
 

Micron

AWF VIP
Local time
Today, 18:17
Joined
Oct 20, 2018
Messages
3,476
Y'all will probably miss the edit in post 11. I asked but was informed that an edited post can't be moderated to trigger notifications.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
8,463
@Micron,
Like I said in Post 17, I might have read too much into the OPs post. They may have been asking how multiple event procedures can call another procedure, instead of how I interpreted.

With that said, no one demonstrated that.

Code:
Public Sub Label1_Click()
  SomeProcedure
End sub
...
Public Sub Label85_Click()
  SomeProcedure
End sub

Public Sub SomeProcedure()
  some commoncode here
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:17
Joined
Feb 19, 2013
Messages
16,553
whew! Time of another day:D

this is usually the point when the OP says, 'thanks, but what I meant was.....'
 

Users who are viewing this thread

Top Bottom