Correct table design

[Stephanie]

Hi. I have 3 tables:

Volunteering
VolunteeringID
VolunteeringName...

Frequency
FrequencyID
FrequencyName (1xWeek, 1xMonth...)

Days
DaysID
DaysName (Monday, Tuesday...)

Currently, I have Volunteering holds both the Days and Frequency FKs.
Probably wrong?

I want to be able to list the volunteer opportunity, choose the frequency,
and then choose all of the days that correspond to that frequency using a
combo box. For example: I combo box select 2xWeek for the frequency, then
I'd like to choose Monday and Wednesday from combo boxes (challenge is
knowing how many?).

So, I'm guessing I don't have a table design that works properly. Which is
important before I try to figure out the form. I appreciate your suggestions!

 

[Brian]

FrequencyID works in the Volunteering table because it is a single entry, but
Days has seven possibilities. So, you have two choices: add a Yes/No field to
Volunteering for each day of the week or add another table: VolunteeringDays.

VolunteeringID (FK)
DaysID (FK)

With the first option, you can just have a grid of seven bound checkboxes -
1 bound to each weekday field in Volunteering. The user simply
checks/unchecks each day.

With the second option, you need a continuous subform that will allow the
user to select multiple days. This seems more difficult because now you have
to ensure that the user does not enter the same one twice, etc.

I would probably use option 1, because you know the number of days in the
week is static.

 

[Stephanie]

Interesting! This may be why none of my reports work!
I like the idea of yes/no fields. What about an option group, or does that
only allow you to chose one of the options?
I appreciate you taking the time to reply!

 

[Brian]

Option groups are always either/or - only one of the options can be checked.
If you put the combo boxes in a single row across the form and put the labels
S M T W Th F S, each above the respective combo box, the whole thing won't
take much space.

 

[Stephanie]

Excellent! I used yes/no boxes for the days and it's nifty. I think this
will help when I try to tackle calendar reports (I can wish!).

I do have a question. My Frequency combo box (SELECT Frequency.FrequencyID,
Frequency.Frequency FROM Frequency flips out if it isn't selected first
which is a bad order for me: "index or primary key cannot contain a Null
value".

My form is based on Volunteering. I have a subform to choose the
Organization. The subform is based on 2 tables: EventSponsor and
Organization:

Organization
OrganizationID
OrganizationName

EventSponsor
OrganizationID (CPK)
VolunteeringID (CPK)

As a reminder, I have
Volunteering
VolunteeringID
VolunteeringName
FrequencyID
Monday
Tuesday...

I want to be able to choose the Organization before I put in detail info for
frequency/days. Why is Frequency being a poor sport and what can I do to fix
it?

Thanks!

 

[Brian]

This probably is not a problem with the combo box itself (that RowSource
looks fine). The message: "index or primary key cannot contain a Null value"
appears when you attempt to set the value of a control bound to a required
field (i.e. if it is set to required in the table) to null or when you try to
save a record without the primary key being populated.

So...the key is be to identify why it is attempting to save the record. When
you go from your main form to a subform, I think this will happen
automatically, and if a required field is blank, you will get the message. If
FrequencyID is a foreign key (not the primary key of the table upon which the
main form is based), you might set it to not be required in the table, and
juist make sure it is completed before the user moves to a new record.

 

[Stephanie]

Brian,
Thanks for the follow through. After poking around, Frequency isn't the
culprit. I need to enter a VolunteerName (which is the program name) befor
selecting the Organization, otherwise VolunteeringID is Null which makes
EventSponsor (table of CPK) grumpy. I reactivated some code I had on the
form, which while not triggering correctly yet, does address the issue.

Thanks for your time- I appreciate it!