Unanswered: Attach event procedure code to form control with VBA.
Any help greatly appreciated as I am still in learning mode.
The short version: Normally, Access links an event procedure to its associated form event when the event is set up. However, as I have learned, the links can be lost if someone monkeys with the code in the background and/or the changes the button control names. Reattaching is a simple matter of going into each control and selecting event procedure, whereupon the event 'finds' its code. But doing that type of reconnecting via programmatic means would be very desirable if there were a number of forms and a number of buttons on each form. The question is, how to get VBA do it?
The long version: I have 11 forms that each have 11 buttons across the top. The buttons are a homemade navigation menu, so they do the same thing on each form. Ex: Button 2 on each form closes that particular form and opens Form 2. The background code for these 11 buttons on each of the 11 forms is almost exactly the same, and I had replicated it in all 11 places, meaning I also had to update it in 11 different places whenever a change came along.
Recently figured out how to reference open forms from standard modules, so I dutifully and gratefully pulled all of the button code from all 11 forms and consolidated it into one subroutine. Also changed the button names to a standard format. I tested it with Form 1 and it all works great. From Form 1, I can click any button and successfully go to the associated form.
But here's the bad news. When I deleted the code from behind each of the forms and replaced it with the standardized version, or else when I renamed the buttons, the button on_click events got disconnected from the actual code. For Form 1 to work, I had to manually click on each button in design view and then select on_click --> event procedure, before the form's on_click would reattach to it's corresponding code.
I'll probably be doing more forms standardization in other areas of the system so the more I can learn to do change work programmatically, the better.
I've tried different things but so far, whenver I get a value assigned, the on_click event thinks what I've specified is an expression or a function, not an event procedure.
How often are you or other people renaming controls? My first reaction is you're solving a symptom. You have a process problem if you are finding pre-existing custom VBA is regularly being broken due to the entire form changing.
I'm still lost on your design. Why do you have 11 forms calling the same methods with identical control names? What's wrong with one form instantiated or placed as a subform when needed?
The whole approach smells funny. It sounds like you're trying to outsmart yourself.
I really, REALLY don't think you should be doing this. But... To answer the question directly, I don't believe VBA supports passing around and assigning event delegates by reference at runtime. This is the closest thing I've heard of to similar functionality:
Appreciate your feedback. Perhaps I didn't describe the scenario in enough detail.
It is simply a set of navigation buttons on each form that provides a consistent interface for the customers on each screen. No matter which screen they're on, when they click the button with caption of 'Compliance History', the background code closes the current form and opens the compliance history form.
The VBA wasn't broken. I could have left it alone but wanted to consolidate repetitive code into a subroutine for the navigation buttons and also took the opportunity to implement a standard button naming convention at the same time. The system is still in development and the customers are apt to change their minds yet again about what screens they want, which would in turn affect the navigation buttons. Now I can change the code in one place.
Nothing needs to change at runtime, and in fact by now I could have gone in and manually reconnected all of the buttons. Was just trying to learn something new. Being able to programmatically manipulate controls seems a good skill to have.
By the way, the 2010 Navigation Buttons feature looked promising, and I tested it, but IMO it unnecessarily complicated things. For instance, all of the screens would end up having to be subforms. For that and other reasons, I like sticking with the homemade navigation. Plus, it is very clean and manageable now that all of the button click code is in one place.
Since this is still in development, it's worth seriously considering redesigning it! As you have already found out, maintaining 11 forms with identical components is a challenge.
A much simpler design (as suggested by Teddy) would be to have one form with the navigation buttons in the header.
You can use a tab control (and hide the tabs if desired) to hold all the subforms and simply use the navigation buttons to move around the tabs. It will make your maintenance much easier. And depending on their use, they don't have to be subforms. You can just have the appropriate fields on one of the tabs which makes sharing between the tabs much easier.
If you really want to use all 11 forms I think your simplest solution is a simple cut and paste. If you have already renamed your buttons to be the same on all forms and pulled the code out to a module it should be straight forward. Just create the on click event in one form and copy the entire sub to the remaining forms. If the names are the same they should connect up just fine.
But... you really should go to a single form design, it will avoid a lot of headaches in the future!
Had not occurred to me to create nav bar within the context of a subform. Was able to get it to work and is probably a higher quality solution. However, the extra layer of complexity seems too big a step for me just yet. Ex: Cannot reference form controls with Me. from the subform, must use Forms (form name). Would be more straightforward to grasp if all 11 forms had the same recordset and other properties, but they vary.
Steve, I didn't understand your suggestion entirely but got some of it, and appreciate.