VBA Form update after values set in code instead of AfterUpdate()

Question

I have multiple combo and option boxes whose visibility is set true or false depending on user selection of other parent option boxes.

I am using the AfterUpdate() function for the parent option boxes. This method has been working fine.

However when I set the values of the option boxes in VBA code (Me.MyOptionBox = 1), there is no "update" to make the child option and combo boxes appear. It works when I manually click on the option, but if I set it in the code nothing changes except for the option box selection. It doesn't make sense to see the option box set correctly but the code responsible for checking which option is selected not work.

Does anyone have any ideas on how to get around this? I just want the form to be updated once I set the values of the option boxes. What is the "update" that AfterUpdate() is referring to anyway?

I have tried MyForm.Repaint and MyForm.Requery but these do not work either.
Any ideas?

Answer 1

You can call the OptionBox_AfterUpdate() method directly - after you set the values.

Answer 2

I'm pretty sure this is intentional. The problem is avoiding circular recursive updates, when you consider that a control can be changed by a user and by two forms of code (explicit and bound data).

For bound controls, changes in the data change the controls.

For user input, changes in the control change the data, plus whatever dependendencies you explicitly specify in events.

For changes caused by your code, the assumption is that you need to manually apply all the consequences of the state change you are coding.

If you are using bound controls, you can change the datasource and have the consequences applied automatically.

Answer 3

By "I am using the AfterUpdate() function," you mean you added an event handler for the AfterUpdate event, right?

If that handler is not firing when you expect it to, you could always call your handler directly (instead of letting the event fire it) where necessary. But that probably wouldn't be the best way of doing it; more likely there is another event that should be calling your handler instead of or in addition to AfterUpdate. I don't remember the names of combo box events, but isn't there something like ItemChanged or SelectionChanged or something?

Answer 4

In VBA, most control events do not fire when the control is updated programmatically, rather than manually. You must specify the code that is to run after the programmatic update.