In a subform showing several records subordinate to the record in the main form, how can I ensure that only one record in the subform (and in the many to many link table that supports it) is flagged as the main link?

Background:
table - Contact
table - Organisation
link table - Contact_Organisation_Links
Contact_Organisation_Links table fields:
    ContactID
    OrganisationID
    MainOrganisationFlag (Y/N)
form - Contact
subform - Contact_Organisations
(the subform displays the organisation details for all the organisations linked to the contact and a check box indicates whether that particular organisation is the main one for that contact)

How do I prevent the user from ticking the MainOrganisationFlag check box in more than one record in the subform?


R

__________________
R

You can approach this many ways. Here are two.

1) Don't use checkboxes. Instead, use a dropdown box. In that way, they can only choose one.

2) Don't use checkboxes. Use an option group. In that way, when you select one, the other UNselects.

For saving space and simplicity, I'd try suggestion #1.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Actually, I think the problem is that you have multiple records, not just one single record. So Greg's answer doesn't really address your issue.

In the BeforeUpdate event of your check box, you need to see if there is another Organization that is already selected as the main for the current contact. You can do that with DLookup fairly quickly. It will be something like this...

dim varCheck as variant

varCheck = DLookup("MaingOrganisationFlag","Contact_Organisat ion_Links","(ContactID = " & me.ContactID & ") AND (MainOrganisationFlag = True)")
if isnull(varCheck) then
   ' no main org checked yet
else
   ' main org already checked
   msgbox ...
   Cancel = True ' don't allow this change
end if

You could be a nice guy about it and go update the record where the other flag was set so the update on the current org can proceed. But that's more involved.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org