Auto Check/Uncheck boxes based on checking 1 of 30 boxes

  • Thread starter Thread starter Steve Stad
  • Start date Start date
S

Steve Stad

I have 30 check boxes where only one box can be checked at a time. For
example, if box 1 is YES, then box 2 thru 30 should autopopulate to 'NO'. If
box2 is YES then box 1 and box 3 thru 30 should autopopulate to 'NO'. And so
on thru box 30. Is there code available to accomplish this and enforce only
one box can be checked at at time.
 
I have 30 check boxes where only one box can be checked at a time.
Click to expand...

Then you have an incorrectly designed table. You can do this with ONE table
field, with thirty possible values; you can use a combo box, or a listbox, or
an option group control to select the desired value.
 
John,

Thank you for your reply re table design. You are probably correct about
the most optimal table design etc. The check boxes are used to indicate if a
step in a multi-step process is dated/or completed. There are 30 steps. I
was able to code the rule(s) based on the after_update event for each check
box as follows:

Private Sub VAC_STEP_01_AfterUpdate()
Me.VAC_STEP_02 = NO
Me.VAC_STEP_03 = NO
Me.VAC_STEP_04 = NO
Me.VAC_STEP_05 = NO
Me.VAC_STEP_06 = NO
Me.VAC_STEP_07 = NO
Me.VAC_STEP_08 = NO
Me.VAC_STEP_09 = NO
Me.VAC_STEP_10 = NO
Me.VAC_STEP_11 = NO
Me.VAC_STEP_12 = NO
Me.VAC_STEP_13 = NO
Me.VAC_STEP_14 = NO
Me.VAC_STEP_15 = NO
Me.VAC_STEP_16 = NO
Me.VAC_STEP_17 = NO
Me.VAC_STEP_18 = NO
Me.VAC_STEP_19 = NO
Me.VAC_STEP_20 = NO
Me.VAC_STEP_21 = NO
Me.VAC_STEP_22 = NO
Me.VAC_STEP_23 = NO
Me.VAC_STEP_24 = NO
Me.VAC_STEP_25 = NO
Me.VAC_STEP_26 = NO
Me.VAC_STEP_27 = NO
Me.VAC_STEP_28 = NO
Me.VAC_STEP_29 = NO
Me.VAC_STEP_30 = NO
End Sub
 
Peter,

Thank you for your reply re using como box solution. It may be the most
optimal. The check boxes are used to indicate if a step in a multi-step
process is dated/or completed. There are 30 steps. I was able to code the
rule(s) based on the after_update event for each check box (e.g., repeated 30
times) as follows:

Private Sub VAC_STEP_01_AfterUpdate()
Me.VAC_STEP_02 = NO
Me.VAC_STEP_03 = NO
Me.VAC_STEP_04 = NO
Me.VAC_STEP_05 = NO
Me.VAC_STEP_06 = NO
Me.VAC_STEP_07 = NO
Me.VAC_STEP_08 = NO
Me.VAC_STEP_09 = NO
Me.VAC_STEP_10 = NO
Me.VAC_STEP_11 = NO
Me.VAC_STEP_12 = NO
Me.VAC_STEP_13 = NO
Me.VAC_STEP_14 = NO
Me.VAC_STEP_15 = NO
Me.VAC_STEP_16 = NO
Me.VAC_STEP_17 = NO
Me.VAC_STEP_18 = NO
Me.VAC_STEP_19 = NO
Me.VAC_STEP_20 = NO
Me.VAC_STEP_21 = NO
Me.VAC_STEP_22 = NO
Me.VAC_STEP_23 = NO
Me.VAC_STEP_24 = NO
Me.VAC_STEP_25 = NO
Me.VAC_STEP_26 = NO
Me.VAC_STEP_27 = NO
Me.VAC_STEP_28 = NO
Me.VAC_STEP_29 = NO
Me.VAC_STEP_30 = NO
End Sub
 
John,

Thank you for your reply re table design. You are probably correct about
the most optimal table design etc. The check boxes are used to indicate if a
step in a multi-step process is dated/or completed. There are 30 steps. I
was able to code the rule(s) based on the after_update event for each check
box as follows:
Click to expand...

And... if next week, the bosses add steps numbers 31 and 32, will you
restructure your table, rewrite all your queries, redesign all your forms and
reports? OUCH!

"Fields are expensive, records are cheap". If each Process has 30 (or 32, or
any number) of Steps, and each Step will be in many Processes, you have a many
to many relationship; the correct table structure would have three tables:

Processes (or whatever it is modeling, with the current fields excluding the
30 checkbox fields)

Steps (a 30 row table identifying each step)

ProcessSteps (with foreign keys to Processes and to Steps)

Continuing to struggle with your current design will become a maintenance
nightmare.
 
Well you could make that a LOT more efficient if you have to stick with this
table design.

Private Sub sResetChecks(KeepThisOne as Long)
Dim I as Long

'Set everyone to false
For I = 1 to 30
Me("VAC_STEP_" & Format(I,"00")=False
Next I

'Now set the current one to true
Me("VAC_STEP_" & Format(KeepThisOne,"00")=False

End Sub

Then all you need in each of the controls after update event is a line like
sResetChecks 1
or sResetChecks 28

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Whoops. A little cut and paste error


Private Sub sResetChecks(KeepThisOne as Long)
Dim I as Long

'Set everyone to false
For I = 1 to 30
Me("VAC_STEP_" & Format(I,"00")=False
Next I

'Now set the current one to true
Me("VAC_STEP_" & Format(KeepThisOne,"00")=True

End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John,

I will try this code and let you know. BTW.. I did not see any difference
between your two examples (but assume you corrected the first also). Do I
name the subroutine sResetChecks 1 or sResetChecks 28 or does it matter?
And, will the after_update event trigger the subroutine?
 
John set Me("VAC_STEP_" & Format(KeepThisOne,"00") to False, rather than
True in the first answer. He corrected that in the second.

You do not change the subroutine's name from sResetChecks. sResetChecks 1
and sResetChecks 28 are examples of how you invoke the routine. In the first
case (sResetChecks 1), only check box 1 will be selected. In the second case
(sResetChecks 28), only check box 28 will be set.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)
 
Back
Top