Unique value in a subform

[Rennie]

I have a database with businesses in one table and contacts (people) in another table linked together. When entering a new record in the form you enter the details of the business then in a subform enter the details of the contacts for that business.



One of the contact fields is called prime contact, which is meant to be used to indicate this is the main contact for the business. There is only meant to be one of these per business.

My question is how do I put a setting in the subform that only allows one record to be selected as prime contact (yes/no field) or show up some sort of warning box if people try to add a second prime contact for the same business?

[June7]

One way would be VBA code in the BeforeUpdate event of the PrimeContact testbox. This code would query the table for record associated with this buisness already checked as prime. This could use DLookup, something like:
If Me.textboxPrime.NewValue = Yes AND Not IsNull(DLookup("ContactID","Contacts","Prime=Yes AND BusinessID=" & Me.textboxID)) Then
MsgBox "A contact is already designated as prime."
Cancel = True
End If

Another way might be to have a field in Businesses table for the PrimeContact record ID (no Yes/No field in Contacts).

[Rennie]

Thanks very much for that, I took your idea of having the link in the business table and created a filed in the business table that listed a single contact ID as the prime contact, then I just checked against this when changing prime contacts. Thanks again for the inspiration.