A common way to make those sql statements run when the db closes is like
this:
Create a form that is opened in hidden mode when the db opens.
On the Unload Event for this hidden form, put those sql statements.
When users close the db, those sql statements will run just before the
hidden form closes.
Here are my notes on disabling the app close button.
Would you settle for disabling the application [X] button?
Take your pick based on version.
ACC: How to Disable the Close Button (X) on the Access
Application Window (95/97)
http://support.microsoft.com/?id=258049
ACC2000: How to Disable the Close Button (X) on the Access
Application Window
http://support.microsoft.com/?id=245746
ACC2002: How to Disable the Close Button (X) on the Access
Application Window and the Exit Command on the File Menu
http://support.microsoft.com/?id=300688
Alternatively, take a look here:
http://www.mvps.org/access/general/gen0005.htm
or here:
http://www.datapigtechnologies.com/flashfiles/preventcloseform.html
And some ready made code from MVP Terry Kreft
aste the following code into a module, then call it with
Call Buttons(false) To turn them off and
Call Buttons(True) to turn them on
' ********** Code Start *************
Option Explicit
Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_SYSMENU = &H80000
Private Const SWP_NOSIZE = &H1
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOZORDER = &H4
Public Const SWP_FRAMECHANGED = &H20
Private Declare Function GetWindowLong _
Lib "user32" Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long _
) As Long
Private Declare Function SetWindowLong _
Lib "user32" Alias "SetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long _
) As Long
Private Declare Function SetWindowPos _
Lib "user32" ( _
ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal X As Long, _
ByVal Y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long _
) As Long
' *************************************
Function AccessTitleBar(Show As Boolean) As Long
Dim hwnd As Long
Dim nIndex As Long
Dim dwNewLong As Long
Dim dwLong As Long
Dim wFlags As Long
hwnd = hWndAccessApp
nIndex = GWL_STYLE
wFlags = SWP_NOSIZE + SWP_NOZORDER + SWP_FRAMECHANGED + SWP_NOMOVE
dwLong = GetWindowLong(hwnd, nIndex)
If Show Then
dwNewLong = (dwLong Or WS_CAPTION)
Else
dwNewLong = (dwLong And Not WS_CAPTION)
End If
Call SetWindowLong(hwnd, nIndex, dwNewLong)
Call SetWindowPos(hwnd, 0&, 0&, 0&, 0&, 0&, wFlags)
End Function
Function Buttons(Show As Boolean) As Long
Dim hwnd As Long
Dim nIndex As Long
Dim dwNewLong As Long
Dim dwLong As Long
hwnd = hWndAccessApp
nIndex = GWL_STYLE
Const wFlags = SWP_NOSIZE + SWP_NOZORDER + SWP_FRAMECHANGED + SWP_NOMOVE
Const FLAGS_COMBI = WS_MINIMIZEBOX Or WS_MAXIMIZEBOX Or WS_SYSMENU
dwLong = GetWindowLong(hwnd, nIndex)
If Show Then
dwNewLong = (dwLong Or FLAGS_COMBI)
Else
dwNewLong = (dwLong And Not FLAGS_COMBI)
End If
Call SetWindowLong(hwnd, nIndex, dwNewLong)
Call SetWindowPos(hwnd, 0&, 0&, 0&, 0&, 0&, wFlags)
End Function
' ********** Code End *************
-- Terry Kreft MVP Microsoft Access >>>>>>
-- Jeff Conrad Access Junkie
This is interesting. I've only used the instructions in the 97 article
before.
The 97 instructions work for 97, 2000, 2002, and 2003 by the way.
Anyway, I found it interesting that the 2002 article has additional
instructions
for allowing the user to toggle the close button through a form.
What step 6 is showing you is a way to toggle the enabled status of the
application close button. A back door so to speak. What I have done
is always set up an AutoExec macro to automatically turn it off without
user intervention. Just my choice.
So here is what you do.
Just create a new blank small form and put two command buttons on it.
Name them cmdEnable and cmdDisable.
In the Click event for cmdEnable enter this code into the code window:
Private Sub cmdEnable_Click()
Call SetEnabledState(True)
End Sub
In the Click event for cmdDisable enter this code into the code window:
Private Sub cmdDisable_Click()
Call SetEnabledState(False)
End Sub
Make any other formatting changes you wish to the form. Now it is
your decision whether you want to actually have the other users be
able to use this form. If not, do not show it to them and make a sneaky
way for you to open it. Clicking the two buttons will toggle the enabled
state of the application close button.
If you would like to disable the close button automatically when the
database opens just follow these steps:
1. Create a new macro
2. In the Action column enter RunCode
3. In the Function Name area in the bottom left corner enter this:
SetEnabledState(False)
4. Save and close the macro. You *must* name this macro
AutoExec.
5. It is ****essential**** you have a command button somewhere
on your main form that not only closes the application, but Access
as well. Something like DoCmd.Quit.
6. Close the database and then reopen. You will notice that the
close button is disabled and the Exit option on the File Menu.
-- Jeff Conrad Access Junkie
Jeanette Cunningham
