Controlling How Your Application Starts and Runs

Especially if you’re distributing your application for others to use, you probably want your application to automatically start when the user opens your database. As noted in the previous section, you should design switchboard forms to help the user navigate to the various parts of your application. You should also set properties and write code to ensure that your user can cleanly exit your application.

Setting Startup Properties for Your Database

At this point, you know how to build all the pieces you need to fully implement your database application. But what if you want your application to start automatically when you open your database? One way is to create a macro named AutoExec-Access always runs this macro if it exists when you open the database (unless you hold down the Shift key when you open the database). In the Conrad Systems Contacts database, we use an AutoExec macro to first determine whether the database is being run in a trusted environment. You can also specify an opening form in the startup properties for the database. You can set these properties by clicking the Microsoft Office Button, clicking Access Options, and then clicking the Current Database category, as shown in Figure 24–16.

Figure 24–16: You can set startup properties for your database in the Current Database category of the Access Options dialog box.

You can specify which form opens your database by selecting a form from the Display Form list. You can also specify a custom title for the application, an icon for the application, and a custom Ribbon to override the built-in Ribbon. If you always open the database with its folder set to the current directory, you can simply enter the icon file name, as shown in Figure 24–16. If you’re not sure which folder will be current when the application opens, you should enter a fully qualified file name location. Note that you can also ask Access to display the icon you specify as the form and report icon instead of the standard Access icons.

If you clear the Display Navigation Pane check box, Access hides the Navigation Pane when your application starts. (As you’ll learn later, you can also write code that executes in your startup form to ensure that the Navigation Pane is hidden.) You can also hide the status bar if you want by clearing the Display Status Bar check box. We like to use the SysCmd function to display information on the status bar, so we usually leave the Display Status Bar check box selected. We recommend you always clear the Enable Design Changes For Tables In Datasheet View (For This Database) check box. If you leave this check box selected, your users can make design changes to your tables displayed in Datasheet view, as well as any forms that open in Datasheet view.

Finally, you can disable special keys-such as F11 to reveal the Navigation Pane, Ctrl+G to open the Debug window, or Ctrl+Break to halt code execution-by clearing the Use Access Special Keys check box. As you can see, you have many powerful options for customizing how your application starts and how it operates.

Starting and Stopping Your Application

Although you can set startup properties asking Access to hide the Navigation Pane, you might want to include code in the Load event of your startup form to make sure it is hidden. All the sample databases provided with this book open the frmCopyright form as the startup form. Note that the AutoExec macro in these sample databases first checks to see whether the database is running in a trusted location. If the database is in a trusted location, the macro opens frmCopyright; otherwise, the macro opens the fdlgNotTrusted form followed by the frmCopyrightNotTrusted form. The copyright forms display information about the database. In the trusted version, code behind the form checks connections to linked tables. In both the Conrad Systems Contacts and Housing Reservations sample applications, the code behind the frmCopyright form tells you to open the frmSplash form to actually start the application.

When the frmSplash form opens, code in the Load event uses the following procedure to make sure the Navigation Pane is hidden:

 ' Select the Navigation Pane DoCmd.SelectObject acForm, "frmSplash", True ' .. and hide it RunCommand acCmdWindowHide

The procedure hides the Navigation Pane by selecting a known object in the Navigation Pane to give the Navigation Pane the focus and then executing the WindowHide command. The splash form waits for a timer to expire (the Timer event procedure) and then opens a form to sign on to the application. When you sign on successfully, the frmMain form finally opens.

The frmMain form in the Conrad Systems Contacts application has no Close button and no Control menu button. The database also has an AutoKeys macro defined that intercepts any attempt to close a window using the Ctrl+F4 keys. (You’ll learn about creating an AutoKeys macro in the next section.) So, you must click the Exit button on the frmMain form to close the application. On the other hand, the frmMain form in the Housing Reservations application does allow you to press Ctrl+F4 or click the Close button to close the form and exit the application.

You should always write code to clean up any open forms, reset variables, and close any open recordsets when the user asks to exit your application. Because the user can’t close the frmMain form in Conrad Systems Contacts application except by clicking the Exit button, you’ll find such clean-up code in the command button’s Click event. In the frmMain form in the Housing Reservations database, the clean-up code is in the form’s Close event procedure. The code in both forms is similar, so here’s the exit code in the Conrad Systems Contacts sample application.

 Private Sub cmdExit_Click() Dim intErr As Integer, frm As Form, intI As Integer Dim strData As String, strDir As String Dim lngOpen As Long, datBackup As Date Dim strLowBkp As String, strBkp As String, intBkp As Integer Dim db As DAO.Database, rst As DAO.Recordset   If vbNo = MsgBox("Are you sure you want to exit?", _     vbYesNo + vbQuestion + vbDefaultButton2, _       gstrAppTitle) Then       Exit Sub   End If   ' Trap any errors   On Error Resume Next   ' Make sure all forms are closed   For intI = (Forms.Count - 1) To 0 Step -1     Set frm = Forms(intI)     ' Don't close myself!     If frm.Name <> "frmMain" Then       ' Use the form's "Cancel" routine       frm.cmdCancel_Click       DoEvents     End If     ' Note any error that occurred     If Err <> 0 Then intErr = -1   Next intI   ' Log any error beyond here   On Error GoTo frmMain_Error   ' Skip backup check if there were errors   If intErr = 0 Then     Set db = CurrentDb     ' Open ztblVersion to see if we need to do a backup     Set rst = db.OpenRecordset("ztblVersion", dbOpenDynaset)     rst.MoveFirst     lngOpen = rst!OpenCount     datBackup = rst!LastBackup     rst.Close     Set rst = Nothing     ' If the user has opened 10 times     ' or last backup was more than 2 weeks ago...     If (lngOpen Mod 10 = 0) Or ((Date - datBackup) > 14) Then       ' Ask if they want to backup...       If vbYes = MsgBox("CSD highly recommends backing up " & _         "your data to avoid " & _         "any accidental data loss. Would you like to backup now?", _         vbYesNo + vbQuestion, gstrAppTitle) Then         ' Get the name of the data file         strData = Mid(db.TableDefs("ztblVersion").Connect, 11)         ' Get the name of its folder         strDir = Left(strData, InStrRev(strData, "\"))         ' See if the "BackupData" folder exists         If Len(Dir(strDir & "BackupData", vbDirectory)) = 0 Then           ' Nope, build it!           MkDir strDir & "BackupData"        End If        ' Now find any existing backups - keep only three        strBkp = Dir(strDir & "BackupData\CSDBkp*.accdb")        Do While Len(strBkp) > 0          intBkp = intBkp + 1          If (strBkp < strLowBkp) Or (Len(strLowBkp) = 0) Then            ' Save the name of the oldest backup found            strLowBkp = strBkp          End If          ' Get the next file          strBkp = Dir        Loop        ' If more than two backup files        If intBkp > 2 Then          ' Delete the oldest one          Kill strDir & "BackupData\" & strLowBkp        End If        ' Now, setup new backup name based on today's date        strBkp = strDir & "BackupData\CSDBkp" & _          Format(Date, "yymmdd") & ".accdb"        ' Make sure the target file doesn't exist        If Len(Dir(strBkp)) > 0 Then Kill strBkp        ' Create the backup file using Compact        DBEngine.CompactDatabase strData, strBkp        ' Now update the backup date        db.Execute "UPDATE ztblVersion SET LastBackup = #" & _          Date & "#", dbFailOnError        MsgBox "Backup created successfully!", vbInformation, gstrAppTitle      End If      ' See if error log has 20 or more entries      If db.TableDefs("ErrorLog").RecordCount > 20 Then        ' Don't ask if they've said not to...        If Not (DLookup("DontSendError", "tblUsers", _          "UserName = '" & gstrThisUser & "'")) Then          DoCmd.OpenForm "fdlgErrorSend", WindowMode:=acDialog        Else          db.Execute "DELETE * FROM ErrorLog", dbFailOnError        End If      End If    End If    Set db = Nothing  End If  ' Restore original keyboard behavior  ' Disabled in this sample ' Application.SetOption "Behavior Entering Field", gintEnterField ' Application.SetOption "Move After Enter", gintMoveEnter '   Application.SetOption "Arrow Key Behavior", gintArrowKey  ' We're outta here! frmMain_Exit:   On Error GoTo 0   DoCmd.Close acForm, Me.Name   ' In a production application, would quit here   DoCmd.SelectObject acForm, "frmMain", True   Exit Sub frmMain_Error:   ErrorLog "frmMain", Err, Error   Resume frmMain_Exit End Sub

After confirming that the user really wants to exit, the code looks at every open form. All forms have a public cmdCancel_Click event procedure that this code can call to ask the form to clear any pending edits and close itself. The DoEvents statement gives that code a chance to complete before going on to the next form. Notice that the code skips the form named frmMain (the form where this code is running).

If there were no errors closing all the forms, then the code opens a table that contains a count of how many times this application has run and the date of the last backup. Every tenth time the application has run or every two weeks since the last backup, the code offers to create a backup of the application data. If the user confirms creating a backup, the code creates a Backup subfolder if it does not exist, deletes the oldest backup if there are three or more in the folder, and then backs up the data using the CompactDatabase method of the DBEngine.

Next, the code checks to see whether more than 20 errors have been logged by code running in the application. If so, it opens a dialog box that gives the user the option to e-mail the error log, print out the error log, skip printing the error log this time, or turn off the option to print the log. Because the error log option form opens in Dialog mode, this code waits until that form closes. Finally, the code closes this form and selects an object in the Navigation Pane to reveal that pane. If this weren’t a demonstration application, the code would use the Quit method of the Application object to close and exit Access.

This might seem like a lot of extra work, but taking care of details like this really gives your application a professional polish.

Creating an AutoKeys Macro

As noted earlier, the Conrad Systems Contacts sample application (Contacts.accdb) has an AutoKeys macro defined to intercept pressing Ctrl+F4. You can normally press this key combination to close any window that has the focus, but the application is designed so that you must close the frmMain form using the Exit button, not Ctrl+F4. You can create an AutoKeys macro to define most keystrokes that you want to intercept and handle in some other way. You can define something as simple as a StopMacro action to effectively disable the keystrokes, create a series of macro actions that respond to the keystrokes, or use the RunCode action to call complex Visual Basic code. Figure 24–17 shows you the AutoKeys macro in the Conrad Systems Contacts database open in Design view.

Figure 24–17: The design of this AutoKeys macro intercepts the Ctrl+F4 key combination.

The critical part of a macro defined in an AutoKeys macro group is the macro name. When the name is a special combination of characters that match a key name, the macro executes whenever you press those keys. Table 24–5 shows you how to construct macro names in an AutoKeys macro group to respond to specific keys.

Table 24–5: AutoKeys Macro Key Codes
Open table as spreadsheet

AutoKeys Macro Name	Key Intercepted
^letter or ^number	Ctrl+[the named letter or number key]
{Fn}	The named function key (F1-F12)
^{Fn}	Ctrl+[the named function key]
+{Fn}	Shift+[the named function key]
{Insert}	Insert
^{Insert}	Ctrl+Insert
+{Insert}	Shift+Insert
{Delete} or {Del}	Delete
^{Delete} or ^{Del}	Ctrl+Delete
+{Delete} or +{Del}	Shift+Delete

Keep in mind that you can also intercept any keystroke on a form in the KeyDown and KeyPress events when you want to trap a particular key on only one form or control.