Hi Stu,
I don't think there's a really elegant way of doing this. If the back
end database is SQL Server or the like it could be done with a trigger,
a procedure which runs every time a record changes. If the Primary field
in one record was set to Yes, the trigger would run and set Primary to
No in all other records associated with that organisation. You'd then
have to requery the subform so it would reflect the changes.
But Jet - Access's default database engine - doesn't have triggers, so
it's necessary to use code behind the form to do it. The idea is to
build and execute a SQL query that will set Primary to False for all
contacts for the current university, except the current contact. The air
code below (which will need fixing up) assumes
(a) there's a checkbox in the detail section named ckPrimary and bound
to the Primary field. The code would go in the AfterUpdate event of
ckPrimary.
(b) there's a textbox txtContactID in the detail section, bound to the
ContactID field (or whatever the primary key is)
(b) the combobox in the header is named cboOrganisation, and the OrgID
is in the first column of its RowSource (i.e. the Value of the combobox
is the OrgId of the current university).
Dim strSQL As String
Dim dbD as DAO.Database
If Me.ckPrimary.Value = True Then
'Current record has been changed to primary contact
Set dbD = CurrentDB()
strSQL = "UPDATE tblContacts SET Primary = 0 " _
& "WHERE (OrgID = " & Me.cboOrganisation.Value _
& ") AND (ContactID <> " & Me.txtContactID.Value _
& ");"
dbD.Execute strSQL, dbFailOnError
Me.Requery
Set dbD = Nothing
End If
Thanks John. I was able (after a bit of finagling the Event Procedures) to
have the University combo in the header of the continuous form. Now, is
there a way to limit the "Primary" field to =Yes for <=1 contact per
university? It seems that the "option group" does not cover the entire
continuous form, only each individual record?
I hope I am being clear...Now that I have all contacts from, say, Arizona, I
want to pick exactly zero or one contact as "Primary" (a Yes/No field in
tblContacts), and similarly for Baylor and Duke, etc...
TIA,
Stu
Other common approaches to this situation are
-Have a form bound to tblOrganizations, with a subform on it displaying
the contacts belonging to that organization (i.e the linking fields are
tblOrganizations.OrgID and tblContacts.University). So as you move from
organisation to organisation the assocaited contacts are automatically
displayed.
-Use a continuous form displaying the contacts, with a combo box in the
form's header displaying the list of universities. Select a university
from the combo and the contacts are automatically displayed.
I have a report based on the following:
SELECT * FROM tblContacts INNER JOIN tblOrganizations ON
tblContacts.University=tblOrganizations.OrgID ORDER BY
tblOrganizations.[Organization Name];
The page is set up with University as a header, such that all the contacts
from Arizona are listed first, then those from Baylor, Duke, etc.
What I want is a form that can group records this same way. The idea is
that I want an option group so that I can select EXACTLY ONE contact from
each University as "Primary" (tblContacts.Primary=Yes/No). Is it possible
to either
a) Group the records displayed on the continuous form by University, then
display [Last Name], [First Name], and [Primary]?
b) Put the editable control for setting "Primary" onto the report?
TIA,
Stu
