Access Cookbook - Ken Getz [182]
Because the database options are stored in the user's registry, any changes you make to them will affect not only the current database but also any other database the user runs. It's best to store the original value of any option you change and restore it when your application is closed. The acbStoreOriginalAutokeys function uses the GetOption method to read the original key assignment macro name when your application is loaded and store it in the mstrOriginalAutokeys module-level variable. Like the rest of the functions in this solution, acbStoreOriginalAutokeys is very simple, consisting of one statement, a few comments, and an error handler:
Public Function acbStoreOriginalAutokeys( )
' Store the user's original Autokeys macro name
' so we can restore it when we're done.
On Error GoTo HandleErr
mstrOriginalAutokeys = Application.GetOption("Key Assignment Macro")
ExitHere:
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "acbStoreOriginalAutokeys( )"
Resume ExitHere
End Function
The acbRestoreOriginalAutokeys function resets the option to its original value. This function should be called from the last open form. In the sample database, it is called from the Close event of frmUnit. Its source code is:
Public Function acbRestoreOriginalAutokeys( )
' Put the Autokeys macro setting back the way we found it.
On Error GoTo HandleErr
Application.SetOption "Key Assignment Macro", mstrOriginalAutokeys
ExitHere:
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "acbRestoreOriginalAutokeys( )"
Resume ExitHere
End Function
Each form passes the name of its custom key assignment macro to the acbSetAutokeys function when the form is activated. The Activate event of the form calls this function. The function uses the SetOption method to take the passed macro and make it the key assignment macro. Its source code is:
Public Function acbSetAutokeys(strMacroName As String)
' Set a new Autokeys macro. Takes the name of the
' macro to use for keyboard reassignment.
On Error GoTo HandleErr
Application.SetOption "Key Assignment Macro", strMacroName
ExitHere:
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "acbSetAutokeys( )"
Resume ExitHere
End Function
You can generalize this technique of using GetOption and SetOption to control many properties of your application at runtime—for example, to activate the status bar and toolbars or to allow the user to pick a new font for datasheets from a list you supply. You should always follow the same three basic steps:
Use GetOption to read the current option value and save it in a module-level variable.
Use SetOption to set your new value. Be sure to use the name of the option exactly as it appears in the Access online help.
Use SetOption to restore the original value when your application is closed.
OVERLAPPING USER INTERFACE (UI) METHODS
In a well-designed Windows application, keyboard shortcuts should not be the only method a user can employ to accomplish a task. Because they are hard for new users to discover or for infrequent users to remember, keyboard shortcuts should be used only as an alternative method of accomplishing a task. Make the task available from some other UI method, preferably one that is more easily discovered than a keyboard shortcut. Other UI methods include command buttons, toolbar buttons, standard menus, and shortcut menus.
To reduce the time delay in switching key assignment macros, we decided to reset the user's key assignment macro only when the last open form is closed. A safer but perhaps slower alternative would be to reset the key assignment macro in the Deactivate event of each form.
DETECTING WHEN A USER CLOSES AN APPLICATION
There is no built-in way to have Access always run a cleanup routine when the user closes your application. The final event you can trap is the last form's closing. If there are multiple possible last forms, you