Now that my last post is buried well within the previous pages it is time for another pearl of wisdom - the second pearl on the string, so to speak.
For a considerable time I ignored the On Activate event for forms, I just couldn’t see the use of such an event. Lately I find myself using it more and more as it has (at least for me) beneficial characteristics. Let me try to explain and then give a practical example.
I like to picture the On Activate event as a layer that covers the form, protecting all the controls and objects on that form. Thus when you click on the form or move to it by some other method the form is not disturbed in any way. If say you clicked on a command button the On Click event for that button is not triggered until the On Activate handler finishes, and is not triggered at all if you move away from the form, perhaps by means of a set focus. What this means is that if there is any validation to perform before letting the user have access to the form, do it in the On Activate event handler.
I often design application functions involving two separate forms; one form is a list and the second form, that can be opened and closed from the list, is a detail form showing all data for the row highlighted on the list. While the detail form is open it remains synchronized as the user moves up and down the list. (I know a similar effect can be achieved using the form-sub form structure but there are reasons for not using it.)
Now maybe the user has changed data on the detail form and this needs to be validated before I let him/her back into the list. I insert a call to the validation routine in the On Activate event for the list form and if the validation fails simply set focus to the detail form. The user cannot return to the list form until the error on the detail form is corrected (all methods of closing the detail form locally are removed). The benefit of this design is that the call to the validation is in one place and one place only.
I tend as a matter of practice to write all my form validation routines as a procedure (or set of procedures) in the form’s VBA module itself. Further the procedure is always called by the same name, e.g. ‘Valid’. Thus the code behind the On Activate event has the general pattern:
If form1 is loaded then
If not form1.valid then
Set focus form1
Elseif form2 is loaded then
If not form2.valid then
Set focus form2
So in summary, if you need to freeze everything and perform some validation or clean-up before you let the user into a form, the On Activate event has to be your choice. The key point is that the form is not disturbed and no control events triggered until the On Activate event finishes normally. If you move away from the form from within the On Activate event then those control events are not triggered at all.