Hiding Column in Sub Form without it hiding on the source table

[chin chin]

Hi, I am a beginner and struggling to get to grips with some fundamentals of Access 2007 and VBA. Like the subject says, I am currently trying to hide a column in a sub form without it hiding in the source table. I'm trying a workaround by using VBA to hide the column when the form opens then unhide it when it closes. However I can't even seem to get the Form_Open() procedure working let alone change the columnhidden property. It's starting to drive me crazy so any help would be very welcome! Here's the code I've been trying:

Private Sub Form_Open()
Me.[Child12].[BookNum].ColumnHidden = True
End Sub

I've tried a few configurations of the columnhidden line with the same error each time. Ive also tried simply:

Private Sub Form_Open()
MsgBox ("Form Open!")
End Sub

to debug but get the same error complaining that 'Procedure declaration does not match description of event or procedure having the same name'

I do have a procedure working in the same class module:
Private Sub Check13_Click()
If [Check13] = True Then
[Child12].Enabled = True
Else
[Child12].Enabled = False
End If
End Sub

which is successfully disabling the subform unless a check box is ticked.

Thanks for any advice,

chin chin.

 

[vbaInet]

Put it in the Load event of the SUBFORM.

 

[Galaxiom]

The column width in the form and table are independent.

Refering to a property of a subform requires the .Form property of the subformcontrol.

Me.subformname.Form.someproperty

 

[chin chin]

Thanks, Ok I'm now putting this:

Me.[Child12].Form.[BookNum].ColumnHidden = True

in the Form_Load() sub and I'm getting a run-time error 'the expression you entered refers to an object that is closed or doesn't exist' - did I write it correctly? (btw Child12 is the subform and booknum is the column I want to hide)

GalaxiomAtHome, I thought that would be the case (independent columns) but it seems that whenever I hide a column in one it gets hidden in the other and vise-versa. Why would that be?

Thanks again for helping,

chin chin.

 

[vbaInet]

If in the subform, Me.BookNum.ColumnHidden...

 

[chin chin]

If in the subform, Me.BookNum.ColumnHidden...

Thanks, tried this now:

Private Sub Form_Load()
Me.[BookNum].ColumnHidden = True
End Sub

but get a new error saying it can't find the field. Don't I need to declare the name of the subform (child12) somewhere?

 

[vbaInet]

As the error implies, that field (i.e. BookNum) doesn't exist. Ensure it exists in the record source of your subform.

 

[Galaxiom]

I think maybe you have the table as the SourceObject of the subform. In this case you would be referring to the table and that would affect the ColumnWidth property of the table.

The subform's SourceObject should be a form whose RecordSource is based on the table.

 

[chin chin]

It's definitely there. Perhaps I put the code in the wrong place - it is just in the module I get if I click 'view code' from the form design view?

 

[chin chin]

I think maybe you have the table as the SourceObject of the subform. In this case you would be referring to the table and that would affect the ColumnWidth property of the table.

The subform's SourceObject should be a form whose RecordSource is based on the table.

So I make a form that just shows that table, then refer to the form in my main form? I'll have a go - thanks.

 

[chin chin]

That seems to have done it - phew. Thanks a lot for everyone's help.

chin chin

 

[Galaxiom]

Gald we got to the bottom of the problem. The ColumnWidth property changing the table was the key to realising what was happening.

Although table and queries can be used as a SourceObject of a subformcontrol it is not the way to go. Forms have a lot more facilities to manage record editing and display.