Solved

How hide fields on a form if their tag value is set to "Hide"

Posted on 2016-08-30
14
19 Views
Last Modified: 2016-08-30
I have several fields on a form that have tag set to "Hide" (without the quote marks)

If the selected value in a combobox on the form is <> 2 then I want all those fields to be hidden.

Can this be done?
0
Comment
Question by:SteveL13
  • 7
  • 5
  • 2
14 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
so, you would need to use the combo boxes AfterUpdate event, like:

Private Sub cboYourComboName_AfterUpdate

    Dim ctrl as control

    On Error Resume next
    for each ctrl in me.controls
        if ctrl.Tag = "Hide" then ctrl.visible = (me.cboYourComboName<>2)
    Next

 End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
This expert suggested creating a Gigs project.
This is code from one of my apps that locks/unlocks controls based on security.  I didn't alter it to suit your purpose because you might find it useful for its original function.  Use the same concept to hide/show.  To call this code use

Call LockControls (Me, True)

In your case instead of the boolean, use an integer if you prefer and instead of locking the control hide or show it.

ctl.Visible = True  'to show

or

ctl.Visible = False   ' to hide


Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acOptionGroup, acOptionButton, acCheckBox      ''not working
             Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Open in new window

0
 

Author Comment

by:SteveL13
Comment Utility
Neither suggestion seem to be working.  I'm sure I am doing something wrong.  If I change the combobox selection to something other than 2 the fields are hidden which is what I would expect.  But if I change it back to 2 they are still hidden.

Here is what I have in the after update event of the combobox:

    If Me.cboPrinterID2 = 2 Then
        Call HideControls(Me, True)
    End If

Open in new window



And here is the code being called:

Public Sub HideControls(frm As Form, bLock As Integer)
    
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox   ', acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "Hide"
                    ctl.Visible = False
                Case Else
                    ctl.Visible = True        'toggle locks
            End Select
            
            
        Case acLabel   ', acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "Hide"
                    ctl.Visible = False
                Case Else
                    ctl.Visible = True        'toggle locks
            End Select
            
            
        Case acCommandButton   ', acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "Hide"
                    ctl.Visible = False
                Case Else
                    ctl.Visible = True        'toggle locks
            End Select
            
            
        Case acLine    ', acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "Hide"
                    ctl.Visible = False
                Case Else
                    ctl.Visible = True        'toggle locks
            End Select
            

    End Select
    
    Next ctl
    Set ctl = Nothing
    
End Sub

Open in new window

0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
Comment Utility
I forgot to mention that for records which already exist, you will need to call this afterupdate event from the Form_Current event:

Private sub Form_Current

    Call cboPrinterID2_AfterUpdate

End Sub

Open in new window

I still think this should work for you:

Private Sub cboPrinterID2_AfterUpdate

    Dim ctrl as control

    On Error Resume next
    for each ctrl in me.controls
        if ctrl.Tag = "Hide" then ctrl.visible = (NZ(me.cboPrinterID2, 0)<>2)
    Next

 End Sub

Open in new window

This should loop through each control on your form and determine whether the tag property of that control is "Hide".  If it is, then it will set the Visible property of the control to True when the value of your combo box is <> 2 and False when it is = 2.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
I should have explained the locking procedure in more detail so you could translate it better.

It is always called, regardless of the security setting.  The Boolean is used to specify lock or unlock.  In the code, the Lock, Unlock are used to override the boolean.  For example, you might want to lock the fields on a particular form to prevent the user from changing anything but if you use the AllowUpdates = No setting, that also locks any search combos and buttons.  So, you would call the code with True for the boolean value.  Then at the individual control level, the Tag is examined, if the Tag is Lock, the control is ALWAYS locked (use this for fields that are generated and cannot be updated such as ChangeByUser and the Autonumber control).  If the Tag says Unlock, that means that regardless of the boolean setting, this control should always be unlocked.  It is the third option that toggles the setting for the untagged controls.  True = lock, False = unlock.  The code doesn't require that you remember to set the Tag property for every control.  You only have to set the Tag property for controls that are the exception.

In your case, use the boolean to set the visible value.

If you don't need any overrides - and you don't for your simple procedure, then - remove the If statements and just use the boolean.
    If Me.cboPrinterID2 = 2 Then
        Call HideControls(Me, True)
     Else 
         Call HideControls(Me, False)
    End If

Open in new window

Public Sub HideControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox            
                    ctl.Visible = bLock         'toggle Visible
    End Select
Next ctl
Set ctl = Nothing
End Sub

Open in new window


I removed the excess code since the visible property works the same way on all controls unlike the locked property.
0
 

Author Comment

by:SteveL13
Comment Utility
Pat, I'm getting an error "You can't hide a control that has the focus".

Dale:  I had to change the <> to = and then it worked.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You should get the same error with Dale's code.  As the first step in the procedure, move the focus to some control you know you are not going to hide.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:SteveL13
Comment Utility
Pat,  I will try that but am definitely not getting the error with Dale's code.  Back to you soon.
0
 

Author Comment

by:SteveL13
Comment Utility
Pat.  I just tried setting the focus to a control that I know should not become hidden and all controls disappeared.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
It is doing the same thing.  The pieces of code are equivalent.  I just gave you code I had for a different use to repurpose for hiding rather than locking.  Dale wrote the code from scratch.  Put the cursor in the same field before running each procedure.  You should get the same error.
0
 

Author Comment

by:SteveL13
Comment Utility
Pat,

I can see what you have proposed but I still get same result with your solution.   All fields disappear when I select 2 from the combobox.
0
 

Author Comment

by:SteveL13
Comment Utility
And here is the after update event:

    If Me.cboPrinterID2 = 2 Then
        Call HideControls(Me, True)
     Else
         Call HideControls(Me, False)
    End If

Open in new window


And here is the code:

Public Sub HideControls(frm As Form, bLock As Boolean)

    Dim ctl As Control
    Me.cmdCloseForm.SetFocus
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            ctl.Visible = bLock         'toggle Visible
    End Select
Next ctl
Set ctl = Nothing

End Sub

Open in new window

0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Sorry, I forgot that you only want to work with the tagged controls.

Public Sub HideControls(frm As Form, bLock As Boolean)

    Dim ctl As Control
    Me.cmdCloseForm.SetFocus
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            If ctl.Tag = "Hide" Then
                ctl.Visible = bLock         'toggle Visible
            End If
    End Select
Next ctl
Set ctl = Nothing

End Sub

Open in new window

0
 

Author Comment

by:SteveL13
Comment Utility
Pat,

That did it.  Thanks for the patience.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
employee anniv date with text field type 9 22
Access MDB/PDF 21 28
Recording mileage on a form 2 11
Error in SQL Query 36 33
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now