Checkbox Validation

[kelann]

I have a table (ContactsT) that associates contacs with organizations (ContactsOrg) and has a checkbox (PrimaryContact) to indicate that that person is the primary contact for that organization.



I would like to provide for the following, if possible:

If someone checks the PrimaryContact box, and there is another contact has that box checked for that organization, I would like a message to say that "ContactFirstLast is already listed as the primary contact for this organization. An organization can only have one primary contact."

Also, if the organization is selected, and no contact has been associated with it yet, I'd like the box to check itself by default and, if someone unchecks it, to have a message that says, "An organization must have a primary contact. This is the only contact associated with ContactOrg, so this box must remain checked."

Is this, or some workable similarity, possible within reason?

I've seen options for "After Update" to look at other checkboxes or fields in the same record, but nothing that goes to another record for info.

With thanks!!!!!!

--ak

[June7]

Domain aggregate functions could probably work. Try the checkbox BeforeUpdate event. Something like:

If Me.checkboxname = Yes Then 
   If Not IsNull(DLookup("checkfield", "contactstable", "checkfield = True AND organization=" & Me.orgID)) Then
      MsgBox "Primary contact already selected"
      Cancel = True
   End If
Else
   If DCount("contactID", "contactstable", "organization=" & Me!orgID)<=1 Then
   MsgBox "This is the only contact for org and must be primary."
   Cancel = True
End If

I avoid checkbox fields when I can. They can be tricky to build conditional code with. Running code when user voluntarily checks the checkbox control is easy enough but forcing user to deal with the checkbox is not easy. Yes/No fields don't have a Required property. Assuring that the field is correctly populated for a new organization and contact will be more complicated. In what event should code go - form BeforeUpdate?

[kelann]

Thanks, June!

That gave me a syntax error -- probably because of, like you said, checkboxes being funky. Would you suggest something different if I simply made it a lookup list with "Yes" and "No" choices?

[June7]

If you make it a text field with Yes/No options then the field can be set Required and user will be forced to deal with before leaving record. That might be easiest option.

[kelann]

Thank you again -- is there a way that I can do my message stuff -- if they already have a primary = yes for that org, then they are told there can only be one; and if they choose "no" and there is not yet a primary, then they must choose yes?

Thank you so much for your help -- I am definitely getting far beyond myself!!

[June7]

Try the BeforeUpdate event for textbox with suggested code.

If Me.textboxname = "Yes" Then

[kelann]

Thank you again! I hope to be able to spend most of tomorrow working on this. So much appreciated!

[kelann]

Hi, June!

I'm getting an error message at the line with the red asterisks -- Run-time error '2471' - The expression you entered as a query parameter produced this error: '[it inserts the value of ContactOrg for the given record]'


FIELDS

ContactsT

• ID (primary key)
• ContactOrg
• PrimaryContact (now a lookup list containing "Yes" and "No")

ContactsAddF

• ContactOrg
• PrimaryContact

YOUR SUGGESTION

If Me.checkboxname = Yes Then
If Not IsNull(DLookup("checkfield", "contactstable", "checkfield = True AND organization=" & Me.orgID)) Then
MsgBox "Primary contact already selected"
Cancel = True
End If
Else
If DCount("contactID", "contactstable", "organization=" & Me!orgID)<=1 Then
MsgBox "This is the only contact for org and must be primary."
Cancel = True
End If


MY CODE (changes in red) => substituting "real" names for what (I think) are intended as placeholders and text field options for checkbox field items with respct to PrimaryContact

If Me.PrimaryContact = Yes Then
If Not IsNull(DLookup("PrimaryContact", "ContactsT", "PrimaryContact = Yes AND ContactOrg=" & Me.ContactOrg)) Then
MsgBox "Primary contact already selected"
Cancel = True
End If
Else
If DCount("ID", "ContactsT", "ContactOrg=" & Me!ContactOrg)<=1 Then ***********************
MsgBox "This is the only contact for org and must be primary."
Cancel = True
End If
End If

Thank you so much for your thoughts!

[June7]

Are you still using YesNo field or did you change to text type?

If it is text and the field contains Yes or No then this is text and must be in quote marks.

Is the value of ContactOrg a number or text?

Want to provide db for analysis? Follow instructions at bottom of my post.

[kelann]

Thanks, June! Answers to your questions are in red:

Are you still using YesNo field or did you change to text type? Text -- dropdown with options I input ("Yes" and "No")

Is the value of ContactOrg a number or text? Text


Changes I made to "my" (hardly) code in the previous post are in red -- I used your questions as clues, did a bunch of googling, and the code works!!!!!!!

Jill, I can't tell you how much I appreciate your help!!

If Me.PrimaryContact = "Yes" Then
If Not IsNull(DLookup("PrimaryContact", "ContactsT", "PrimaryContact = ""Yes""AND ContactOrg=""" & Me.ContactOrg & """")) Then
MsgBox "Primary contact already selected"
Cancel = True
End If
Else
If DCount("ID", "ContactsT", "ContactOrg=""" & Me!ContactOrg & """")<=1 Then
MsgBox "This is the only contact for org and must be primary."
Cancel = True
End If
End If

[kelann]

Hi, all!

The code below been working perfectly -- I added tons of contacts last week, and the messages worked exactly as they should.

Today, I am getting the MsgBox regardless of my choice in the Primary Contact field.

I have made changes to the database, but none that would affect this -- nevertheless, I went back and confirmed all the field names.

Can you think of any reason it would not work now?

Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.PrimaryContact = "Yes" Then
   If Not IsNull(DLookup("PrimaryContact", "ContactsT", "PrimaryContact = ""Yes"" AND ContactOrg=""" & Me.ContactOrg & """")) Then
      MsgBox "This organization already has a primary contact.  There can be only one primary contact at an organization.  Please edit the other contact before designating this person as the primary contact."
      Cancel = True
   End If
Else
   If DCount("ID", "ContactsT", "ContactOrg=""" & Me.ContactOrg & """") <= 1 Then
   MsgBox "Each organization must have a primary contact.  This is the only contact for this organziation, so you must choose Yes."
   Cancel = True
End If
End If
End Sub

I double-checked the field names, and it's all good -- I hadn't changed any, but I checked nonetheless!

I am perplexed!

I am so close to being done ... and now ... an ugly wrinkle!

Thank you for any thoughts!

--ak

[June7]

I can only suggest you step debug. Review link at bottom of my post.

Or provide db for analysis. Instructions at bottom of my post.

Repeated quote marks will work to indicate text delimiter but I like apostrophe instead in sql strings, like:

"PrimaryContact = 'Yes' AND ContactOrg='" & Me.ContactOrg & "'"

[kelann]

June,

Thank you for scoping. I read the debug, and I don't know enough to understand it!!

I switched to single quotes, but no luck, so I reverted.

Thank you again!!

--ak

Copy (2) of Event.zip

[kelann]

I should add that *sometimes* when it's the first contact for an org, it will let you get by with Yes, but for adding additional contacts, it balks, and it doesn't like either Yes or No (or blank, for that matter).

[June7]

Can't get your file to download, just errors.

Apostrophe or quotes will work, matter of preference.