Making checkboxes mutually exclusive

  • Thread starter Thread starter Eric G
  • Start date Start date
E

Eric G

I have a main form on which there are several checkboxes.
Depending on which checkboxes are checked, different queries are run.

Can someone tell me if it is possible to prevent a series of
checkboxes from being checked, if another series is checked?
If so, guidance to set this up would be appreciated.

TIA Eric
 
You can use an Option Group which is available using the Control Wizard in
your form's design view. You can format the options as check boxes.
 
Hi Cheryl,

I thought about an Option Group but don't know if I'd have to do a
major overhaul of the form or not.

My current form design looks something like this:

CancelAll Keep Chk Student Suspend Days
/ / Harris, Fred
/ Smith, Lucy
Black, Roger / 2
2 / White, Claire

Here's how it works. Any student can have:
Chk
Chk + CancelAll
Chk + Keep
(CancelAll and Keep _should be mutually exclusive)
Chk + Suspend + Days
(Suspend and Days should be mutually inclusive, not allow
just one)
CancelAll
Keep
Suspend + Days

But they shouldn't have:
CancelAll + Suspend (these should also be mututally exclusive)

Keep runs the CancelAll query plus another.
Suspend runs the CancelAll query plus another.

Because my admin person is somewhat clumsy in her handling of the
form, she has managed to come up with 'non-desirable choices', such as
CancelAll + Days instead of Suspend + Days.
Or today just inputting a number for Days and not adding a check for
Suspend.

I hope you can make sense of this all. Thanks!
Eric
 
Eric,

Here's how I think I'd set it up:

In the Option Group Wizard, you will have 3 options:

Cancel All
Keep
Suspend

Days does not belong in here as an Option, as it is something that your user
will be required to fill in if she checks Suspend.

Presuming you use the default Option numbers for the group:

Cancel All = 1
Keep = 2
Suspend = 3

To control the appearance of a number in the control Days, set its Locked
property to True. Your user will only have access to it if the Suspend
option is selected and she will be prompted for the number by means of an
InputBo.

In the AfterUpdate event of the OptionGroup, let's call it optDiscipline,
you could enter something like the following code:

Dim strDays as string

Select Case Me.optDiscipline
Case 1
' Do something here
Case 2
DoCmd.OpenQuery "CancelAll"
Case 3
strDays = InputBox("Enter the number of days of suspension")
' Unlock the Days control
Me!Days.Locked = False
' Use Val() function to convert string value for
' Days to a number - presuming that the
' field is a Number type
Me!Days = Val(strDays)
Me!Days.Locked = True

Do.Cmd OpenQuery "CancelAll"
End Select

hth,
 
Hi Cheryl,

Thanks for your help!
I like your idea very much and have started the design of the new
form.

I have a few questions.
Presuming you use the default Option numbers for the group:

Cancel All = 1
Keep = 2
Suspend = 3
Click to expand...

When setting up with the wizard I chose 'save the values for later
use', rather than 'store the values in a field'. Was this the right
choice?

To control the appearance of a number in the control Days, set its Locked
property to True. Your user will only have access to it if the Suspend
option is selected and she will be prompted for the number by means of an
InputBo.
Click to expand...

Do I create this as a text control box?

In the AfterUpdate event of the OptionGroup, let's call it optDiscipline,
you could enter something like the following code:

Dim strDays as string

Select Case Me.optDiscipline
Case 1
' Do something here
Case 2
DoCmd.OpenQuery "CancelAll"
Case 3
strDays = InputBox("Enter the number of days of suspension")
Click to expand...

Will this value be transferred to the text control box?
' Unlock the Days control
Me!Days.Locked = False
' Use Val() function to convert string value for
' Days to a number - presuming that the
' field is a Number type
Me!Days = Val(strDays)
Me!Days.Locked = True

Do.Cmd OpenQuery "CancelAll"
End Select
Click to expand...

As I now enter values onto this new form, (continuous forms view)
with several records being viewed, the same value is being copied into
each record, (they aren't independent right now).
Will this change?

My current AttendSus table mirrors my AttendSus form. Values that are
input into the form are being entered into the table. It is from this
table that my various queries work with.

Will I be able to operate the same way with the new form?
It seems there is a fundamental difference; with the option box having
an event attached to it. (afterupdate event)

I need to be able to have a series of queries run at a particular
time, not when the user has finished inputting the values.

Thanks again! Eric
 
Hi Eric,

Comments In-Line:


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Eric G said:
Hi Cheryl,

Thanks for your help!
I like your idea very much and have started the design of the new
form.

I have a few questions.


When setting up with the wizard I chose 'save the values for later
use', rather than 'store the values in a field'. Was this the right
choice?
Click to expand...

It depends. See my response to your question: "As I now enter values onto
this new form, (continuous forms view) > with several records being viewed,
the same value is being copied into > each record, (they aren't independent
right now)."
Do I create this as a text control box?
Click to expand...

Yes, This is a normal text box, bound to your Days field. The only
departure from normal is that the Locked property is set to true. As you
indicated in your earlier post, there seems to be a problem with Days being
applied when Suspended is not checked, so you can use code to allow Days to
be applied/assessed ONLY when Suspended is checked.
Will this value be transferred to the text control box?
Click to expand...

Yes. In the code below
As I now enter values onto this new form, (continuous forms view)
with several records being viewed, the same value is being copied into
each record, (they aren't independent right now).
Will this change?
Click to expand...

I must have overlooked mention of your using a continuous form in your
earlier post. For a continuous form, this is normal behavior. To have each
control in a column display values independent of each other, the control
must be bound to a field in the underlying table or query. So, Days should
be a field in your table. And, if you want the Option Group values to be
independent of each other, you may need to have a field in your table named,
say, DiscipAction, which will store the value selected from the Option
Group.
My current AttendSus table mirrors my AttendSus form. Values that are
input into the form are being entered into the table. It is from this
table that my various queries work with.

Will I be able to operate the same way with the new form?
Click to expand...

Your earlier post indicated a desire for better control/accuracy of data
entered. In order to get this, you may need to make some changes in how the
data is entered. That is what the Option Group (allowing only one choice)
and InputBox (which allows Suspension Days to be entered only when the
Suspended option is selected) will do for you.
It seems there is a fundamental difference; with the option box having
an event attached to it. (afterupdate event)
Click to expand...

The AfterUpdate is just a means of letting Access know that you want it to
do something when the user updates a control on a form. If you do not want
to run your queries after the user selects an option in the Option Group,
just delete those lines. However, if you want to control the entry of
Suspension Days, leave the code in Case 3 as it is.
I need to be able to have a series of queries run at a particular
time, not when the user has finished inputting the values.
Click to expand...

You can cause your queries to run at any time you want, either from an
AfterUpdate event from some other control or from a command button on your
form.
 
Hi Cheryl,

OK I think I understand the concept now, it's just putting
it into practice that I'm not 100% sure about.

I created the form and have simplified it so that only the
Attendance check + the Option box is visible. It's easier
on the eyes this way.
I've hidden the KeepNum control and the Days control.

If the admin will select Option 2, then an Input box will
pop-up asking for the KeepNum. Similarly when Option 3 is
selected, an Input box will pop-up asking for the Number
of Days for the suspension.

I don't know if I need to put a Control source for the
Option box frame, and if so what it should be?

I must have overlooked mention of your using a continuous form in your
earlier post. For a continuous form, this is normal behavior. To have each
control in a column display values independent of each other, the control
must be bound to a field in the underlying table or
Click to expand...
query.

Sorry, I forgot to mention I had a continuous form!
It's a list of today's students who have detentions, with
accompanying check boxes.
So, Days should
be a field in your table. And, if you want the Option Group values to be
independent of each other, you may need to have a field in your table named,
say, DiscipAction, which will store the value selected from the Option
Group.
Click to expand...

OK I have SusDays and Keep in my AttendSus table.
I'm having problems with the Option Group part.

My AttendSus table currently has the following fields:
DetID, Chk, CancelAll, Keep, Sus, SusDays

Keep and SusDays are number fields that store the
appropriate number.
Chk, CancelAll and Sus are checkboxes.

It would be nice if I didn't need to add another field to
the table.
But if I need to...
Would you recommend that I possibly consolidate my
CancelAll and Sus checkboxes into one that would cover the
three Option Box choices (CancelAll, Keep, and Sus) and
change it from a checkbox into a text, indicating (C/K/S) ?

So the part I'm stuck on now is how to _display
independent values in the new continuous form. I'm able to
get a value into the AttendSus table, but then it repeats
for every student in the form.
The AfterUpdate is just a means of letting Access know that you want it to
do something when the user updates a control on a form. If you do not want
to run your queries after the user selects an option in the Option Group,
just delete those lines. However, if you want to control the entry of
Suspension Days, leave the code in Case 3 as it is.
Click to expand...

OK, I understand now.
You can cause your queries to run at any time you want, either from an
AfterUpdate event from some other control or from a command button on your
form.
Click to expand...

OK, great.

Thanks!
 
OK I think I understand the concept now, it's just putting
it into practice that I'm not 100% sure about.
Click to expand...

Actually, you're catching on to this pretty quickly. I can see that as you
type your questions, you're already posing solutions to them.
It would be nice if I didn't need to add another field to
the table.
But if I need to...
Would you recommend that I possibly consolidate my
CancelAll and Sus checkboxes into one that would cover the
three Option Box choices (CancelAll, Keep, and Sus) and
change it from a checkbox into a text, indicating (C/K/S) ?
Click to expand...

Yes! Combine all of these into a single field (number type - to correspond
to your Option Group values). Now, in your form, bind the Option Group to
this field by making this field the Control Source for the Option Group.
You'll find that binding this control to a field in the table will make the
value of the Option Group for each student independent of the other.

hth, good luck with your project and do post back with any follow-ups.
 
Hi Cheryl,
hth, good luck with your project and do post back with any follow-ups.
Click to expand...

Thanks very much for your help. I'm able now to get the form to work
properly. I've been working on a test form and test backend .mdb file.
I'm going to have to weigh whether the change is warranted at this
time.

After I added the new field to the AttendSus table I wasn't able to
enforce referential integrity with the one-to-one link between it and
the Detentions table.

On the positive side, I went back to my original db (before the new
form changes) and tightened up the queries that work with the
AttendSus form.

For example, my SusQry (update query) would update the status field in
Detentions when the Sus checkbox was true.
I changed that to include when SusDays Is Not Null (in case the
SusDays is input but not the Sus checkbox), etc.

Eric
 
Back
Top