Hide subform 2 based on value on subform 1

[johnny_swindle]

I have two subforms on a tab. On subform 1, I have a combo box (eName) and if the value is Tour De Lis, the make subform 2 visible. I am not sure how to approach this situation.

Johnny

 

[GaP42]

Try this reference: Forms: Refer to Form and Subform properties and controls (mvps.org)
The two subforms appear side - by - side? Or is subform 2 a subform in subform 1?
If the latter then possibly:

If ename.text = "Tour De Lis"  then  ' the value for the combo is assumed to be an ID and not visible
 Me!Subform2.Form.visible = true
else
Me!Subform2.Form.visible = false
end if

However you may want to invoke this when loading the form - based on the value of the Foreign Key or the "eName" field in the record, and change the visibility on the after update event of the cbo.

 

[johnny_swindle]

@GaP42

I am glad you asked if they were side by side. Inf fact, subform 2 is a subform of subform1. I am sure that this makes a difference.

 

[Pat Hartman]

1. The .text property is only available while the control still has the focus so it is only available in the change event and possibly the click event. I would always use the .Value property for a combo.
2. Keep in mind that you are actually referencing the bound value, not the value that is displayed.
3. When the data in subformB depends on a value on formA, use the Current event of formA to control the visibility of subformB.
4. If the bound value is not the visible value, you need to reference the .Column property of the control to compare the string to the text value of the combo's RowSource.

 

[johnny_swindle]

I tried my hand at crafting the correct code. I am getting an error message on line one:

Private Sub eName_Change()
If Me!eName.Value = "Tour De Lis LA" Then

Forms!tourDetails_subform.Visible = ture

Else

Forms!events_subform1.Form!tourDetails_subform.Form!.Visible = False

End If
End Sub

 

[johnny_swindle]

I was able to solve the problem with the users who responded and with a little help from ChatGPT.

Private Sub eName_AfterUpdate()
If Me.eName = "Tour De Lis LA" Then
' Replace "SomeValue" with the value that should trigger hiding Form2
Me.tourDetails_subform.Visible = True
Else
Me.tourDetails_subform.Visible = False
End If
End Sub

 

[GaP42]

The error is? It is likely associated with incorrectly referencing the combobox properties, although this looks like it may be the first error encounterd in the code you posted:
What is the rowsource for the cbo eName? If it is multicolumn note the order of columns, even those that are hidden (width = 0). If the rowsource includes two columns say TourID and TourName in that order and the cbo eName is bound to TourID, then

If Me!eName.Value = thevalueofTourIDcorrespondingto"Tour De Lis LA" Then 
Me.tourDetails_subform.Visible = True
Else
Me.tourDetails_subform.Visible = False
End if

Alternately, if you explicitly need to refer to matching the text "Tour De Lis LA" then the statement is likely

If Me!eName.text = "Tour De Lis LA" Then ... etc
' (where the text TourName is the second column in the bound cbo control and is the visible content of the cbo).

Or
If Me!eName.column(1) = "Tour De Lis LA" Then ... etc
' (where TourName is the second column in the rowsource of the cbo control - column numbering starts at 0).

As @Pat Hartman mentioned - the display of the subform depends upon the value for "TourID" in subform1 records, so place the code in the Current event of subform1. If you also want the subform2 to be hidden immediately on selection of a value in the cbo eName, then you can also place the code in the Change event of the combo eName.

@johnny_swindle
Glad you have it sorted. Your rowsource is a simple list of values, not driven from a table of stored tour name values (hence the difference). This is OK but may in the longer term mean it is not a readily maintained.

 

[Pat Hartman]

I was able to solve the problem with the users who responded and with a little help from ChatGPT.

You never said what the error message was. We're good but not that good.