Allow only one form, or a certain set of 2 to 3 forms, to simultaneously be open

[rbstewart]

An Access 2007 database has several ribbons, each with several buttons. Each button opens a different form.



On ribbon A: only one form should be open at a time.

On ribbon B: forms red, blue, and green may be open simultaneously but brown may only be open by itself.

What is the best way to approach this?

--------------------------------------

My current approach follows:

Add an onClose handler to every form (tedious) that calls a setApplicationStateFoo function with the name of the form.

setApplicationStateFoo cycles through all open forms, discards the most-recently-closed form from consideration, and disables/enables ribbon buttons according to the currently opened forms.

-----------------------------------------

Since I'm lazy, I would like to either:

(A) Catch and handle an event that fires when any form is closed.
or
(B) Iterate over AllForms and add the onClose event handler dynamically when the database is opened.

-----------------------------------------

Since I'm new to Access programming, I would also like to know any other (preferably better) ways to approach this problem.

[Rod]

OK

I assume that your system is single-user and stand-alone. By that I mean all the checking is performed within the same machine; you're not talking about rules across a LAN.

There are two events you could use: when you close a form (as you describe in your post) or when you attempt to open another form. I think (without setting up a prototype) there is little to choose between them: you suggest enabling/disabling your ribbon controls as you close forms (you would have to also adjust these controls when you open forms); otherwise you could simply test whether it's valid to open a form according to whatever is already open (a variation would be to force the conflicting form to close).

We all tend to be lazy when referring to forms opening and closing. Here are the sequences of events (red means it may be cancelled):

Open → Load → Resize → Activate → Current

Unload → Deactivate → Close

If you use the DoCmd object then opening a form will cause all five events to happen in sequence. You can test whether it is valid to continue with opening the form at the Form_Open event which may be cancelled, that is you can prevent the form from opening. How do you test whether it's valid to continue? Well there is an IsLoaded function that tells you for a specific form whether it is loaded - it may not be active or current, it may be minimized, it may be hidden but it will tell you if it's loaded. So when opening a form, the Form_Open event could have an interrogation in the procedure to determine if any conflicting forms are loaded and, if they are, display a warning to tell the user what to do and cancel the opening of the new form. This suggestion avoids altering the ribbon buttons.

If you want to use the ribbon buttons then you must adjust them both as you open a new form and as you close an existing form.

This post is very much a discussion of strategy. Decide how you want to proceed and we'll help with the code if necessary.