Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: Conditional Visibility

    Hi, I am quite new to access so can someone possibly help with a fundimental problem I cant seem to solve.

    I have a combo box with options, dependant on which option is chosen I want to make a textbox visible.

    This is where I am now and cant seem to get it to work.
    Private Sub Combo3_AfterUpdate()
    If Me.Combo3 = "On" Then
    Me.textbox2.Visible = True
    Else
    Me.textbox2.Visible = False
    End Sub

    Thanks in anticipation.
    Alan

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The code looks fine, but its in the wrong place
    the after update event fires after an update is completed
    place the code in a function
    call that function from
    various events in the combo box
    the on click event
    the on change event
    and arguably the on lost focus event
    you also need to set the visibility when a row changes so place a call to the the code in the on current event

    ...its probably worthwhile understanding what evcents are, when they fire and the precedence.
    Google
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Posts
    4
    Thanks for the advice but now poses another question. Function call and calling from various events and how it works is a mystery.

    Forgive my novice like questions, working to improve.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    write a function that encapsulates the behaviour
    VB/VBA supports two variants
    a functiuon which returns a value
    a sub routine which doesn't
    ..in this case I'd suggest using a sub routine

    Code:
    private sub SetTextBox2Visibility()
    If Me.Combo3.value = "On" Then ' could be Me.Combo3.text
      Me.textbox2.Visible = True
    Else
      Me.textbox2.Visible = False
    endif
    end sub
    then in the relevant events call that subroutine
    you can use
    Code:
    SetTextBox2Visibility
    or
    Code:
    call SetTextBox2Visibility()
    BTW make life easier for ourself by giving all controls, forms, tables, variable meaningful names. you don't have to accept the default values given by Access
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post

    The code looks fine, but its in the wrong place ...the after update event fires after an update is completed
    When would you want it to occur other than after the update to the Control is completed? This kind of thing is done all the time using the Control's AfterUpdate event! The OP doesn't have the code in the Form_AfterUpdate event, but rather in the AfterUpdate of the Combobox!

    And what's the justification in placing the code in the OnClick, OnChange and the OnLostFocus events?

    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
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its about user feedback
    put the code in the after updfate and it doesnt' trigger untill the row is updated
    put it in the on click/ on change event it occurs as soon as the state is changed.
    as this toggels visibility of a textbox then it ought to be whilst the rest of the row is visible of redit. putting the code in the after update event means you do somework, update the row, then change something and then redit the row.
    its an uneccesary step
    it doens't fit with a workflow
    toggling the visibili9ty of the text box to me means that you need to set/unset a value int he text box, so I'd expectr the user to make thise changes as part of the current edit session, NOT to complete the edit, then re edit the row the commit the changes.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post

    ...put the code in the after updfate and it doesnt' trigger untill the row is updated
    This statement is simply not true! Once again, please look at the Op's original code!

    Private Sub Combo3_AfterUpdate()
    If Me.Combo3 = "On" Then
    Me.textbox2.Visible = True
    Else
    Me.textbox2.Visible = False
    End Sub
    His code is in the AftertUpdate event of Combo3, it is not in the Form_AfterUpdate event! The code will execute as soon as a selection is made from the Combobox, if the selection is "On!" It does not wait to execute until after the row is updated! It doesn't!

    The only thing wrong in the OPs original code is that he did not have the closing End If for his If...Then construct, which would throw an error, which he should have mentioned, and that it also needed to be, as you mentioned, in the Form_Current event.

    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
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From "Access 2003 VBA Language Reference" (AfterUpdate Event [Access 2003 VBA Language Reference]
    ...
    The AfterUpdate event is triggered when a control or record is updated. Within a record, changed data in each control is updated when the control loses the focus or when the user presses ENTER or TAB. When the focus leaves the record or if the user clicks Save Record on the Records menu, the entire record is updated, and the data is saved in the database.
    ...
    Have a nice day!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so it looks like the rumours are false...

    ..you can teach an old dog new tricks
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's what I love about Access! No matter how long you've been using it, there's always something new to learn! Don't remember the details, but I actually taught God (aka Allen Browne) something, once upon a time!

    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

  11. #11
    Join Date
    Sep 2012
    Posts
    4
    Hi Guys, Thanks for all your input and debating the issue resulting in me being as confused slightly more now.
    Could someone please clarify just how and why this works and sample code if possble.

    Thanks for all your help.
    Alan

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry! We did get sidetracked!

    As I said in Post #7, your original code, assuming that the names of the Controls are correct, is valid except, you do not have a closing End If statement! You should have gotten an error statement that said that. Adding that one line should change the the Visibility of textbox2 immediately, if 'No' is selected from the Combo3 Combobox.
    Code:
    Private Sub Combo3_AfterUpdate()
     If Me.Combo3 = "On" Then
      Me.textbox2.Visible = True
     Else
      Me.textbox2.Visible = False
     End If
    End Sub


    To maintain the appropriate formatting, as you move from Record-to-Record, the same code must also be in the Form_Current event:

    Code:
    Private Sub Form_Current()
     If Me.Combo3 = "On" Then
      Me.textbox2.Visible = True
     Else
      Me.textbox2.Visible = False
     End If
    End Sub


    Note that this code will only work on a Single View Form. Formatting, on a Continuous View or Datasheet View Form has to be done through Conditional Formatting, and the Visibility Property cannot be controlled through CF. There are some workarounds, but they are complicated and do not, in my opinion, give a satisfactory nor professional appearance, involving using Textboxes without Borders, among other things.

    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

  13. #13
    Join Date
    Sep 2012
    Posts
    4
    Hi, appreciate the clarification.

    Thanks for your help.

    Alan.

Posting Permissions

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