Solved

Generic Onclick event for form of Checkboxes

Posted on 2011-09-08
10
488 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
  • 5
  • 4
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

919 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now