Solved Hide/Show columns on subform datasheet (1 Viewer)

[mib1019]

Hello, all. Hope this fall day finds you well.

Simple problem, I think. I want the On Current Event of a subform to trigger hiding/showing field columns on datasheet of a second subform.

If subform1 field called Plot = "Weekly" Then These fields/columns on subform2 should be visible: AirWeek, Weekdays,DayCount; AirDate to be hidden.
If subform1 field called Plot = "Daily" then field/column AirDate should be visible, the others hidden.

Hope that makes sense.
Thanks!
MIB1019

 

[theDBguy]

Hi. What have you tried? Exactly what help do you need? Have you tried setting the Hidden property of each column to True or False?

 

[MajP]

See this article for hiding datasheet columns

You Can Do That with Datasheets? - vb123.com

Everyone who uses datasheets knows they’re powerful, but very few people know all of the things you can do with them. Michael Kaplan pulls out a handful of tricks from the unlocked Access 97 wizards that can help you really … Continue reading →

vb123.com

 

[mib1019]

It ALMOST works. Gets hung on the first .ColumnHidden = True when the main form first loads, requiring reset. I think it's not seeing the subform (subfrmIODetailsDates) until the form loads, so current event can't complete. Maybe needs an error trap?

Code is below. Also, I'd love to shorthand this code, but control referencing is still difficult.

Private Sub Form_Current()

    'Show/hide columns on Dates subform depending on value of Plot field.
    Dim Plot As String
    Plot = Me.Plot

    'Dim Form As Form
    'Dim AirWeek As Control
    'Dim AirDate As Control
    'Dim Weekdays As Control
    'Dim DayCount As Control
    'Set Form = Forms![Insertion Order Billing Data]![subfrmIODetailsDates].Form
    'Set AirWeek = Forms(Form)!Air_Week
    'Set AirDate = Forms(Form)!Air_Date
    'Set Weekdays = Forms(Form)!Weekdays
    'Set DayCount = Forms(Form)!Day_Count
    
    If Plot = "Daily" Then
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Air_Week].ColumnHidden = True
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Air_Date].ColumnHidden = False
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Weekdays].ColumnHidden = True
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Day_Count].ColumnHidden = True
        
    ElseIf Plot = "Weekly" Then
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Air_Week].ColumnHidden = False
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Air_Date].ColumnHidden = True
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Weekdays].ColumnHidden = False
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Day_Count].ColumnHidden = False
        
    Else
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Air_Week].ColumnHidden = False
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Air_Date].ColumnHidden = False
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Weekdays].ColumnHidden = False
        Forms![Insertion Order Billing Data]![subfrmIODetailDates].Form.[Day_Count].ColumnHidden = False
        
    End If   
    
End Sub

Thanks for the (always) outstanding help!
MIB1019

 

[Minty]

Subforms load BEFORE the main form, so you need to force it to load afterwards.

Set the subforms container source object to nothing then save the main form.
In the load event of the main form do something like

Me.YourSubformContainerControlName.SourceObject = "YourSubformsNameGoesHere"


And as you can see you can reference certain properties of the subform by referencing the subform container name.
You could also get the plot value from the main form and do all the column hiding on the subform to simplify things?

Dim Plot As String
Plot = Me.Parent.Plot

 

[arnelgp]

to simplify your code:

Private Sub Form_Current()

    'Show/hide columns on Dates subform depending on value of Plot field.
    Dim Plot As String
    Dim frm As Form
    
    Set frm = Me![subfrmIODetailDates].Form
    Plot = Me.Plot

    'Dim Form As Form
    'Dim AirWeek As Control
    'Dim AirDate As Control
    'Dim Weekdays As Control
    'Dim DayCount As Control
    'Set Form = Forms![Insertion Order Billing Data]![subfrmIODetailsDates].Form
    'Set AirWeek = Forms(Form)!Air_Week
    'Set AirDate = Forms(Form)!Air_Date
    'Set Weekdays = Forms(Form)!Weekdays
    'Set DayCount = Forms(Form)!Day_Count
    
    With frm
        If Plot = "Daily" Then
            ![Air_Week].ColumnHidden = True
            ![Air_Date].ColumnHidden = False
            ![Weekdays].ColumnHidden = True
            ![Day_Count].ColumnHidden = True
        
        ElseIf Plot = "Weekly" Then
            ![Air_Week].ColumnHidden = False
            ![Air_Date].ColumnHidden = True
            ![Weekdays].ColumnHidden = False
            ![Day_Count].ColumnHidden = False
        
        Else
            ![Air_Week].ColumnHidden = False
            ![Air_Date].ColumnHidden = False
            ![Weekdays].ColumnHidden = False
            ![Day_Count].ColumnHidden = False
        
        End If
    End With
    Set frm = Nothing
End Sub

 

[mib1019]

Thanks, everyone. Got it working!
MIB1019

 

[theDBguy]

Thanks, everyone. Got it working!
MIB1019

Congratulations! Good luck with your project.