Unhide Fields On Sub Form

[jo15765]

I have a subform that is based off a query. What happens is you input a username in a textbox and push a button and a stored procedure is executed and a sub-form displays on screen with the results. This works, until someone hides a few of the columns in the sub-form! The only way I have found to "unhide them" is to highlight the entire sub-form, right click and say unhide.

Is there a way I can use VBA to perform a check that no column is hidden before displaying the sub form to the user? (and of course if the column is hidden display it)

I tried on the subform on load event to do this, but hidden columns remain hidden

Private Sub Form_Load()
'Show any hidden field when the subform loads
For Each ctrl In Me.[Form with space subform].Controls
   If ctrl.ControlType = acTextBox Then
      ctrl.ColumnHidden = False
   End If
Next
End Sub

[rpeare]

Have you got a sample database, I would rather not have to re-design something if you've got something handy.

It may be as simple as cycling through controls on the subform and setting the hidden property to false

dim ctl as control

for each ctl in me.controls
    if ctl.controltype = x then
        me.fieldname.columnhidden = -1 
        'or
        'me.fieldname.columnhidden = 0
    endif
next ctl

if you are calling it from within the subform

list of control types here:
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

[jo15765]

The line

Me.FieldName.

throws an error of method or data member not found.

[rpeare]

try using the subform name then instead of me

or forms("subformname").fieldname

I don't use bound forms so someone may be able to help you better than I can with syntax.

I would test it first just using

debug.print ctl.name

and see if it prints off a list of the controls properly. You really only want your data entry fields to show up when this debug.print goes off.

[jo15765]

Still no such luck. The only way I have seen to this point is to highlight and right click and say unhide.

[rpeare]

how about providing a sample of your database so I don't have to re-create something to do testing

[jo15765]

Database1.zip

Look at form1 if you hide a field in the subform, how can VBA unhide the field?

[rpeare]

there's something wrong with your zip, I can't unzip it without getting a multi volume error.

[jo15765]

Try this version. I created the 1st one using 7zip - maybe that was the issue.

[rpeare]

I put a button on the main form with the following code

For Each ctl In Me.Controls("Data subform").Controls    If InStr(ctl.Tag, "DE") > 0 Then
        ctl.ColumnHidden = False
        ctl.ColumnWidth = -1
    End If
Next ctl

On the subform, set the TAG property of all the fields to DE (last property in the property window

This worked fine for me