Yes/No fields and code to uncheck a default "yes" box if another yes box is checked

[Repent]

I have a form with several yes/no fields. I have the first yes/no
field labeled as "No Problems" this I have setup to be "yes"
(checked) by default.

underneath this first field I have several other fields listed, all
unchecked. the idea is, the user open the form and has several yes/no
selections to make. the first is already checked to show "no
problems" however if the user checks any other boxes down below the
first checkbox then that action would unckeck the first "no problems"
checkbox, because now the user is showing that there was a problem and
thus "no problems" is no longer valid.

How can I achieve this? I plan on having several groups of these on
one form. I cannot use an option group because I need to be able to
check more than one thing.

thanks all;

chris

 

[Jeanette Cunningham]

Hi Repent,
here is an example

Create the check boxes with similar name, with a different number in the end
(except for the one called NoProblems, leave it with its current name).

Check1, check2, check3 ... check6
==========================================
Loop through the check boxes to find what value to use for the main
checkbox.
You could run this code in the before update event of the form.


Sub CheckAmountOfCheck()
Dim I as Integer
Dim MySum as Integer

For I = 1 To 6
MySum = MySum + Abs(Me("Check" & I))
Next I

If MySum >0 Then
Me.NoProblems = False
else
Me.NoProblems = True
End If

End Sub


For more info on using checkboxes to store information see this link
http://allenbrowne.com/NoYesNo.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Repent]

Jeanette;

Can I use this solution on a form that has several groups of these
yes/no questions? You mention putting the code in the before update
event of the form.

also, are you saying that i need to name the checkboxes "check1, 2, 3,
etc) instead of the name the user will see?

I'm new to this so could you please expound a little?

thanks;
chris

 

[Jeanette Cunningham]

also, are you saying that i need to name the checkboxes "check1, 2, 3,
etc) instead of the name the user will see?
Yes, name the checkboxes check1, 2, 3, - the name the user sees goes in the
label next to the checkbox.

yes/no questions?

Yes, you could do this if you had a controlling checkbox for each group of
checkboxes - as you have with the checkbox called NoProblems.
The code would go in the before update event of the form.

However, it is always poor design to use several yes/no fields in a table.
One or 2 yes/no fields in a table is manageable, but more than this makes it
very difficult to build and maintain the database.

If you are putting several yes/no fields in any tables, I strongly suggest
that you go back to the table design and change it to avoid having so many
yes/no fields in any table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Repent]

Jeanette;

I have alot of yes/no fields in this certain table. The main reason I
do is because the users want to see all the options available to them
to select all displayed at once. That was why I created "groups" of
yes/no selections. Each group is a category of equipment, i.e, pump,
die, conveyorbelt, etc and for each group or category of equipment
there are several to many options that can be checked as they pertain
to the category of equipment. I thought about having combo/list boxes
but that won't show all options available, whether selected or not.

How would you create controls given what you advise against how i'm
laying this form out? This is really my biggest stumbling block on
the form design.

chris

 

[Jeanette Cunningham]

aWithout knowing enough about what you are trying to do, I can imagine a
subform for each group or category of equipment. Put all the subforms on the
main form.

I can imagine a combo for each thing to be checked on a subform for that
group of equipment.

You could have a subform for pumps.
On that subform would be a series of combos.
The first might be for size, the next for capacity, the next for type etc.
This layout lets you see all the characteristics that apply to pumps. - one
characteristic per combo.

Repeat in a similar fashion for other categories of equipment.


You could have a table for category of equipment.
Then a table for the characteristics.
Another table to show which characteristics apply to which category of
equipment (so you know how to populate the combos, although this table is
optional).
Another table to store the CategoryID of the equipment, the CharacteristicID
for what was measured, date measured and who entered it etc.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia