Cannot add or change a record because a related record is requiredin table...

[elizabeth baker]

Hi. i'm stumped. This is the first database i've tried to build that is
relational. We are planning a series of events. The tables and
relationships relevant to my problem are as follows (hope i can
articulate them clearly). All the relationships have the "enforce
referential integrity" and "cascade delete" boxes checked. i believe i
need a many-many relationship between contacts and events.

tblContacts
ContactID
1-many relationship with tblEventsLink.ContactID
LastName, etc.

tblEventsLink
EventID (composite key with ContactID)
ContactID (composite key with EventID)

tblEvents
EventID
1-many relationship with tblEventsLink.EventID
EventName
EventTypeID

tblEventTypes
EventTypeID
1-many relationship with tblEvents.EventTypeID
EventType

my form (frmContacts) holds all the info needed for each contact. It
also has a subform (sfrmRSVP) with a query (qryRSVP) as its recordsource.

qryRSVP has these three tables--tblEvents, tblEventsLink, tblContacts
--all related as above for a many-many relationship between contacts and
events. The five fields used in the query are:
tblEvents.EventName
tblContacts.LastName
tblEventsLink.RSVP'd
tblEventsLink.Coming
tblEventsLink.HeadCount

sfrmRSVP displays EventName, RSVP, Coming, and HeadCount.
EventName is a combo box with data supplied by tblEvents.
RSVP and Coming are checkboxes, and HeadCount is a textbox.

my intention is to select an event from the combo box,click to indicate
whether or not the contact has RSVP'd, etc.

i can select an event from the combo box but then i get the following
message when trying to save the record:

You cannot add or change a record because
a related record is required in table 'tblEventTypes'.

i can't figure out what is going on here. Any ideas! Please help. Thanks.

e

 

[Allen Browne]

Open tblEvents in design view.
Select the EventTypeID field.
In the lower pane, remove the zero from the Default Value property.

Access automatically assigns a zero as the default value for any numeric
field, but the zero does not match any EventTypeID value in tblEventTypes.

You might also consider setting the Required property of your foreign keys
to Yes. Even though you have enforced referential integrity, Access permits
nulls in the foreign key unless you mark them as required.

Use cascading deletes sparingly: You probably do not want to loose all the
events of a certain type just because someone decides they don't want that
type any more.

 

[Mikel]

Sounds like your form is trying to create a new Contact and a new Even
at the same time but not associating the Event with an EventType in th
Event table. By setting "enforce referential integrity" between Even
and EventType you have said every Event must have an EventType to be
valid record.

Maybe you want to create a Contact then associate them to an existin
Event so double check the Control Source of components on your form t
check you are not creating new Events rather than just associatin
contacts to existing Events. Or if you do want to create new Contact
and Events at the same time you need to add EventTypeID somewhere int
your form so you can link the Event to an EventType at the same time.

Not sure i fully understood the details of your database so m
suggestions may not be the answer you are looking for

 

[elizabeth baker]

Allen, thanks so much for your help. It's usually something simple,
isn't it?
--e