Solved

Generic Onclick event for form of Checkboxes

Posted on 2011-09-08
10
492 Views
Last Modified: 2013-11-27
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....
0
Comment
Question by:SidFishes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36504627


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
 
LVL 77

Expert Comment

by:peter57r
ID: 36504636
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
 
LVL 75
ID: 36504672
more detail ...

mx
Capture1.gif
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 36

Author Comment

by:SidFishes
ID: 36504698
hmmm getting a byRef error on Public Function clickListener() As Boolean

0
 
LVL 75
ID: 36504740
Works for me ... and tested.
You can drop the As Boolean.  And post exactly what you have.  

mx
0
 
LVL 36

Author Comment

by:SidFishes
ID: 36504755
byref error on x to be precise
0
 
LVL 36

Author Comment

by:SidFishes
ID: 36504774
bah ok that's a byref passing to the function args... Looks like it will work... back in a few
0
 
LVL 75
ID: 36504783
Yes ... get rid of ByRef ...

mx
0
 
LVL 36

Author Comment

by:SidFishes
ID: 36504867
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
 
LVL 75
ID: 36505008
"I had to declare x,y & z individually"

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

mx
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question