Hiding/Showing a Subform based on Combo box Criteria

**DefinitelyMaybe** · 03-01-2021, 12:50 PM

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

**davegri** · 03-01-2021, 01:12 PM

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.

**DefinitelyMaybe** · 03-01-2021, 03:38 PM

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

**DefinitelyMaybe** · 03-01-2021, 03:50 PM

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.

**davegri** · 03-01-2021, 04:05 PM

Originally Posted by DefinitelyMaybe

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.

**DefinitelyMaybe** · 03-01-2021, 04:30 PM

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.

**davegri** · 03-01-2021, 04:48 PM

Originally Posted by DefinitelyMaybe

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?

**DefinitelyMaybe** · 03-01-2021, 06:05 PM

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

**davegri** · 03-01-2021, 07:26 PM

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

**ssanfu** · 03-01-2021, 07:53 PM

PMFJI,

Originally Posted by DefinitelyMaybe

<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???

**davegri** · 03-01-2021, 08:50 PM

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