Results 1 to 9 of 9
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    Hiding Fields in a Subform

    I have a Form with an option box. I am trying to code the option box for AfterUpdate to hide certain fields on the subform but I can't seem to get the right format. But none I tried works. I just showed the relevant lines below.



    I have tried the following:

    If Choice = 1 then 'This is the option Option Box
    [Forms]![Formname]![Subformname]![fieldname].visible = false

    If Choice = 1 then 'This is the option Option Box
    [fieldname].visible = false

    If Choice = 1 then 'This is the option Option Box
    [Subformname]![fieldname].visible = false

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    A Select Case Statement may be more eloquent. Also, if using an option group, it is helpful to use the Wizard to set it up.

    Maybe something like the following is what you are after...
    Code:
    If Me.Choice.Value = 1 then 'This is the option Option Box
         [Forms]![Formname]![Subformname]![fieldname].visible = false
    Else If Me.Choice.Value = 2 then 
    [Forms]![Formname]![Subformname]![fieldname].visible = True
    [Forms]![Formname]![Subformname]![SomeOtherControl].visible = false
    Else
    [Forms]![Formname]![Subformname]![fieldname].visible = False
    [Forms]![Formname]![Subformname]![SomeOtherControl].visible = false
    End if

  3. #3
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I did use the wizard to create the option Group. The issue is with the Syntax for the Field in the Subform. If I want to hide a field on the main form, both my original syntax as well as your syntax both work. However for a field on the subform, both my syntax and yours give me an error message.

    Database can't find the field 'Subform' referred to in your expression.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure if matters but try:

    Forms![Formname]![Subformname].Form![fieldname].visible = False

    (Obviously substitute the real names of Form, subform and fieldname.)

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by swenger View Post
    ...If I want to hide a field on the main form...
    EDIT as Bulzie mentioned, the code I placed and the code you used is missing the distinction of the object type property for your subform control /edit

    So, you need to consider where the VBA resides. A subform control can contain forms, tables reports, etc.

    If you are placing code directly in the mainform's module you can reference controls using the Me shortcut this way
    Me.ControlName.Visible = -1

    Maybe you need to place code in the subform, itself. It is not something I typically use from the subform, but you should be able to reference the parent form using the Me shortcut (from a form's module).
    Me.Parent.ControlName.Visible = -1

    Or you can use the fully qualified name and reference the Forms collection
    Forms!MyMainFormName.ControlName.Visible = -1

  6. #6
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    MY BAD. I had a typo in the Subform name. Both syntax now works.

    Sorry for the trouble.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Can you show the code you used so we can see the solution? As mentioned, you should have gotten some errors either way.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Change this : Forms![Formname]![Subformname].Form![fieldname].visible = False

    To this: Forms![Formname]![Subformcontainername].Form![fieldname].visible = False

    where Subformcontainername is the name of the form control containing the subform. You cannot refer to a subform by the form name. (Unless of course the subform and its container control have the same name)

  9. #9
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Private Sub Choice_AfterUpdate() 'Choice is the Option Box
    If Choice = 1 Then
    Field1 = -1
    Field2 = 0
    Field3 = 0
    [Forms]![MainForm]![SubForm]![Field1].Visible = True
    [Forms]![MainForm]![SubForm]![Field2].Visible = False
    [Forms]![MainForm]![SubForm]![Field3].Visible = False
    [Forms]![MainForm]![SubForm]![Field1_Label].Visible = True
    [Forms]![MainForm]![SubForm]![Field2_Label].Visible = False
    [Forms]![MainForm]![SubForm]![Field3_Label].Visible = False
    Else
    If Choice = 2 Then
    Field1 = 0
    Field2 = -1
    Field3 = 0
    [Forms]![MainForm]![SubForm]![Field1].Visible = False
    [Forms]![MainForm]![SubForm]![Field2].Visible = True
    [Forms]![MainForm]![SubForm]![Field3].Visible = False
    [Forms]![MainForm]![SubForm]![Field1_Label].Visible = False
    [Forms]![MainForm]![SubForm]![Field2_Label].Visible = True
    [Forms]![MainForm]![SubForm]![Field3_Label].Visible = False
    Else
    If Choice = 3 Then
    Field1 = 0
    Field2 = 0
    Field3 = -1
    [Forms]![MainForm]![SubForm]![Field1].Visible = False
    [Forms]![MainForm]![SubForm]![Field2].Visible = False
    [Forms]![MainForm]![SubForm]![Field3].Visible = True
    [Forms]![MainForm]![SubForm]![Field1_Label].Visible = False
    [Forms]![MainForm]![SubForm]![Field2_Label].Visible = False
    [Forms]![MainForm]![SubForm]![Field3_Label].Visible = True
    End If
    End If
    End If
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hiding Subform Layout
    By Mahendra1000 in forum Access
    Replies: 3
    Last Post: 11-28-2013, 06:10 AM
  2. Replies: 1
    Last Post: 11-27-2011, 11:37 PM
  3. Hiding fields in a subform
    By degras in forum Forms
    Replies: 7
    Last Post: 01-18-2011, 10:55 AM
  4. Hiding fields that contains empty records
    By sakthivels in forum Reports
    Replies: 4
    Last Post: 05-27-2009, 07:06 AM
  5. Replies: 1
    Last Post: 10-26-2007, 07:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums