Hi,
I have a form which has multiple checkboxes as many as two or three
checkboxes (choices) per question.

I want to ensure the user can only select one and only one checkbox
per question.

Each checkbox represents an individual field.

For example, for the question: Did an agency contact you? The choices
are: YES or No (Two separate checkboxes)

Or another example is the question: How long before you hear from the
agency: 1 Week or 2 Weeks or 3 Weeks (Three separate checkboxes)

I know I cannot use an option group as each checkbox represents an
individual field.

Any ideas?

Thanks!

John

zufie wrote:

Yes. Use Option Groups (select it from the toolbox). Then following the
wizard determine which field you want to store the result in and if you
want checkboxes, radio buttons, or toggle buttons. BTW, in your above
example, the results would be 1, 2, or 3

John,
Your table structure is wrong.

If there is a question: "Did an agency contact you?", then the response to
that question should be in one field, _not_ Yes in one field and No in
another. The same applies to all your questions.

If the table was structured properly then you could simply use an option
group tied to the one field.

If you continue with your current structure then you have to write code in
order to change the state of the other related answers when a user selects
an answer.

Going on down the development road when you want to report you'll have to
jump through hoops to make sure you get the correct data out.

Looking at your "How long before you hear from the agency" question, say you
want to change this at a later date to be 1, 2, 3 or 4 weeks? You have to
add a new field to the table, change your form, change your queries for
reports etc. a complete pain which could be obviated by simply storing the
number of weeks in a single field.

I would strongly recommend that if you are controlling the table structure
then stop now, sort your table out first and then go back to the form
development work.
-------------------
To answer your question though if you insist on going the way you are yo can
do something like:-

Question: Did an agency contact you?
Assumptions: 2 fields one indicating Yes, the other indicating No. The
controls on the form called Yes and No respectively.

Private Sub No_AfterUpdate()
Me.Yes = Not Me.No
End Sub

Private Sub Yes_AfterUpdate()
Me.No = Not Me.Yes
End Sub

This becomes much more complex with the question "How long before you hear
from the agency" as you have to jump through hoops to ensure that you have a
valid answer. Personally I would do something like.

Private Sub Ctl1Week_AfterUpdate()
If Me.Ctl1Week Then
Me.Ctl2Weeks = False
Me.Ctl3Weeks = False
End If
End Sub

Private Sub Ctl2Weeks_AfterUpdate()
If Me.Ctl2Weeks Then
Me.Ctl1Week = False
Me.Ctl3Weeks = False
End If
End Sub

Private Sub Ctl3Weeks_AfterUpdate()
If Me.Ctl3Weeks Then
Me.Ctl2Weeks = False
Me.Ctl1Week = False
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me
If Not (.Ctl1Week Or .Ctl2Weeks Or .Ctl3Weeks) Then
MsgBox "How long before you hear from the agency must be
answered"
Cancel = True
End If
End With
End Sub

but then of course this again gets modified by the response to "Did an
agency contact you?" and generally it all starts getting very messy.

The full code (for just these two questions !!!) might look like this:-

'************ Bad code answer start ***************
Option Compare Database
Option Explicit

Private Sub No_AfterUpdate()
Me.Yes = Not Me.No
End Sub

Private Sub Yes_AfterUpdate()
Me.No = Not Me.Yes
End Sub

Private Sub Ctl1Week_AfterUpdate()
If Me.Ctl1Week Then
Me.Ctl2Weeks = False
Me.Ctl3Weeks = False
End If
End Sub

Private Sub Ctl2Weeks_AfterUpdate()
If Me.Ctl2Weeks Then
Me.Ctl1Week = False
Me.Ctl3Weeks = False
End If
End Sub

Private Sub Ctl3Weeks_AfterUpdate()
If Me.Ctl3Weeks Then
Me.Ctl2Weeks = False
Me.Ctl1Week = False
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me
If Me.Yes Then
If Not (.Ctl1Week Or .Ctl2Weeks Or .Ctl3Weeks) Then
MsgBox "How long before you hear from the agency must be
answered"
Cancel = True
End If
End If
End With
End Sub
'************ Bad code answer end ***************


With a couple of option groups and decent validation rules it becomes
something like:-
'************** Good code start *****************
'************** Good code end *****************

That's correct, no code.


Terry Kreft


"zufie" <john.marruffo (AT) illinois (DOT) gov> wrote