Instead of having an IsPrimary (Yes/No) field, would you consider a Priority
(Number) field? This allows the user to set a number 1 record, then a number
2 contact, and so on. When I do this, I generally allow duplicates, i.e. the
user can have two number 1s for the same company if they really don't care
which is the primary contact. The documentation just explains that it's pot
luck which one the software chooses if there are equals.
If you don't llike that, you could use the AfterUpdate event procedure of
the form (i.e. the subform) to execute an Update query statement to disable
any other related rows that have this field set to yes if the user just set
this one to yes. This involves several steps.
The OldValue of the yes/no field is no longer available in Form_AfterUpdate.
Consquently, you need to use Form_BeforeUpdate to know if the value changed.
1. In the General Declarations section of the form's module (at the top,
with the Option statements), declare a variable:
Private mbBecamePrimary As Boolean
2. In the BeforeUpdate event procedure of the form, set this variable if the
field changed to True:
Private Sub Form_BeforeUpdate(Cancel As Integer)
mbBecamePrimary = ((Me.IsPrimary) And Not Nz(Me.IsPrimary.OldValue,
False))
End Sub
3. In the AfterUpdate event procedure of the form, exeute the Update, and
requery the form so the other rows hear about the change:
Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim strSql As String
If mbBecamePrimary Then
strSql = "UPDATE Table1 SET IsPrimary = False " & _
"WHERE ((IsPrimary = True) AND (MyFK = " & Me..FK & _
") AND (MyPK <> " & Me.MyPK & "));"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Requery
End If
End If
mbBecamePrimary = False
Set db = Nothing
End Sub
Well, that's the basic code. As it stands, this will have the side effectof
jumping to the first record whenever IsPrimary is set to True. If you
prefer, you could loop through the subform's RecorsetClone, using Edit and
Update to turn off the IsPrimary for other records: however, this might not
do it for all related records if the subform were filtered.
(BTW, FK represents the name of the foreign key field, and PK the primary
key field.)
So, does the Priority (Number) field provide better flexibilty, without any
code, and move the onus to the user to make the entries meaningful?
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
- Show quoted text -
When you see what I ended up doing, you'll know how much of a hack I
really am! But it's simple and it works.
What I did, was use a command button on the continuous subform in
question. The form is just a strip with the command button (small) and
a single unbound text field that calls in first name, last name, title
- and then of course a couple of hidden fields like contactid,
companyid and this contactdefault checkbox.
When the button is pushed, an update query changes all the related
contacts to this company equal No, and then after makes
me.contactdefault yes. I've tested it quite a bunch and worked out all
the bugs and it works great. I even spiced it up with conditional
formatting to make the default contact bold so you can easily pick it
out of the list. I could even change the subforms query to sort the
list based on the default if i was feeling really froggy. The code is
kind of messy, but here it is:
Private Sub Command14_Click()
Dim stSQL As String
Dim stControl As String
stSQL = "UPDATE Contacts " & _
"SET ContactDefault = No " & _
"WHERE ContactCompany = Forms!ProspectForm!ID AND ContactID <>
ContactRef"
' ContactRef is bound by the ContactID field
stControl = "Company"
DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
Me.ContactDefault = -1
Forms!ProspectForm!ContactID = [ContactID]
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdRefresh
End Sub
magmike
