Trap some multiple occurrences of specific values in specific fiel

[tuesamlarry]

Have a form (sfrmFamilyMembers) which is used to enter the names and
attributes of people belonging to a household which has been given a ClientID
pk. Form fields include ClientID, MemberID, LastName, FirstName,
RelationToClient. Relations include "Self", "son", "daughter", "mother",
"friend", etc. There can be multiple sons, daughters, but only 1 Self,
father, mother, etc. I want to prevent the user entering more than 1 Self,
mother, etc. What is the best way to go about this. I created a DCount method
on a query filtering for SELF and placed it in the BeforeUpdate property of
the relation field. My query works standalone but haven't been able to get it
to work in VBA behind the form. Thank you for your advice.
Access 2007

 

[pietlinden]

Have a form (sfrmFamilyMembers) which is used to enter the names and
attributes of people belonging to a household which has been given a ClientID
pk. Form fields include ClientID, MemberID, LastName, FirstName,
RelationToClient. Relations include "Self", "son", "daughter", "mother",
"friend", etc. There can be multiple sons, daughters, but only 1 Self,
father, mother, etc. I want to prevent the user entering more than 1 Self,
mother, etc. What is the best way to go about this. I created a DCount method
on a query filtering for SELF and placed it in the BeforeUpdate property of
the relation field. My query works standalone but haven't been able to getit
to work in VBA behind the form.  Thank you for your advice.
Access 2007

Test in the before insert event of the subform. If the count># then
cancel the insert by setting Cancel=True

 

[tuesamlarry]

I tried the before insert on the form but that didn't work either. Set up an
experiment to determine what was going on and realized (belatedly) that the
query looking for records of course needs actual saved records. So putting
the test for duplicate records wouldn't alert me until I was on my third
record. I have since put the DCount of the query on the On Current property
of the form and it does alert me once that second record is saved. Not
exactly as soon as I had hoped for, but good enough.