'Cannot Add' Error Message - Forms

[Allan March]

Hi there,
I have been working on a database for my golf club that records then prints
prize vouchers for our competitions. I have an "Event Table", primary key of
EventID in which all the competition details are entered. The "Event Table"
has a 'VoucherID' foreign key field which gives a one-to-many relationship
with the "Voucher Table". That is, for any Event you can have more than one
prize voucher.
The "Event" form is based on a query of the "Event Table" and has two combo
dropdown boxes that allow you to select 'Event Type' and the 'Employee'. I
have not- in-list procedures for these combo boxes and they work fine.
The "Voucher" form, primary key of VoucherID, is linked to the 'Event' form
as a subform, the many side of the above relationship. The "Voucher Table"
has four foreign key fields of which the "Member" and "Vistor" fields are
giving me a problem.

Again the 'Voucher' form is based on a query of the "Voucher Table" and
related fields in the four foreign key fields/tables. Two of the foreign
field key present no problem as the have attached combo boxes and are looking
at only one piece of information from their tables.
My problem is that when I set up the voucher form and create combo drop down
boxes for the "Member" or "Visitor" (which fills in related information on
the form) I have to choose a name from both combo boxes for the record to be
saved and printed as a voucher. If I leave the "Visitor" combo box blank I
get the following error message 'You cannot add or change a record because a
related record is required in table Visitors' and vice a versa for the
'Member' combo box.

Is there away around this problem? The way I originally wanted to do the
form was to have an option group on the voucher form that enabled/disabled
either the 'Member' combo box or the 'Visitor' combo box depending on the
choice made in the option group but this approach doesn't work. If the
problem is because I have the two foreign keys in the "Voucher Table" should
I create another table for Visitors Vouchers only and have this linked to
Event Table as the same way as above and thus have two subforms on the Event
form. Can you have two subforms related to the Event form by the EventID
primary field?

If anyone has any suggestions on how to best overcome this problem or what I
am doing wrong in the first place I would like to thank you in advance for
your reply.

Regards
Allan March

 

[Brian]

Just a couple of questions to clarify the question:

1. In what table(s) are the Member & Visitor the primary keys?

2. Is there a) always, b) ever, or c) sometimes both a Member & Visitor on
a voucher?

If a voucher has only a member OR a visitor, perhaps you need a table
structure in which the Vouchers table has a Member Yes/No box. If it is True,
then the info will be for a member; if False, then for a visitor. In the
Click event of this box, you could set the RowSource of the person's name to
either show Members or Visitors.

 

[Allan March]

Thanks for your reply Brian.
To answer your questions:
1) The MemberID is part of the Members Table and and the VisitorId is part
of the Visitor Table. Both are in a one to many relationship with the voucher
table. That is, a Member or Visitor can win many different Vouchers.
2) There can only be either a Member on a single voucher or a Visitor on a
single voucher not both.
3) The Voucher form is a subform of the Event form and is linked in
Parent/Child relatonship via the EventID field.

Your suggestion is a good one but I am not sure how I would go about doing
it as the information I need to put on the voucher varies from a member to a
vistor e.g I don't need to put the Members golf club on it as, of course,
being a member we know his club but I need to put a visitor's club on the
voucher. I don't think I could work out how to make it autofill the details
from the Visitor Table this way.

Your suggestions are most welcome. Perhaps my whole approach to the problem
is wrong. Perhaps I should have the voucher as the parent and the event as
the child in a one to one relationship. Anyway as I said your thoughts are
most welcome.
Regards
Allan March

 

[Brian]

If there are several or many fields in common (i.e. used whether it is a
member or a visitor), I would probably them into one table, and a Yes/No
Member field, and just leave some fields blank in the table if it is a
visitor, and others if it is a member. You can control this on the form where
you input the personal info; just enable/disable the various boxes in the
Click event of the Member box, based on whether the individual is a member or
a visitor. Then, once you select the individual on the Vouchers form
(AfterUpdate of the selection combo box), use:

If DLookup("[Member]","[PeopleTable]","[PersonID] = " & Me.[PersonSelector])
Then
'enable & populate boxes relevant to members; disable others
Else
'enable & populate boxes relevant to visitors;disable others
End if

However, enough on my design philosoph, and back to the original issue (to
work within your existing design). The message you are getting normally
indicates that there is an entry in the control on the form, but there is no
corresponding entry in the foreign table. Check these two things:

1. Are both Visitor & Member are required in the Vouchers table, but you
really want to require only one or the other. If this is the case, remove the
required property from the table and add some code on From_BeforeUpdate to
ensure that there is a member OR a visitor, not both, or
2. When you select a member, does Visitor also have a value (and vice
versa)? Throw a "MsgBox VisitorID" and "MsgBox VisitorID" into your VBA code
(perhaps Form_BeforeUpdate?) to see if it pops up values for both.