Hide Labels/Text Boxes based upon info in current record

[Bcanfield83]

Hello,

I have a form that is populated based upon a web-based form that users submit. There's an import process in the database that transfers the information from those submissions into a table.
The data source for the form is essentially a query that pulls over any "Active" submissions.
When the user submits this form, they can choose between a few different system types.
The form contains numerous Labels and Text Boxes for ALL possible submissions.. so I'm looking to hide them based upon what was selected on the current record.
For example, if the user selected "Application A", then I want any Labels/Text Boxes pertaining to Applications B, C, and D to be hidden.

Where I'm getting stuck is how to lookup the information. At any given time, there could be more than 1 active/open request submission - thus, the form would show multiple records.
Each record has a unique identifier (e.g.: a confirmation number).
Below is what I initially started.. But the DLOOKUP function isn't referencing the submission #. So let's suppose there's 5 open requests, thus 5 records in the form. The confirmation #'s are "145", "146", "147", "148", and "149".
Depending on which record is selected, the System Affected could be different. So if I'm on Record # 146 in the form, I may want different Labels/Text Boxes to be visible versus if I toggle to the next record (#147) in the form.

Not sure if this making sense, but any help is appreciated!

-------------------------------------------------------------------
Dim strReqestedApp As String


strReqestedApp = DLookup("[System_Affected]", "[qry_System_Changes_Open]")




If (strReqestedApp = "HRIS" Or strReqestedApp = "E1" Or strReqestedApp = "HRIS_NET") Then
Forms![frmSystem_Change_Requests].[lblENSReq].Visible = False
Forms![frmSystem_Change_Requests].[ENS_Request_Type].Visible = False
Forms![frmSystem_Change_Requests].[lblAppsecRole].Visible = False
Forms![frmSystem_Change_Requests].[ENS_Security_Role].Visible = False
Forms![frmSystem_Change_Requests].[lblENSApp].Visible = False
Forms![frmSystem_Change_Requests].[ENS_Application].Visible = False


ElseIf (strReqestedApp = "HRIS" Or strReqestedApp = "ENS" Or strReqestedApp = "HRIS_NET") Then
Forms![frmSystem_Change_Requests].[lblWex].Visible = False
Forms![frmSystem_Change_Requests].[MBI_Request_type].Visible = False
Forms![frmSystem_Change_Requests].[lblWexRole].Visible = False
Forms![frmSystem_Change_Requests].[E1_Security_Role].Visible = False
Forms![frmSystem_Change_Requests].[lblWexRole].Visible = False
Forms![frmSystem_Change_Requests].[E1_Request_Type].Visible = False
End If

[pbaldy]

Add a criteria:

http://www.theaccessweb.com/general/gen0018.htm

not sure why your ElseIf includes tests for values already tested for.

[isladogs]

A really easy method of doing this is by making use of the Tag property.
Assign the same tag value e.g. A, B etc to each group of controls you want to behave in the same way.

You then use code like this to show/hide groups of controls

ShowControls True, "A"
ShowControls False, "B","C"

You can also enble/disable, lock/unlock controls using similar commands

I have an example database which demonstrates the method at https://www.access-programmers.co.uk...d.php?t=293439

HTH

[Missinglinq]

At any given time, there could be more than 1 active/open request submission - thus, the form would show multiple records.

If, as the above sounds, you mean that the Form is displayed in Continuous View or Datasheet View, i.e. showing multiple Records on the screen, at the same time, there's really no way to make certain Controls invisible...on a Record by Record basis.

Is on of the above the case...or is the Form in Single View?

Linq ;0)>

[Bcanfield83]

If, as the above sounds, you mean that the Form is displayed in Continuous View or Datasheet View, i.e. showing multiple Records on the screen, at the same time, there's really no way to make certain Controls invisible...on a Record by Record basis.

Is on of the above the case...or is the Form in Single View?

Linq ;0)>

Yep, the default view is Single Form : )

[Bcanfield83]

Thanks to all who chimed in! I'm definitely on the right track with this now. Just need to tinker around with some things.

[Bcanfield83]

If, as the above sounds, you mean that the Form is displayed in Continuous View or Datasheet View, i.e. showing multiple Records on the screen, at the same time, there's really no way to make certain Controls invisible...on a Record by Record basis.

Is on of the above the case...or is the Form in Single View?

Linq ;0)>

Thanks - this was very helpful! Though I noticed in the EnableControls, LockControls, HideControls subs provided, it's being applied to any active form that's open.
Though I assume if I wanted to tweak this so that it only applied to this one specific form in my database, would it just be a matter of updating the 3 subs as follows:

'For Each ctrl In Screen.ActiveForm.Controls (Remove or comment this line out)
For Each ctrl In Forms!<MyFormName>

[isladogs]

You don't need to change anything.

The module code is Public so it is available for use in any active form.
But it still needs the ShowControls line in any form where you want to use it

In other words, if you only put that code line in one form, then it will only work in that form.
Also only one form can be active at a time anyway.

[Bcanfield83]

Using the Tag Property as ridders52 suggested, I was able to do what I sought out to do. I'm using the below code as part of the "OnCurrent" property. The string variables (e.g.: strAppType, strRequestType) are populated via DLookup function. I'm sure there's probably a more efficient way to do this, but it seems to be working : )

strAppType = DLookup("[System_Affected]", "[qry_System_Changes_Open]", _
"[Request_Number] = " & frmHRS0014s!txt0014ID)


strRequestTypePB = DLookup("[HRIS_Request_Type]", "[qry_System_Changes_Open]", _
"[Request_Number] = " & frmHRS0014s!txt0014ID)



strRequestTypeNet = DLookup("[HRIS_Net_Request_Type]", "[qry_System_Changes_Open]", _
"[Request_Number] = " & frmHRS0014s!txt0014ID)



strRequestTypeENS = DLookup("[ENS_Request_Type]", "[qry_System_Changes_Open]", _
"[Request_Number] = " & frmHRS0014s!txt0014ID)




strRequestTypeWex = DLookup("[E1_Request_Type]", "[qry_System_Changes_Open]", _
"[Request_Number] = " & frmHRS0014s!txt0014ID)

If strAppType = "ENS" Then


If strRequestTypeENS Like "Modify_Existing_User*" Then
ShowControls True, "User Info"
ShowControls True, "Default Info"
ShowControls True, "ENS"
ShowControls False, "Screen Info"
ShowControls False, "NewRole"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"
ShowControls False, "HRIS PB"
ShowControls False, "WexHealth"
ShowControls False, "HRIS.NET"

ElseIf strRequestTypeENS Like "Create*" Then
ShowControls True, "ENS"
ShowControls True, "Default Info"
ShowControls True, "NewRole"
ShowControls True, "NewRoleEns"
ShowControls False, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "HRIS.NET"
ShowControls False, "WexHealth"
ShowControls False, "HRIS PB"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"

Else:
ShowControls True, "ENS"
ShowControls True, "Default Info"
ShowControls False, "NewRole"
ShowControls True, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "HRIS.NET"
ShowControls False, "WexHealth"
ShowControls False, "HRIS PB"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"

End If


ElseIf strAppType = "HRIS_NET" Then
If strRequestTypeNet Like "Modify_Existing_User*" Then
ShowControls True, "User Info"
ShowControls True, "Default Info"
ShowControls True, "HRIS.NET"
ShowControls False, "Screen Info"
ShowControls False, "NewRole"
ShowControls False, "ENS"
ShowControls False, "WexHealth"
ShowControls False, "HRIS PB"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"

ElseIf strRequestTypeNet Like "Create*" Then
ShowControls True, "HRIS.NET"
ShowControls True, "Default Info"
ShowControls True, "NewRole"
ShowControls False, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "ENS"
ShowControls False, "WexHealth"
ShowControls False, "HRIS PB"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls True, "NewRoleNet"

Else:
ShowControls True, "HRIS.NET"
ShowControls True, "Default Info"
ShowControls False, "NewRole"
ShowControls True, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "ENS"
ShowControls False, "WexHealth"
ShowControls False, "HRIS PB"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"

End If

ElseIf strAppType = "HRIS" Then


If strRequestTypePB Like "Modify_Existing_User*" Then
ShowControls True, "HRIS PB"
ShowControls True, "User Info"
ShowControls True, "Default Info"
ShowControls False, "Screen Info"
ShowControls False, "NewRole"
ShowControls False, "ENS"
ShowControls False, "WexHealth"
ShowControls False, "HRIS.NET"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"

ElseIf strRequestTypePB Like "Create*" Then
ShowControls True, "HRIS PB"
ShowControls True, "Default Info"
ShowControls True, "NewRole"
ShowControls False, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "ENS"
ShowControls False, "WexHealth"
ShowControls False, "HRIS.NET"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls True, "NewRolePb"
ShowControls False, "NewRoleNet"

Else:
ShowControls True, "HRIS PB"
ShowControls True, "Default Info"
ShowControls False, "NewRole"
ShowControls True, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "ENS"
ShowControls False, "WexHealth"
ShowControls False, "HRIS.NET"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"

End If

ElseIf strAppType = "E1" Then


If strRequestTypeWeb Like "Create*" Then
ShowControls True, "WexHealth"
ShowControls True, "Default Info"
ShowControls True, "NewRole"
ShowControls False, "User Info"
ShowControls False, "ENS"
ShowControls False, "HRIS PB"
ShowControls False, "HRIS.NET"
ShowControls False, "Screen Info"
ShowControls False, "NewRoleEns"
ShowControls False, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"
Else:
ShowControls True, "WexHealth"
ShowControls True, "Default Info"
ShowControls False, "NewRole"
ShowControls True, "Screen Info"
ShowControls False, "User Info"
ShowControls False, "ENS"
ShowControls False, "HRIS PB"
ShowControls False, "HRIS.NET"
ShowControls False, "NewRoleEns"
ShowControls True, "NewRoleWex"
ShowControls False, "NewRolePb"
ShowControls False, "NewRoleNet"
End If

End If

[isladogs]

Using the Tag Property as ridders52 suggested, I was able to do what I sought out to do. I'm using the below code as part of the "OnCurrent" property. The string variables (e.g.: strAppType, strRequestType) are populated via DLookup function. I'm sure there's probably a more efficient way to do this, but it seems to be working : )

Several comments:
1. When you post long sections of code like the above, please use code tags (# button above this window) to make it more readable

2. The code is excessively long
I would be very surprised if you really need 12 or so tag groups to manage all your controls.
I use this code with some very complex forms of around 500 controls but I have less than 10 tag groups to manage them

All controls that behave the same should have the same tag value
Each tag value should normally include several controls
For example, you should have several controls with tag ENS, WexHealth etc etc

3. If you initially set all tag groups as false, then you only need to list the values that should be true for each case
That will make your code much shorter & therefore faster

4. Rather than use lots of If ...Else If ... Else ...End If sections, use Select Case instead.
It is easier to write, easier to read & runs more efficiently

So YES, it could be done much more efficiently
HTH

[Bcanfield83]

Thanks again - I'll use those suggestions and see if I can make it cleaner and more efficient.
Regarding #3 - "If you initially set all tag groups as false, then you only need to list the values that should be true for each case"
Potentially stupid question, but would that simply be done via the FormLoad and/or FormOpen event?

EDIT: it looks like you touched upon this here - which states that it can be set in the Form_Load event. : )

[isladogs]

Correct - use the Form_Load event to set the initial state of the form controls

[Bcanfield83]

Update - I tweaked the code to set the Initial State of Form Controls to False (except for "Default Info") in Form Load event:

SetControls True, "Default Info"
SetControls False, "User Info", "ENS", "HRIS PB", "HRIS.NET", "WexHealth"
SetControls False, "NewRole", "NewRoleENS", "NewRolePB", "NewRoleWex", "NewRoleNet"
SetControls False, "Screen Info", "Screen Info Net", "Screen Info ENS", "Screen Info PB"

I also changed the lengthy IF..THEN statements to SELECT..CASE like below.. Though if anything, the form seems to be a little bit slower after updating it to the below..?

Select Case strAppType
    Case "ENS"
       ShowControls True, "ENS"
       Select Case strRequestTypeENS
            Case "Modify_Existing_User_Add", "Modify_Existing_User_Remove"
            ShowControls True, "User Info"
            Case "Create_New_Group"
            ShowControls True, "NewRole"
            ShowControls True, "NewRoleEns"
            Case Else
            ShowControls True, "Screen Info"
        End Select
    Case "HRIS"
        ShowControls True, "HRIS PB"
        Select Case strRequestTypePB
            Case "Modify_Existing_User_Add_Role", "Modify_Existing_User_Remove_Role"
            ShowControls True, "User Info"
            Case "Create_New_Group"
            ShowControls True, "NewRole"
            ShowControls True, "NewRolePb"
            Case Else
            ShowControls True, "Screen Info"
        End Select
    Case "HRIS_NET"
        Select Case strRequestTypeNet
            Case "Modify_Existing_User_Add_Role", "Modify_Existing_User_Remove_Role"
            ShowControls True, "User Info", "HRIS.NET"
            Case "Create_New_Role"
            ShowControls True, "NewRole", "HRIS.NET"
            ShowControls True, "NewRoleNet", "HRIS.NET"
            Case Else
            ShowControls True, "Screen Info", "HRIS.NET"
        End Select
    Case "E1"
        ShowControls True, "WexHealth"
        Select Case strRequestTypeWex
            Case "Create_New_Role"
            ShowControls True, "NewRole"
            Case Else
            ShowControls True, "Screen Info"
        End Select
End Select
'
'Select Case strRequestTypeENS
'    Case "Modify_Existing_User*"
'    ShowControls True, "User Info"
'End Select

[isladogs]

You need to change each line in the first section of code from SetControls to ShowControls

[Bcanfield83]

Update - I've utilized this technique (e.g.: tying multiple controls to a specific "Tag", and then setting the initial state of those controls on the Form_Load event for my form) on a few forms in the database.
But for some reason, it only seems to work if the code is only referencing that specific form. So right now I've got the same "ShowControls" code in 3 different forms (with a slightly different name for each one).
The only difference is that instead of stating the below:

Public Sub ShowControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)
For Each ctrl In Screen.ActiveForm.Controls

I use the following:

For Each ctrl In Forms!MyFormName_1

When I try using the Public ShowControls procedure (which applies to Screen.ActiveForm.Controls instead of referencing a specific form name), I receive an error message which is usually "You can't hide a control that has the focus" though I've also run into "Error 2475 You entered an expression that requires a form to be the active window".

Any idea what would keep causing it to error out? Below is the entire ShowControls code which I "borrowed" from the example database that ridders52 provided (https://www.access-programmers.co.uk...d.php?t=293439)

Public Sub ShowControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)


On Error GoTo Err_Handler


    For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acPageBreak
            'no code here - these can't be locked
        
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Visible = State
        
        End Select
    Next ctrl
  
Exit_Handler:
    Exit Sub


Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_Handler
    
End Sub