none
Hiding fields within forms and subforms conditionally RRS feed

  • Question

  • I have been perplexed in regards to hiding fields.

    Here I have the following code snippet:

    Option Compare Database
    Dim Sfrm As [Form_SubFormOne subform]
    
    Private Sub BaseClassPublicRoutineLineNumbersQuery_subform_Enter()
    
    End Sub
    
    Private Sub Detail_Click()
    
    End Sub
    
    Private Sub Form_AfterUpdate()
    
    If Sfrm.CalculationID <> Null Then
        Sfrm.CalculationID.Visible = True
    Else
        Sfrm.CalculationID.Visible = False
    End If
    
    End Sub
    

    When I tried to scroll down the records, the sub form remained unchanged; therefore, the CalculationID field is still visible.

    How could I correct this code snippet?

    Regards,

    JohnDBCTX

    Saturday, October 22, 2016 5:24 AM

All replies

  • Copy the code from the Form_AfterUpdate to the Form_Current event. The Form_Afterupdate event will only be executed if you have modified the data in the form.

    Matthias Kläy, Kläy Computing AG

    Saturday, October 22, 2016 6:16 AM
  • If Sfrm.CalculationID <> Null Then
        Sfrm.CalculationID.Visible = True
    Else
        Sfrm.CalculationID.Visible = False
    End If
    
    

    Hi John,

    I do not understand.

    If you compare a value (Sfrm.CalculationID) with un andefined value (Null), the result is always undefined, thus never True. I would expect the Visible property being False after an update of the control.

    To check for a Null-value you can use the  IsNull function.

    Imb.

    Saturday, October 22, 2016 6:52 AM
  • The Connecting sub form is from a query.

    I guess Access won't allow me to hide fields in a connected sub form query.  Right?

    For whatever reason.

    Regards,

    JohnDBCTX

    Saturday, October 22, 2016 7:37 AM
  • I guess Access won't allow me to hide fields in a connected sub form query.  Right?

    Hi John,

    That is not my experience.

    Imb.

    Saturday, October 22, 2016 8:36 AM
  • Normally, I'd do something along the line of:

    Public Sub Form_AfterUpdate()
        On Error GoTo Error_Handler
        Dim Sfrm                  As [Form_SubFormOne subform]
    
        Set Sfrm = Me.[SubFormOne subform]
        If IsNull(Sfrm.CalculationID) = False Then
            Sfrm.CalculationID.Visible = True
        Else
            Sfrm.CalculationID.Visible = False
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not Sfrm Is Nothing Then Set Sfrm = Nothing
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: " & sModName & "\Form_AfterUpdate" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub
    
    Private Sub Form_Current()
        On Error GoTo Error_Handler
    
        Call Me.Form_AfterUpdate
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: " & sModName & "\Form_Current" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub

    You'll notice that I placed all the code in the after update event and simple call that event in the current event.  It is simpler and means there is only one place to make changes when required.

    Also note that when you Dim a variable as an Access object as you do in the line

    Dim Sfrm As [Form_SubFormOne subform]

    you still need to set the variable in your code (and cleanup after it at the end).

    Don't forget error handling.

    Lastly, it's recommended to avoid using space and other special characters when naming objects, fields, controls, ...  Using them causes useless pains!


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Saturday, October 22, 2016 11:31 AM
  • You need to change the Default View from Continous to Single Form in order for the code to work. The Visible and Enabled properties only work in Single Form format for individual records.
    Saturday, October 22, 2016 1:16 PM
  • If you using a continues form, then if you hide (or enable) a control then it applies to all rows.

    However, often the above works quite well. The fact that ALL columns enable + disable when you are editing a single row of data actually HELPS VERY much from a “UI” and visual cue point of view. In other words the brain will instantly connect that “many” rows of visual input to the ONE row a person is editing.

    In fact from a UI point of view, the brain rather likes this, since while viewing the screen you get a great visual input as to if the column you are working on is enabled, or disabled. In fact the visual cue is better than a checkerboard pattern that would occur “if” one could actually hide and show individual controls.

    While one could hide column and get a great effect, enable/disable also works quite well. In fact what I am saying is to embrace how all rows will give feedback to the user during editing.

    Here is an animated gif of such a form in action - note how clear and “loud” the visual feedback is for the disabled columns when the user changes a record:

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    kallal@msn.com

    Saturday, October 22, 2016 8:17 PM
  • If you using a continues form, then if you hide (or enable) a control then it applies to all rows.


    That's not completely true.  You can use conditional formatting to enable or disable a control on a row by row basis.  So while Albert makes a good case for the visual impact of enabling or disabling the whole column based on the data in the current row, you don't have to do that.  You can enable/disable just the copy of the control in the current row.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Sunday, October 23, 2016 8:21 PM
  • It is possible, with a little trickery, to hide a control in just the current row of a continuous form.  This trick involves placing another text box on top of the control you want to hide, with properties set along these lines:

        Back Style : Transparent
        Back Color : No Color
        Border Style : Transparent
        Border Color : No Color
        Special Effect  : Flat
        Font Name: Terminal
        Font Size : (a suitable value to fill the text box)
        Fore Color: (the same color as the detail section's Back Color)
        Control Source: =IIf(IsNull(Sfrm.CalculationID),String(100,"Û"),Null)

    The ControlSource property is set to an expression that, if your condition for hiding the control is met, will fill the text box with a character that -- in the specified font, Terminal -- displays as a solid block.  This is Chr(219), which in the property sheet will display as Û.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, October 23, 2016 8:35 PM