Results 1 to 12 of 12
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Need help with 2 Yes/No fields on form only one can be checked at a time

    I have 2 Yes/No fields on a form: Forecast and BackUp. If forecast is checked and they try to check BackUp I need a message that says only one can be checked at a time.



    I thought it might be an If statement in the After Update Event of each field like
    Code:
    If Forecast = "Yes" Then
    MsgBox "Only one can be checked at a time"
    Else: 
    End If
    But I cannot seem to get the Else to work as expected - basically if the other field is not Yes then let them check the box to set it to Yes.

    Can you help with this part of the statement - if this is even the right way to go. I need to store each value in separate field which is why I am going this way and not a group or toggle.

    Thanks as always

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If only 1 option is available at a time, you might consider an Option group. It depends on your details to see if it is applicable.

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    That appears to be exactly what I need.

    Thank you so much for the quick reply

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with the suggestion, adding that with the first approach, you're assuming no one would ever click one by accident, meaning an error is being locked in. Another possible solution is to just cycle the selection like this:
    Code:
    Private Sub Check0_Click()
    Me.Check2 = Not Me.Check0
    End Sub
    
    Private Sub Check2_Click()
    Me.Check0 = Not Me.Check2
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Both of these are great ideas but Micron brought up a good point. What if they click it by accident. I have tried both solutions provided but neither allow me to uncheck the checked option so that both checkboxes are blank.

    Right now if I click forecast , backup unchecks and if I click backup forecast unchecks. But what if I check forecast by accident and just want to uncheck forecast but not have backup checked.

    Is there a way to do that?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just thinking and typing, but if users are likely to choose an option by mistake, you could
    -capture the option they selected
    -issue a command to get confirmation of their selection
    -then proceed
    if they confirm, carry on
    if the say that was a mistake, then select the other option
    ( I don't have code for this, but that's the logic I foresee.)

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe something like:

    In the OnClick event of Checkbox1:
    If Me.Checkbox1 = True Then Me.Checkbox1 = False
    Me.Checkbox2 = False

    In the OnClick evemtof Checkbox2:
    If Me.Checkbox2 = True Then Me.Checkbox2 = False
    Me.Checkbox2 = False

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    busy thread while I was gone!
    I wanted to test this with the BeforeUpdate event to see what cancelling would do to the checkbox, assuming that would be the way to go. To my surprise, the checking can be cancelled, but the checkbox assumes the value of -1 (True), on subsequent execution of the event even when it is not checked on the form! Anyway, my code resembles that of Bulzie's but I split it into a function, assuming the OP would like to learn how to use one for multiple calls that basically do the same thing. Note that the code also demonstrates how to pass the actual control to the function, not just the value of the control. This would allow the function to access any property of the control.
    Code:
    Private Sub Check0_AfterUpdate()
    If IsChecked(Me.Check2) = True Then Me.Check0 = False
    End Sub
    
    Private Sub Check2_AfterUpdate()
    If IsChecked(Me.Check0) = True Then Me.Check2 = False
    End Sub
    
    Private Function IsChecked(ctl As Control) As Boolean
    Dim strMsg As String
    
    IsChecked = False
    strMsg = "Only one box can be checked." & vbCrLf & "Deselect other checkbox first"
    If ctl = True Then
        MsgBox strMsg
        IsChecked = True
    End If
    End Function
    In truth, the = True part is not really necessary
    Last edited by Micron; 03-28-2017 at 02:03 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Click image for larger version. 

Name:	Capture1.JPG 
Views:	17 
Size:	60.6 KB 
ID:	28047I have been working with the function provided by Micron but I keep getting an error. Googling it I have tried a few things but I can't seem to get around the error.

    I get runtime error '-2147352567 (80020009)' The Macro or function set to the BeforeUpdate or Validate Rule property for this field is preventing Microsoft Access from saving the data in the field.

    When I hit debug it takes me to a spot in the first private sub - I have attached a screenshot.

    Any ideas?

  10. #10
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    And I have tried Bulzie's option and it works except once you have selected an option you cannot unselect it. In the case where the rep clicks it by accident and really wants both checkboxes unchecked

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try using the AfterUpdate event as I suggested. You're trying to alter a control value after the user has made the change, but before Access applies the change. That often results in this conflict and error message.

  12. #12
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    That was it!!!! Thank again. I really appreciate it. I totally missed that - had before update on the brain

Please reply to this thread with any new information or opinions.

Similar Threads

  1. When check box is checked, more fields appear
    By lonesoac0 in forum Programming
    Replies: 1
    Last Post: 05-08-2016, 05:21 PM
  2. Replies: 7
    Last Post: 03-17-2016, 10:42 AM
  3. Replies: 5
    Last Post: 07-24-2014, 07:54 AM
  4. Checked Box + Date = Checked box
    By ItsATJ in forum Access
    Replies: 12
    Last Post: 09-03-2013, 10:25 AM
  5. Counting only 'checked' Yes/No fields on a
    By pwdpwd in forum Programming
    Replies: 9
    Last Post: 04-14-2011, 09:28 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