Hiding columns in a Microsoft Access 2007 datasheet with VBA

Question

I am trying to hide specific columns in an Access 2007 split form through code. I need the form to check certain conditions to see whether it needs to display a column or not. I have code in the form's 'Activate' event to hide the column like this:

txtControl.ColumnHidden = True

This code works in the "Open" event, but if I hide the column on Activate, it won't display these changes until I close the form and open it again. I have tried calling the form's refresh, repaint, and requery methods, but this doesn't work. Please help!

Edit: Ideally, I need this event to occur whenever the focus switches to this form.That's why I'm using the Activate event rather than the Open event.

Answer 1

I've had a problem like this before working in Access 2002. I was able to solve the problem with a subform by setting the subform source object equal to itself and then running the requery.

Me.SubForm.SourceObject = Me.SubForm.SourceObject
Me.SubForm.Requery

See if this technique works for your particular situation.

Answer 2

Try setting it in either the form's Current or Load events. You will also probably need to requery the control after setting that property: Me.TextControl.Requery Current is called every time a form's record is changed, the form is repainted or requeried. Load, as its name suggests, is called once, after the form has opened when the form loads its records. These have always been more reliable for me than using Activate, which really has to do with more the focus of the form, not really what you want.