Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 31st, 2005, 01:22 PM
Authorized User
Join Date: Mar 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default selecting only one primary record in a subform

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?

table - Contact
table - Organisation
link table - Contact_Organisation_Links
Contact_Organisation_Links table fields:
    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?

Old April 1st, 2005, 09:12 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

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
Old April 1st, 2005, 10:20 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

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
   ' 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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting top record of a group by clause lic023 Access 7 June 7th, 2006 11:25 AM
Primary Key causes multiple record deletion mhellste SQL Server 2000 6 November 23rd, 2005 07:19 PM
Subform only may contains 1 record Stanny Access 5 December 21st, 2004 11:17 AM
selecting multiple records as one record array Greywacke Classic ASP Databases 1 February 10th, 2004 10:33 AM
Subform/Links ~ Too Many Primary Keys fdsi19 Access 1 September 30th, 2003 12:48 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.