How to restrict data entry between two fields

[SamJ]

Table A is a contact list of members, table B is a contact list of non-
members, Table C is an activity table that records activity with both
members and non-members. Table C has fields that look up the member or
non member name in their respective tables.

The trouble is, for any particular record in table C, the user can pull
in a name for both the member and non-member fields, when for any
activity (record) the contact should be with *either* a member or non
member. Is there an easy way to limit data entry in the form to one of
these fields so the user cannot enter data for both?

Thanks.

 

[John W. Vinson]

Table A is a contact list of members, table B is a contact list of non-
members, Table C is an activity table that records activity with both
members and non-members. Table C has fields that look up the member or
non member name in their respective tables.

The trouble is, for any particular record in table C, the user can pull
in a name for both the member and non-member fields, when for any
activity (record) the contact should be with *either* a member or non
member. Is there an easy way to limit data entry in the form to one of
these fields so the user cannot enter data for both?

Thanks.

Well, you're paying the price for what I would consider to be improper
normalization of your tables. Ideally you would have a *single* table of
people, classified as members or nonmembers, and this conflict would not
arise. If you need to store different information about the two subclasses of
the major class of People, you could have a one to one relationship to a table
of Members (and perhaps a different table of Nonmembers).

That said... you will need to deal with this using VBA code on the form. For
instance, you could use code in the BeforeUpdate event of both controls to
check whether the other control is NULL, and complain to the user if it isn't;
you could even disable the Nonmember control in the afterupdate event of the
Member control, and vice versa.

 

[SamJ]

Well, you're paying the price for what I would consider to be improper
normalization of your tables. Ideally you would have a *single* table of
people, classified as members or nonmembers, and this conflict would not
arise.

Yes unfortuntaely the two tables were already in existence when i was
asked to come up with a design to record activity. Without starting from
scratch it seemed to be the best solution.

That said... you will need to deal with this using VBA code on the form. For
instance, you could use code in the BeforeUpdate event of both controls to
check whether the other control is NULL, and complain to the user if it isn't;
you could even disable the Nonmember control in the afterupdate event of the
Member control, and vice versa.

Thanks