Show All Columns on Subform on Open

[alsoascientist]

I have a from with subform where the columns that are visable in the subform are controlled by checkboxes on the parent form.

I am trying to find a way to ensure that ALL the columns are visible when the form is opened, however I am running into a couple of issues:

DoCmd.RunCommand acCmdUnhideColumns

doesn't seem to work and crashes Access (I think because I am trying to use it on a subform - I have also tried it including a 'With' statement)

Forms!Orders![Orders Subform].Form![ProductID].ColumnHidden = False

works, however is very impractical due to the amount of columns in the subform.

Does anyone know a way of applying this to all of the columns? Or a way to loop through the columns, maybe something like...

for each clm in subform
.ColumnHidden = False

 

[Severin]

if you can not get anything to work, then a work around could be to use .sourceobject to load a subform with all columns when necessary!~)

 

[PeterF]

The code below in the open or load event of the subform should show all.

        Set ctl = me.Controls
        For Each F In ctl
                 Me(F.Name).ColumnHidden = False
            End If
        Next

 

[alsoascientist]

Thanks both of you...

Severin, I did think about that, however the form will need to reset each time it is loaded and is from a dynamic datasource so can't get my head around how to make that work in practice!

PeterF I think that is what I am looking for, however (forgive me if I'm being dense!) I don't see where the f is coming from and there is an End If there without a Block If?

 

[bob fitz]

You could try this:

For Each ctrl In Me.[B][COLOR=red]subformName[/COLOR][/B].Controls
   If ctrl.ControlType = acTextBox Then
      ctrl.ColumnHidden = False
   End If
Next

Change subformName with the name of your subform

 

[alsoascientist]

Works a charm, Bob, Thanks!!