Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Question Unanswered: Make field visible based on tickbox

    Hi all,

    I have looked at previous posts/solutions and can't seem to get this simple thing to work.

    I have a form with a tickbox and a couple of related fields on it, amongst a whole lot of other fields. I want the related fields to be greyed out until the tickbox is checked (indicating that the tickbox and related fields are relevant for this record).

    I know that I need an event procedure for this but all the ones I have tried don't seem to work.

    The tickbox fieldname is HasCamera and the related fields are CamCode_1 and CamCode_2

    Does anyonw know how to have this happen and in which property should I place the event procedure?

    Thanks in advance for any help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Private Sub HasCamera_AfterUpdate()
    
        Me.CamCode_1.Enabled = Me.HasCamera.value
        Me.CamCode_2.Enabled = Me.HasCamera.value
        
    End Sub
    If HasCamera is bound, you'll need to add the same lines in the Form_Current event handler.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code like
    Code:
     Me.CamCode_1.Enabled = Me.HasCamera.value
     Me.CamCode_2.Enabled = Me.HasCamera.value
    has become very popular, as a way to avoid using the If...Then construct. I dislike it for two reasons:

    • While it is shortcut, it tends to make what the code is doing obscure. In the days of yore, when memory was precious, this kind of thing was encouraged. With memory as cheap as it is today, I believe it is more important to make code clear to understand, rather than to make it take up as little space as possible.
    • The second reason I hate, is that it doesn't work! It has to be in the Form_Current event, in order to be Record-specific when the user moves from Record-to-Record, and this type of code does not take into account the fact that in a New Record the Checkbox will be Null initially, and Me.CamCode_1.Enabled = Null is not valid and will pop an 'Invalid use of Null' error!

    While you could use the code in the AfterUpdate event of the Checkbox, as ticking or ticking/unticking it will return -1 or 0, I prefer to use the same code for both events:
    Code:
    Private Sub HasCamera_AfterUpdate()
     If Me.HasCamera.Value = -1 Then
       CamCode_1.Enabled = True
       CamCode_2.Enabled = True
     Else
       CamCode_1.Enabled = False
       CamCode_2.Enabled = False
     End If
    End Sub
    
    Private Sub Form_Current()
       If Me.HasCamera.Value = -1 Then
       CamCode_1.Enabled = True
       CamCode_2.Enabled = True
     Else
       CamCode_1.Enabled = False
       CamCode_2.Enabled = False
     End If
    End Sub
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Missinglinq View Post
    ... While it is shortcut, it tends to make what the code is doing obscure. In the days of yore, when memory was precious, this kind of thing was encouraged. With memory as cheap as it is today, I believe it is more important to make code clear to understand, rather than to make it take up as little space as possible.
    Except that a CheckBox actually returns a value which can be True (-1) or False (0). From MSDN: Value Property [Access 2003 VBA Language Reference]
    The Value property uses the following setting depending on the specified control.
    True The check box is selected. (Visual Basic: True)
    False (Default) The check box is cleared. (Visual Basic: False)
    So the code you suggested actually should be written:
    Code:
    Private Sub HasCamera_AfterUpdate()
     If Me.HasCamera.Value = True Then
       CamCode_1.Enabled = True
       CamCode_2.Enabled = True
     Else
       CamCode_1.Enabled = False
       CamCode_2.Enabled = False
     End If
    End Sub
    
    Private Sub Form_Current()
       If Me.HasCamera.Value = True Then
       CamCode_1.Enabled = True
       CamCode_2.Enabled = True
     Else
       CamCode_1.Enabled = False
       CamCode_2.Enabled = False
     End If
    End Sub
    Which seems totally verbose and redundant to me (there's a feature in Mathematics called Transitivity about that). And it's never been a question of using more or less memory (which was a concern in the MS-DOS era or sooner, never under Windows).

    Quote Originally Posted by Missinglinq View Post
    ... The second reason I hate, is that it doesn't work! It has to be in the Form_Current event, in order to be Record-specific when the user moves from Record-to-Record, and this type of code does not take into account the fact that in a New Record the Checkbox will be Null initially, and Me.CamCode_1.Enabled = Null is not valid and will pop an 'Invalid use of Null' error!
    That's precisely why I wrote:
    Quote Originally Posted by Sinndho View Post
    If HasCamera is bound, you'll need to add the same lines in the Form_Current event handler.
    Have a nice day!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Sinndho View Post
    ...Which seems totally verbose and redundant to me...
    What you call 'verbose and redundant' I call clarity.

    Quote Originally Posted by Sinndho View Post
    ...That's precisely why I wrote:

    "If HasCamera is bound, you'll need to add the same lines in the Form_Current event handler....
    I know that you addressed the issue of having the code in the Form_Current event, but using your original code
    Code:
    Me.CamCode_1.Enabled = Me.HasCamera.value
    Me.CamCode_2.Enabled = Me.HasCamera.value
    in the Form_Current event will cause an error because the Value of a Checkbox on a New Record is Null, until the Checkbox is ticked or the Record is saved, and

    Me.CamCode_1.Enabled = Null


    is not valid code. It is the 'invalid use of Null.'

    By the way, the use of -1 instead of True, relative to a Checkbox Value, is preferred by many experienced developers because Access sometimes balks at True.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Thumbs up

    Hello Sinndho and Missinglinq

    Thank you both very much for your help. The discussion on programming style was very enlightening. Talk about more than one way to skin a cat!

    In the end I decided to implement the more verbose solution simply because one day I will hand this project to some other Newbie and I think it will make it easier for then to follow and understand what is going on.

    Once again much thanks and a Happy New Year to you both, and other members of the forum!

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by sheusz View Post
    ..In the end I decided to implement the more verbose solution simply because one day I will hand this project to some other Newbie and I think it will make it easier for then to follow and understand what is going on.
    ...
    My point exactly!

    And a Happy New Year to you and Sinddho!
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Aug 2012
    Posts
    2
    Can you help me please? I had the same problem, so thank you for the answers But I have one more problem whit this: If I uncheck the tickbox, write something into the field, that is now enabled, and after that I uncheck the tickbox, the field becomes gray, but it shows the typed data.

    I want that the programs indicates (with a message box) if the user unchecks the tickbox, but the field has data.

    Have you any ideas for me?

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You can't use Sinndho's shortcut code, for this situation, but rather have to use the longer code like I gave in Post #3 (the second code shown in that post) and modify it slightly:
    Code:
    Private Sub HasCamera_AfterUpdate()
     If Me.HasCamera.Value = -1 Then
       CamCode_1.Enabled = True
     Else
       Me.CamCode_1 = Null
       CamCode_1.Enabled = False
     End If
    End Sub
    
    
    Private Sub Form_Current()
     If Me.HasCamera.Value = -1 Then
       CamCode_1.Enabled = True
     Else
       CamCode_1.Enabled = False
     End If
    End Sub

    The line of code

    Me.CamCode_1 = Null

    removes any data that has been entered in the Disabled Control, before Disabling it.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Aug 2012
    Posts
    2

    Thumbs up

    Thank you! It works great!

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Oct 2012
    Posts
    2
    Quote Originally Posted by Sinndho View Post
    Except that a CheckBox actually returns a value which can be True (-1) or False (0). From MSDN: Value Property [Access 2003 VBA Language Reference]

    So the code you suggested actually should be written:
    Code:
    Private Sub HasCamera_AfterUpdate()
     If Me.HasCamera.Value = True Then
       CamCode_1.Enabled = True
       CamCode_2.Enabled = True
     Else
       CamCode_1.Enabled = False
       CamCode_2.Enabled = False
     End If
    End Sub
    
    Private Sub Form_Current()
       If Me.HasCamera.Value = True Then
       CamCode_1.Enabled = True
       CamCode_2.Enabled = True
     Else
       CamCode_1.Enabled = False
       CamCode_2.Enabled = False
     End If
    End Sub
    Which seems totally verbose and redundant to me (there's a feature in Mathematics called Transitivity about that). And it's never been a question of using more or less memory (which was a concern in the MS-DOS era or sooner, never under Windows).


    That's precisely why I wrote:
    In desperate need of help with this. We are trying to accomplish something very similar. The following code:

    Private Sub HasPet_AfterUpdate()
    If Me.HasPet.Value = -1 Then
    PetType.Visible = True
    PetNum.Visible = True

    Else
    Me.HasPet = 0
    PetType.Visible = False
    PetNum.Visible = False
    End If
    End Sub

    Private Sub Form_Current()
    If Me.HasPet.Value = -1 Then
    PetType.Visible = True
    PetNum.Visible = True
    Else
    PetType.Visible = False
    PetNum.Visible = False
    End If
    End Sub

    works fine until we save the database. When we reopen the db the PetType and PetNum fields are visible and will not hide when the check box is empty.

    Any assistance / explanation with this would be appreciated. Thanks

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Are the controls HasPet, PetType and PetNum bound or unbound?

    2. If they are bound, can the related columns contain Null values?
    Have a nice day!

  14. #14
    Join Date
    Oct 2012
    Posts
    2
    The controls are bound. If the checkbox is not checked then the PetType and PetNum are null. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •