Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132

    Checkbox Validation


    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Domain aggregate functions could probably work. Try the checkbox BeforeUpdate event. Something like:
    Code:
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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!!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Try the BeforeUpdate event for textbox with suggested code.

    If Me.textboxname = "Yes" Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Thank you again! I hope to be able to spend most of tomorrow working on this. So much appreciated!

  8. #8
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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!

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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

  11. #11
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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:

    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

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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 & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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

  14. #14
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    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).

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Can't get your file to download, just errors.

    Apostrophe or quotes will work, matter of preference.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Checkbox
    By Mantaii in forum Forms
    Replies: 3
    Last Post: 01-13-2012, 07:17 AM
  2. Checkbox
    By huongdl1987 in forum Forms
    Replies: 1
    Last Post: 06-23-2011, 05:29 PM
  3. Checkbox help
    By NateHaze in forum Programming
    Replies: 3
    Last Post: 05-26-2011, 02:50 PM
  4. Checkbox
    By Rbtsmith in forum Access
    Replies: 2
    Last Post: 02-17-2009, 04:19 PM
  5. checkbox
    By Suresh in forum Forms
    Replies: 0
    Last Post: 12-19-2007, 01:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums