Is there an On Database Close event?

[MacroScope]

I have an Access 2007 system with about 20 users, with a MySql back end. I've been asked to add a reminder message that pops up if someone either closes the database through the menu or hits the X at the top right.

Is there an event that triggers only on database close?

If not, does anyone have a creative way to accomplish same?

 

[lameid]

There is not a whole database file event.

You can open a hidden form when the application starts, and put the code you want to run on that form's close event.... At least I think that should work, there may be some nuances I'm not remembering... but seems I have read about it in one of these Access fora.

 

[MacroScope]

Thanks. That doesn't allow cancellation of the event (something I didn't mention originally but something I'd like to have if possible), but it certainly will allow at least a popup reminder. In fact, I already have a hidden form on the database so I don't even have to create it!

 

[PHV]

Use the UnLoad event instead if you want to cancel the event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

The UnLoad event of the hidden form?

 

[PHV]

Yes.

 

[MacroScope]

Thank you. I'll try that. Question, though. Is there any way to assure that the hidden form closes first? If it is the last to close then of course, even if I interrupt the procedure, everything else would be shut down.

 

[lameid]

If there is a way to ensure a form closes first (I can't think of one), it will likely be idiosyncratic and prone to changes with patches, versions and changes to the frontend.

A google search turned up this...

http://www.fmsinc.com/MicrosoftAccess/startup/preventclose.asp

I didn't look at the code but it is to disable the Access application X button... I think you are stuck with the exit on the ribbon... maybe not I haven't played with that aspect of it.

 

[MacroScope]

Thanks, lameid. That was an interesting page. Also, the author made it clear that there is no On Close Database event. I'm going to try the hidden form suggestion along with disabling the X. That seems the best available solution.

Thanks to all who contributed thoughts and ideas.

 

[FancyPrairie]

I have a problem that is somewhat similar. That is, form A opens Form B. Form A does not close rather it is hidden. When Form B closes it unhides Form A. Or Form A may close and it's up to Form B to reopen Form A when Form B closes. However, when the user exits the database through the menu or hits X at the top right Form B then closes. On the onclose event it then attempts to either unhide Form A or open Form A. Since the database is in the process of shutting down, I receive an error trying to hide or open Form A.

Is there a way that SysCmd acSysCmdGetObjectState can be used to determine the state of the database? It appears that it can only be used on objects within the database rather than the database itself. Is that true?

 

[MacroScope]

You need a conditional expression on the OnClose event of the forms in question. [Screen].[ActiveForm].[CurrentView]=1 or [Forms]![FormNameHere].[CurrentView]=1 is one. You can control which action is taken depending on the view of different forms.

The evaluations are:
0 = Design View
1 = Form View
2 = Datasheet View

I'm pretty sure if you play with it you'll find which indicates Hidden View, likely 3 or 4.

The other is [CurrentProject].[AllForms]("FormNameHere").[IsLoaded]. If it evaluates to True then the form is open, and False indicates it is not open.

If you write your expressions around those conditions you should be able to solve all your problems.

 

[lameid]

FancyPrairie,

I don't know on that one.. Will an error handler run in that context? If so you could inspect the value during error or just trap the error but alas, I bet things are just weird when Access is closing.

 

[Craig0201]

One thing you could think about is whether you can always get to a consistent state. If you trap Form1's Unload event, you can set it to never close using Cancel = True and always set it to hide unless certain conditions are met. From that, you may be able to work a solution.