Results 1 to 13 of 13
  1. #1
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65

    Hiding/Showing a Subform based on Combo box Criteria

    Hi...

    2 part question, first I need a subform to show based on if the criteria in the Requester combo box with additional fields to complete. These additional fields in the subform will update the primary form table as well. If it does not meet the criteria, the subform should remain hidden. The second part is what would be the most straight forward way to code if there are say, 10-15 names that would allow the subform to be unhidden?


    This is what I have now which is not working:

    Private Sub Form_AfterUpdate()

    If Me.RequesterName.Value = "Smith" Then

    Forms!SubformName.Visible = True

    Else


    Forms!SubformName.Visible = False



    End If
    End Sub

    Thanks for your help!

    Eric

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try
    Me.SubformName.Visible = True

    Assumes the SubFormName is the name of the CONTROL on the main form holding the subform.

    Maybe you could add a field to the table/query that is the rowsource for the combobox. The field would be a yes/no as to whether the name will show the subform. That field could be populated when the name record is created.

  3. #3
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Dave, I like your idea for adding the column in the table (Y/N). I made that modification and changed the Forms! to Me. however, still not working. I have a few issues going on. One is the subform, which has the same control source as the main form, will not recognize the fields stating No Such Field in the Field List. Those fields still show in the main form available list (not on the form as fields). I am assuming if the subform is visible it can update the same record as the main form. It is linked through a master/child relationship. I'll stick to solving this issue first before I get into why it isn't able to hide.

    Any thoughts?

    Thanks -Eric

  4. #4
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    I was just able to figure out the visible issue adding the code to On Current event. Looking online I believe I am thinking about this wrong regarding the subform. I should just be hiding the objects within the form itself. Might be a little tedious but only at set up.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by DefinitelyMaybe View Post
    I was just able to figure out the visible issue adding the code to On Current event. Still need to figure out the linking piece...
    OK, main form current event makes subform visible or not automatic for each record displayed on main form.
    Don't know why subform won't show fields if its recordsource is proper and valid.

    There are other ways to do what you want if the recordsource is the same for both main and subform. Another way would be to do away with the subform and group all those fields (controls) that were formerly in the subform behind a rectangle where the subform was. Then just make the rectangle visible or not to cover and uncover those controls.

  6. #6
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    I've got it in a rectangle but that is the only thing that is visible or not. I was wondering if there was anything to tie them all together.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by DefinitelyMaybe View Post
    I've got it in a rectangle but that is the only thing that is visible or not. I was wondering if there was anything to tie them all together.
    The controls under the rectangle (box) are not visible when the box is not visible?

  8. #8
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Sorry, the controls are visible. So this is my code below... they are all the controls I want visible/hidden. When I run it stopped at the first label and the error message is Run time 438 - "Object does not support this object or method". I removed the label lines from the code thinking maybe they are tied to the control and would behave based on whatever condition set to the control. That didn't work either, both labels and controls are always visible. The only thing that changes to visible or hidden is the rectangle, and two unassociated labels I have as headings.

    If Me.InfraDash.Value = "Y" Then

    Me.Box140.Visible = True
    Me.Label108.Visible = True
    Me.Label129.Visible = True
    Me.IncludeInReporting_Label = True
    Me.IncludeInReporting = True
    Me.ConstructionStatus_Label = True
    Me.ConstructionStatus = True
    Me.Risk_Level_Label = True
    Me.Risk_Level = True
    Me.Risk_Reason_Label = True
    Me.Risk_Reason = True
    Me.InfraComments_label = True
    Me.InfraComments = True

    Else

    Me.Box140.Visible = False
    Me.Label108.Visible = False
    Me.Label129.Visible = False
    Me.IncludeInReporting_Label = False
    Me.IncludeInReporting = False
    Me.ConstructionStatus_Label = False
    Me.ConstructionStatus = False
    Me.Risk_Level_Label = False
    Me.Risk_Level = False
    Me.Risk_Reason_Label = False
    Me.Risk_Reason = False
    Me.InfraComments_label = False
    Me.InfraComments = False
    End If

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The box should have property background=normal. If background is transparent, then anything under would show with either box visible or not.
    Then this should be enough:
    Code:
    If Me.InfraDash.Value = "Y" Then
        Me.Box140.Visible = True
    Else
        Me.Box140.Visible = False
    End If
    If me.InfraDash is bound to a Yes/No field, then it should be a checkbox

    Code:
    If Me.InfraDash = True then
    or if you don't intend to edit InfraDash, then it shouldn't be on the form at all (but still in the recordsource query) and the code would be

    Code:
    If [InfraDash] = True then
    If you want to upload the db, I can show 2 or 3 ways to accomplish what you want in a return post
    Last edited by davegri; 03-01-2021 at 07:49 PM. Reason: more info

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    Quote Originally Posted by DefinitelyMaybe View Post
    <snip> When I run it stopped at the first label and the error message is Run time 438 - "Object does not support this object or method" <snip>
    Labels do not have a VALUE property, thus the error message.

    Rearranging the lines a little
    Code:
        Me.Box140.Visible = True
        Me.Label108.Visible = True
        Me.Label129.Visible = True
    
        Me.IncludeInReporting_Label = True   ' <<-- You cannot set a label to TRUE.  What happened to the visible property??
        Me.ConstructionStatus_Label = True   ' <<-- You cannot set a label to TRUE.  What happened to the visible property??
        Me.Risk_Level_Label = True           ' <<-- You cannot set a label to TRUE.  What happened to the visible property??
        Me.Risk_Reason_Label = True          ' <<-- You cannot set a label to TRUE.  What happened to the visible property??
        Me.InfraComments_label = True        ' <<-- You cannot set a label to TRUE.  What happened to the visible property??
    
    
        Me.IncludeInReporting = True         ' <<-- You want to set the control value to TRUE???  What happened to the visible property??
        Me.ConstructionStatus = True         ' <<-- You want to set the control value to TRUE???  What happened to the visible property??
        Me.Risk_Level = True                 ' <<-- You want to set the control value to TRUE???  What happened to the visible property??
        Me.Risk_Reason = True                ' <<-- You want to set the control value to TRUE???  What happened to the visible property??
        Me.InfraComments = True              ' <<-- You want to set the control value to TRUE???  What happened to the visible property??
    What am I missing???

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe better to show examples. Here's a db that has 2 examples.
    frmMembersTwo is a form/subform setup with the subform being visible or not depending on field IsVisible.
    frmMembersOne is a single form with a covering box that is either visible or not depending on field IsVisible.

    defmab-davegri-v01.zip

    frmMembersTwo
    Click image for larger version. 

Name:	twoviz.png 
Views:	15 
Size:	27.6 KB 
ID:	44432 Click image for larger version. 

Name:	twonovis.png 
Views:	15 
Size:	19.3 KB 
ID:	44433


    frmMembersOne
    Click image for larger version. 

Name:	onevis.png 
Views:	14 
Size:	25.8 KB 
ID:	44434 Click image for larger version. 

Name:	onenotvis.png 
Views:	14 
Size:	24.5 KB 
ID:	44435

  12. #12
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    I got it to work... As Steve pointed out some bad code that was missing "visible". Ugh...

    Thanks for your help Dave and Steve.

    -Eric

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think most of us have had the "Bad Code" experience at one time or another....

    Happy to help. Good luck with the rest of your project....

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

Similar Threads

  1. Showing/Hiding Certain fields
    By ryan812x in forum Forms
    Replies: 1
    Last Post: 08-05-2019, 12:56 PM
  2. Replies: 11
    Last Post: 07-26-2019, 07:31 AM
  3. Problem showing/hiding toolbars
    By JackieEVSC in forum Programming
    Replies: 8
    Last Post: 09-23-2016, 05:02 PM
  4. Hiding/Showing a Group of Controls
    By EddieN1 in forum Reports
    Replies: 6
    Last Post: 01-25-2014, 04:16 PM
  5. Replies: 6
    Last Post: 06-03-2009, 02:01 PM

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