Hi Jenfunk,
Fred and Dave raise good questions in trying to help you with this problem.
Fred questions why you need a checkbox at all if the parent is making one or
more selections to fill volunteer positions. From my own experience,
however, it helps to add up all the persons who make a commitment to
volunteer. The checkbox in a sense “summarizes†clearly who and how many
parents agree to volunteer. The checkbox serves as a standalone piece of
information with no reference to exactly what volunteer positions the parent
selects. That in itself may be important to you. There may be situations
too where you need to count up all the persons who answered at least one of
the Yes/No questions, so you can perform percentages on only those who
provided an answer (vs. persons who did not respond). Fred also would like to
know what method you’re using to identify the parents’ selection of the
volunteer positions: Checkboxes, combo boxes, option groups, etc. Without
that information, it’s difficult to advise you.
Dave suggests you consider a more versatile format by creating a many-to-one
table to store one or more volunteer positions selected by each willing
parent. This probably would require an embedded subform into your master
form to track all the selections --- each volunteer position as a separate
record (on a separate line). This method allows you to expand, as Dave
suggests, in the event you add more volunteer positions in the future.
However, there could be factors that might convince you that an additional
many-to-one table with embedded subform in your master form design leads to
more labor and time than you care for. Such factors might be (1) you are
certain that expansion of volunteer positions will not take place, or (2)
that only a small number of parents are going to volunteer, or (3) that a
parent in most cases will make only one or two selections at the most (out of
the six choices), or (4) your database may have a limited life and simplicity
in structure is preferable.
Based on your initial comments, a parent may select more than one volunteer
position. It also sounds like you are using checkboxes to identify each of
the six volunteer positions with a seventh checkbox to identify if the parent
can be simply tagged as a ‘‘volunteerâ€.
For that kind of a situation, I am suggesting the following names to
illustrate my example below. The volunteer checkbox you refer to might be
named, [chkVolunteer]. Each checkbox for the six volunteer positions might
be named, [chkVPost1], [chkVPost2], [chkVPost3], [chkVPost4], [chkVPost5],
and [chkVPost6].
In each checkbox control on your form for the volunteer positions, you would
write an AfterUpdate Event Procedure (Property Sheet, AfterUpdate) as follows:
Private Sub chkVPost1_AfterUpdate()
If (chkVPost1 = -1) Then
ChkVolunteer = -1
Elseif (chkVPost1 = 0) And _
(chkVPost2 = 0) And _
(chkVPost3 = 0) And _
(chkVPost4 = 0) And _
(chkVPost5 = 0) And _
(chkVPost6 = 0) Then
chkVolunteer = 0
End If
End Sub
The elseif section allows the chkVolunteer control to go back to zero if all
six checkbox controls for the volunteer positions are unchecked.
Note too in the above example each line ends with And, space, underscore.
Otherwise the expression would read on one line, (chkVPost1 = 0) And
(chkVPost2 = 0) And (chkVPost3 = 0) And (chkVPost4 = 0) And (chkVPost5 = 0)
And (chkVPost6 = 0) Then followed by a new line: ChkVolunteer = 0
If you were to use an Option Group (select this from the ToolBox), you could
create an extra text field in your master table with a suggested name,
[VPost_chain]. I use “chain†as a suffix to indicate a chain of values
usually separated by a comma or a space: 1, 2, 3, 4, etc.
In Form Design, create an Option Group with a suggested name, [VPost_list].
In the property sheet for the VPost_list control, you would identify the Row
Source with an existing table you created earlier with two suggested fields,
VPostID and VPostDesc. The table might read:
Col1 Col2
1 “Volunteer Position #1†--- (Title the actual position as you like)
2 “Volunteer Position #2†--- (Title the actual position as you like)
3 “Volunteer Position #3†--- (Title the actual position as you like)
4 “Volunteer Position #4†--- (Title the actual position as you like)
5 “Volunteer Position #5†--- (Title the actual position as you like)
6 “Volunteer Position #6†--- (Title the actual position as you like)
In Form Design, highlighting the [VPost_list] control, open the Property
Sheet and create an AfterUpdate Event Procedure as follows:
Private Sub VPost_list_AfterUpdate()
VPoste_chain = (VPost_list)
Elseif Not IsNull (VPost_chain) Then
VPost_chain = (VPost_chain) & “, “&(VPost_list)
End if
End Sub
Note the space immediately after the comma followed by ending quotes. The
result in the [VPost_chain] control on your form will read: 1, 2, 3 when a
parent selects volunteer positions 1, 2 and 3.
The [VPost_chain] field control, thus, provides the many-to-one relationship
all in one field in your master table allowing you to avoid creating a
separate table and embedded subform. You can, of course, edit this field
control at any time in the event you need to make changes to the volunteer
position selections.
Hope this helps,
- - - -
Tank
JenFunk said:
I'm trying to fix a form so that if a parent chooses to volunteer in any 1 of
6 volunteer positions then the volunteer check box will be automatically
checked. If a parent doesn't choose at least 1 position, the check box will
be left unchecked. Please help.
