Only one primary contact (ContactsT) for each associated organization (OrganizationsT

[kelann]

Hi, all!



I have a table (OrganizationsT) and a contacts table (ContactsT).

On ContactsT, there is a combobox (ContactOrg) that pulls from OrganizationsT, so the contact can be associated with an organization.

On ContactsT, there is also a yes/no checkbox (PrimaryContact). Each organization can have several contacts, but there should be only one primary contact.

What I am trying to do is this: If they check the PrimaryContact box, but another contact has that already checked for the same ContactOrg field, they should get a message box.

If they are checking, and there already is a primary contact, it should say, "ContactFullName is already the Primary Contact for this Organization. You must edit this contact before selecting a different primary contact." It would be a bonus if, after 4 seconds or so, it popped up my contact edit form (ContactEditF).

I figure it has an event related to OnClick, but I didn't want to happen when the OnClick UNCHECKS the box.

Thank you so much for your thoughts -- I couldn't even begin to figure out how to google this!!!

--ak

[June7]

You are using a form for this data entry/edit? You could use a form/subform arrangement so that ALL contacts for Organization are displayed in subform. It would then be evident which record is checked as primary. Could have code in checkbox event (OnChange or BeforeUpdate, not sure as I don't use checkboxes much) to validate user entry. Something like:

If Me.checkboxname = True Then
If DCount("ID","tablename","checkboxfield=True") > 0 Then
MsgBox "Your message"
DoCmd.OpenForm "your form name"
End If
End If

If you don't use forms then maybe data macro will handle this code but I have never used data macros.