Checkbox Validation

[kelann]

Thanks, June!

HE

[kelann]

Hi, June!

I was able to download, but not open, it at home (I'm off today). Not sure if it's because I have 2007 at home, or if it's due to the same problem it's giving you.

Let me try to upload it once more -- if it still won't open for you, I'll try it again tomorrow from work.

I really appreciate your help!!

--ak

Event 10-07-2012.zip

[June7]

Try this:

Set the PrimaryContact combobox DefaultValue property to "No"

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strPrimary As String
strPrimary = Nz(DLookup("ContactFirstName & ' ' & ContactLastName", "ContactsT", "PrimaryContact = 'Yes' AND ContactOrg='" & Me.ContactOrg & "'"), "")
If Me.PrimaryContact = "Yes" And strPrimary <> "" Then
    MsgBox "This organization already has " & strPrimary & " as primary contact." & vbCrLf & _
    "There can be only one primary contact at an organization.  " & vbCrLf & _
    "Please edit the other contact before designating this person as the primary contact."
    Cancel = True
    Me.PrimaryContact = "No"
ElseIf Me.PrimaryContact = "No" And strPrimary = "" Then
    MsgBox "Each organization must have a primary contact.  This is the only contact for this organziation, so you must choose Yes."
    Cancel = True
    Me.PrimaryContact = "Yes"
End If
End Sub

[kelann]

June,

I saved, but ended up not using, this code -- it worked really well, except that if you were editing the contact that was, in fact, the primary contact, it would still block you and let you know there was a primary contact.

Rather than continue to bother you with this, I reworked the database so it wasn't necessary to have only one primary contact. I needed it to be that way for a report, but I just reworked the report.

Anyway, I wanted to thank you for all your help -- I learned so much from you and others here!

--ak

[June7]

Suggested code was written for the ContactsAddF which is set to only allow new record entry, not edit existing. Problem with searching on names is that can't consider misspellings by user, but nothing's perfect.

Code for existing record would require some slightly different logic. But if this is no longer an issue for you, will mark as solved.

[kelann]

Oh, yes, June, that makes perfect sense that it would need different code for editing a record. It worked perfectly for the add!!! I also meant to thank you for adding the part about saying who the primary contact was -- that was awesome!

If I'm asked to add this concept back, I will try, based upon what I've learned from you and others, to make the edit modifications. If I can't quite pull it, you may hear from me again.

Many, many thanks!!!

--ak