CODEWITHSUNDEEP
vbaformsms-accesseventsload
McWilliamsPete
McWilliamsPete

Reputation: 25

MS access event load form

I have about 20 MS-Access databases with about 400 Forms and wish to perform an action whenever any form is loaded. I would need, perhaps, an event at database or application level that would trigger at any form being opened/loaded. Then I might need only to add code once to each of the 20 databases, but not to each of the 400 forms. And: it has to be in VBA (Access >= 2010)! My Question: Is this even possible? And if so, does someone have a hint? Thanks, Pete

Upvotes: 1

Views: 901

Answers (1)

Erik A
Erik A

Reputation: 32632

There are multiple ways to go about this, I think.

What I'd probably do is the following:

  1. Create a hidden form that opens whenever your database opens (using an autoexec macro) with a timer.
  2. When loading the hidden form, initialize a collection/multi-level array of all available forms in the database, and set their status to closed (somewhat difficult, probably would go with multilevel array or a collection inside a collection to be able to store form name + boolean open or closed).
  3. Periodically iterate the collection, check for changes, so you can see whenever a form closes and trigger your event.

You could also use VBA to iterate through all the forms, add a module to them if there is none, and then add your desired Form_Load code to that module. (Would probably be wise to simultaneously create code to undo that action, so you can actually maintain the code). While refining that, you could check if the form already has a Form_Load action, and append code to that if it exists.

Alltogether, possible, but difficult. If you want pointers on some of the steps, I can give them, but for major issues on the implementation, I'd ask a separate question.

Upvotes: 2

Related Questions