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

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.
 
P

PieterLinden via AccessMonster.com

Steve said:
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.

Just asking, but why not use a combobox instead with 30 options? IF you set
the LimitToList property to True and the AutoExpand property to True, the
user should get to his option with a few keystrokes.
 
J

John W. Vinson

I have 30 check boxes where only one box can be checked at a time.

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.
 
S

Steve Stad

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
 
S

Steve Stad

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
 
J

John W. Vinson

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:

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.
 
J

John Spencer

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
 
J

John Spencer

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
 
S

Steve Stad

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?
 
D

Douglas J. Steele

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!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top