Combining multiple click events

[JH]

Is there a way to combine multiple click events into one relatively short
function?

What i have right now is a form with two sets of checkboxes. Each set is
about 25 check boxes.

When the form loads, the most commonly selected ones are automatically
checked. But to save time, I also added a "select all" button for each set of
check boxes. When you click it, it will auto check each of the 25. Uncheck
it and all 25 are unchecked.

What I want to happen is if I click the "select all" checkbox and then
uncheck one of the 25 options, the "select all" checkbox should unchecks
itself (since they are obviously not all selected anymore).

Now, I know I can go in an add a Click() event for each of the checkboxes
that will toggle it off, but that seems like a very... clunky solution.

Any suggestions?

 

[Jeff Boyce]

If I'm understanding your description, you want a routine that runs after
any one (or all) of the checkboxes is updated. I'd suggest using the
AfterUpdate event instead of the Click event first.

Then, if you want it to fire for each one, won't you need to have Access
know that something's changed ... for each one? I suppose, depending on
whether all checkboxes are 'bound' controls, you could try using the
AfterUpdate event of the form itself.

But if you have multiple checkboxes bound to underlying table fields, your
table structure is suspect and may not be well-normalized.

You are asking a 'how' question without describing much of the 'what' --
what underlying data are you working with?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[tighe]

set the afterupdate event for all check boxes to "= UncheckAll()"
Add this to a module:
Function UncheckAll()
If Forms!myForm.ActiveControl = 0 Then 'the active control will be the
check box you just checked
Forms!myForm.MyAllCheckBox = 0
End If
End Function

 

[Marshall Barton]

Is there a way to combine multiple click events into one relatively short
function?

What i have right now is a form with two sets of checkboxes. Each set is
about 25 check boxes.

When the form loads, the most commonly selected ones are automatically
checked. But to save time, I also added a "select all" button for each set of
check boxes. When you click it, it will auto check each of the 25. Uncheck
it and all 25 are unchecked.

What I want to happen is if I click the "select all" checkbox and then
uncheck one of the 25 options, the "select all" checkbox should unchecks
itself (since they are obviously not all selected anymore).

Now, I know I can go in an add a Click() event for each of the checkboxes
that will toggle it off, but that seems like a very... clunky solution.

An event ***property*** can call a function by using
=MyFunction()
instead of the usual
[Event Procedure]

If you do that in every check box's AfterUpdate event
property, the function's code could look like:

If Me.ActiveControl Then
Me.chkClearAll = False
Else
Me.chkSelectAll = False
End If

I hate to say it, but anything with 25 check boxes makes it
sound like there are normalization problems in your table
design.

 

[JH]

I suppose I should clarify.

The checkboxes are not tied to my tables.

What I am doing is using these check boxes to dynamically generate reports.


Each checkbox (broken into 4 or 5 categories) represents a property. When
you uncheck a box and run the report, the source record for the report is
edited to reflect the unchecked boxes.


Example:
on a systems report -
System Status: Operational, Implementation, Development, Initiation,
Modification
Each one is a checkbox (not bound to anything).

When you click the button to run the report, if you only have Operational
and Implementation checked, the SQL source for the report has "AND NOT status
= "Development" AND NOT status = "Initiation" AND NOT status =
"Modification". etc...
If you were to mark all the statuses, the report will run without any "AND
NOT" clauses on it.


The point of these reports is to be able to view very specific sets of data
with ease. We have 5 main system properties and each property has 2-12
possibilities. Instead of always generating a report with all 800 systems, I
built this so management can very easily sort things and get reports with the
click of a button.


So I do not think there is an Afterupdate event I can do, because the
checkboxes are not bound to anything, so nothing is "updating" until you
click the "View Report" button. (at which point the only update is the record
source)

Is there a way to combine multiple click events into one relatively short
function?

What i have right now is a form with two sets of checkboxes. Each set is
about 25 check boxes.

When the form loads, the most commonly selected ones are automatically
checked. But to save time, I also added a "select all" button for each set of
check boxes. When you click it, it will auto check each of the 25. Uncheck
it and all 25 are unchecked.

What I want to happen is if I click the "select all" checkbox and then
uncheck one of the 25 options, the "select all" checkbox should unchecks
itself (since they are obviously not all selected anymore).

Now, I know I can go in an add a Click() event for each of the checkboxes
that will toggle it off, but that seems like a very... clunky solution.

An event ***property*** can call a function by using
=MyFunction()
instead of the usual
[Event Procedure]

If you do that in every check box's AfterUpdate event
property, the function's code could look like:

If Me.ActiveControl Then
Me.chkClearAll = False
Else
Me.chkSelectAll = False
End If

I hate to say it, but anything with 25 check boxes makes it
sound like there are normalization problems in your table
design.

 

[Dirk Goldgar]

I suppose I should clarify.

The checkboxes are not tied to my tables.

What I am doing is using these check boxes to dynamically generate
reports.


Each checkbox (broken into 4 or 5 categories) represents a property. When
you uncheck a box and run the report, the source record for the report is
edited to reflect the unchecked boxes.


Example:
on a systems report -
System Status: Operational, Implementation, Development, Initiation,
Modification
Each one is a checkbox (not bound to anything).

When you click the button to run the report, if you only have Operational
and Implementation checked, the SQL source for the report has "AND NOT
status
= "Development" AND NOT status = "Initiation" AND NOT status =
"Modification". etc...
If you were to mark all the statuses, the report will run without any "AND
NOT" clauses on it.


The point of these reports is to be able to view very specific sets of
data
with ease. We have 5 main system properties and each property has 2-12
possibilities. Instead of always generating a report with all 800
systems, I
built this so management can very easily sort things and get reports with
the
click of a button.


So I do not think there is an Afterupdate event I can do, because the
checkboxes are not bound to anything, so nothing is "updating" until you
click the "View Report" button. (at which point the only update is the
record
source)

Even unbound, each check box has an AfterUpdate event.

 

[JimBurke via AccessMonster.com]

Are you checking the value of the 'Select All' check box somewhere in your
VBA code, rather than referring to individual checkboxes? Or do you only look
at the values of individual checkboxes in your code? If the only function of
the Select All is to check or un-check all other boxes, it shouldn't matter
what it's value is, since you only care about the individual boxes. If you
need to know the Select All value in your code, and don't want it to be true
if any of the other boxes are selected, then you'll have to a have a common
routine that get's called by each of the individual boxes in their After
Update code. In that case, each After Update could just call a sub that's in
the form module where all the checkboxes are, e.g.


Private Sub Check1_AfterUpdate()

call SetSelectAll(check1)

End Sub

and so on for each checkbox, then

Private Sub SetSelectAll(byref myCheck as CheckBox)

if not mycheck then
chkSelectAll = false
end if

End Sub

 

[Jeff Boyce]

So, in the event that someone decides to add three more properties for your
reports, or to take away two, you'll have to modify your form, your queries,
your reports, your code, ...?!? That sure sounds like a lot of work!

Are you 'welded' to your checkbox approach, or are you open to an approach
that would allow any number of properties to be 'selected', but could adapt
with minimal effort in the event that the specific properties were changed
over time?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I suppose I should clarify.

The checkboxes are not tied to my tables.

What I am doing is using these check boxes to dynamically generate
reports.


Each checkbox (broken into 4 or 5 categories) represents a property. When
you uncheck a box and run the report, the source record for the report is
edited to reflect the unchecked boxes.


Example:
on a systems report -
System Status: Operational, Implementation, Development, Initiation,
Modification
Each one is a checkbox (not bound to anything).

When you click the button to run the report, if you only have Operational
and Implementation checked, the SQL source for the report has "AND NOT
status
= "Development" AND NOT status = "Initiation" AND NOT status =
"Modification". etc...
If you were to mark all the statuses, the report will run without any "AND
NOT" clauses on it.


The point of these reports is to be able to view very specific sets of
data
with ease. We have 5 main system properties and each property has 2-12
possibilities. Instead of always generating a report with all 800
systems, I
built this so management can very easily sort things and get reports with
the
click of a button.


So I do not think there is an Afterupdate event I can do, because the
checkboxes are not bound to anything, so nothing is "updating" until you
click the "View Report" button. (at which point the only update is the
record
source)

Is there a way to combine multiple click events into one relatively
short
function?

What i have right now is a form with two sets of checkboxes. Each set
is
about 25 check boxes.

When the form loads, the most commonly selected ones are automatically
checked. But to save time, I also added a "select all" button for each
set of
check boxes. When you click it, it will auto check each of the 25.
Uncheck
it and all 25 are unchecked.

What I want to happen is if I click the "select all" checkbox and then
uncheck one of the 25 options, the "select all" checkbox should unchecks
itself (since they are obviously not all selected anymore).

Now, I know I can go in an add a Click() event for each of the
checkboxes
that will toggle it off, but that seems like a very... clunky solution.

An event ***property*** can call a function by using
=MyFunction()
instead of the usual
[Event Procedure]

If you do that in every check box's AfterUpdate event
property, the function's code could look like:

If Me.ActiveControl Then
Me.chkClearAll = False
Else
Me.chkSelectAll = False
End If

I hate to say it, but anything with 25 check boxes makes it
sound like there are normalization problems in your table
design.

 

[JH]

Well, these properties are pretty well set. I work for a federal agency and
each of these properties are specifically identified by policy... which
granted, may change every few years, but this database isn't supposed to be a
long-term solution.

I'm intrigued by what you might have in mind for an alternative method. It
is mildly tedious to add/remove properties as I have reference each checkbox
several times in the VBA code behind the scenes. I know there are some
selection box solutions I could use, but my end users are less... savy than a
regular user. ;-)

Since my report recordsource is dynamically generated every time, my code
uses the checkboxes to display *exactly* what was requested, it also changes
the layout/format of the report and then it outputs a list of what properties
were included to make that report (when you're working with 25 different
options, it's nice to have it right there...).

I built it this way because my time isn't worth as much the time of my upper
managers that need to use the reports - horray for being a low level IT
monkey. I needed it to be as simple to use (for the external user) and as
comprehensive as possible. It has already been unbelievably helpful to two
different divisions of my agency (more so than I had originally expected), so
I consider it a success. Reports that used to take 2-3 days to manually
prepare are now done with as little as 3 mouse clicks and 10 seconds of
waiting for the report to self-populate. Not bad a week and half worth of
work.

 

[Jeff Boyce]

Here's a general look at the alternative I was thinking about...

If you've ever used the query wizard (or one of the other many wizards),
you've seen "paired listboxes". Perhaps you are already familiar with them
through other applications.

The concept is a form with a list(box) on the left, containing all available
options (in your situation, this sounds like the "properties"). On the
right, another list(box), containing all selected options. Between the two,
four command buttons that allows the user to select one, select all, remove
one, remove all. Behind the scenes, you'd need a (temp) table to hold the
selected options, and a lookup table to hold the available options. Use
queries to populate the two listboxes.

Once the right-side list(box) is final, use a command button (?<Print>?) to
fire a procedure that dynamically generates a SQL statement (AKA, a query)
that uses the properties selected as the criteria, then opens the report.

You could even expand this approach futher by providing a list (a combobox)
of available reports. Then, when the <Print> button gets clicked, the
procedure first checks for the report to run, then applies the selected
properties, then opens the selected report.

JOPO (just one person's opinion) ... (and by the way, this is how I offer
reports and selection criteria to the users of my applications ... they have
no need nor desire to learn Access!)

Best of luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[JH]

I actually proposed a solution almost exactly like what you just suggested
and my supervisor said she did not like that method (for whatever reason).

Later, I realized that two of the fields had a property option of "blank"
which would need to be dealt with (how do you add "blank" to a list... if you
use the word "blank" then I would imagine you have to do some special case
code anyway. I did not go too far into it because I didn't pursue the list
box option.)

Oh well... check boxes was the only alternative to the select/list boxes I
could come up with that my supervisor was happy with. I guess it's more fun
to click a checkbox than populate a list. *blink blink*


However, I will inquire again and see what management thinks. If they are
confident the properties will remain the same for the life of this project
(all of maybe 2 years), then I will probably leave the checkboxes in for now.


As for my solution to the whole "select all" thing... I ended up just
putting a function call in the Click() event (not the Afterupdate()... I
don't think it mattered) to uncheck the box. Clunky, but it works.