3

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.

braX
  • 11,506
  • 5
  • 20
  • 33
Skywalker
  • 412
  • 1
  • 8
  • 14
  • 1
    Have you checked to see if the Activate event is even being called when the form is opening up? Add a line of code that says `Debug.Print "Activate was called"` That way you'll know if it was called. It's possible that Activate is called before the controls in the form are loaded. – Ben McCormack Nov 11 '09 at 20:27
  • 1
    I believe the problem is the Activate is called **after** the controls are loaded. Activate is the last action when a form is loaded, but it is also called when you switch to that form. This is the behavior I need. – Skywalker Nov 11 '09 at 21:39
  • 1
    You really should get in the habit of prefacing control references with the Me! identifier, i.e., Me!txtControl.ColumnHidden, or Me.txtControl.ColumnHidden. – David-W-Fenton Nov 12 '09 at 02:43
  • 2
    I've found Activate is not very reliable (for instance, it never occurs for a popup form). I would never rely on it for initializing properties -- I'd use the OnLoad event (you avoid using the OnOpen event for operations that deal with controls, as the controls may not be fully loaded yet). – David-W-Fenton Nov 12 '09 at 02:45

2 Answers2

7

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.

Dale
  • 12,884
  • 8
  • 53
  • 83
  • The thing is, I really **do** want this event to occur whenever the focus switches to this form. There is a different form which acts as a main menu and has many complicated filter options. When the form with the datasheet is opened or switched to, I have it refiltering based on the main menu form. I also want the columns which are not relevant at that point to be hidden – Skywalker Nov 13 '09 at 14:26
0

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.

Ben McCormack
  • 32,086
  • 48
  • 148
  • 223