Generic Onclick event for form of Checkboxes

My vba skills are a little rusty these days so...

I have a form with 40 checkboxes. on update of any one of these I want to fire an event which runs a function

fnUpdateIngredAllergen Me.IngredientID, Me.ActiveControl.Name, Me.ActiveControl.value

which passes that info to a stored procedure.

Currently I have

Private Sub chkDairyCont_Click()
fnUpdateIngredAllergen Me.IngredientID, Me.ActiveControl.Name, Me.ActiveControl.value
end sub

for each checkbox. Well I've actually just got it set for the one right now..

My question is: is there a way to avoid coding that OnClick for every control. Is there something that can determine which control was clicked (ala Me.ActiveControl.Name) and fire a generic event.

ie
function clickListenerTriggeredbySomething()

fnUpdateIngredAllergen Me.IngredientID, magicallydeterminecontrolthatwasclicked, controlthatwasclicked.value

end function

I'm thinking no....
LVL 36
SidFishesAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:


Create a Function similar to this:

Public Function clickListenerTriggeredbySomething() As Boolean

    Dim x, y, z
    x = Screen.ActiveForm("IngredientID")
    y = Screen.ActiveControl.Name
    z = Screen.ActiveControl.value
   
End Function

Then, directly from the Event property sheet for each text box ... call the Function - see image.  You can select ALL checkboxes first, then paste in the Function name - see image.

mx


Capture1.gif
0
 
peter57rCommented:
You can select all checkboxes at once (using shift key or by dragging the mouse) and display the properties- you can then enter the expression just once and it will be applied to all the selected controls.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
more detail ...

mx
Capture1.gif
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SidFishesAuthor Commented:
hmmm getting a byRef error on Public Function clickListener() As Boolean

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Works for me ... and tested.
You can drop the As Boolean.  And post exactly what you have.  

mx
0
 
SidFishesAuthor Commented:
byref error on x to be precise
0
 
SidFishesAuthor Commented:
bah ok that's a byref passing to the function args... Looks like it will work... back in a few
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... get rid of ByRef ...

mx
0
 
SidFishesAuthor Commented:
I had to declare x,y & z individually so they would pass to the function which fixed the byRef (or I guess I could have removed the declarations in the function but I'd rather be explicit)

Public Function clickListener()
   Forms.frmIngredientAllergens.SetFocus
    Dim x As Integer, y As String, z As Boolean
 
    x = Screen.ActiveForm("Ingredientid")
    y = Screen.ActiveControl.Name
    z = Screen.ActiveControl.value
   
   fnUpdateIngredAllergen x, y, z
   
End Function

good stuff

My ctrl-c & ctrl-v thank you ;)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I had to declare x,y & z individually"

Sure ... what I did was just ... 'conceptual' :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.