I.1. Visual Interface Standards

No matter how good your application is under the covers, people won't believe it if it doesn't look good. On the other hand, if your application looks great, people will think it is a good application. This reality about your user's perception of your Access application might not be fair, but you are going to have to deal with it. Luckily, it isn't too hard to make your application look as great on the outside as you made it inside.

I.1.1. Use Businesslike Colors

Please take our advice: Don't use a lot of colors on your Access forms. If you want your applications to look like they fit right in the Windows environment, you should use the colors that Windows uses. For the venerable Windows Standard color scheme, this means gray. However, you should actually make the colors of your forms adapt to the Windows scheme automatically.

For the background color of almost everything (forms, buttons, read-only text boxes, and so on), use the Windows default background color (which defaults to gray in Windows Standard). The numeric color value is −2147483633. Use white (color value 16777215) for the background of changeable fields.

To test your colors and make sure they aren't hard-coded to a certain color, change your Windows color scheme to something different (like Lilac or Maple) and look through your screens to make sure there isn't any gray still showing.

Use red sparingly. You can use red (255) for the Fore Color of the Exit button and any dangerous buttons you might have, but don't use it anywhere else. If you overuse it, it will lose its special purpose as a warning or danger color.

I.1.2. Provide a Weil-Marked Exit

Provide an easy, but safe, exit from most of your screens. You should put an Exit button on most screens, always in the same place, like the lower-right corner. It should call a function that asks if the user really wants to exit the application. Users seem to like this a lot, because it gives them an easy way to exit the application without them feeling like they are circumventing something. Don't put this button on a screen where the user hasn't completed a particular action, like in the middle of a wizard or in a pop-up screen where they are adding some detailed level of information.

Here is some code you can call from the On Click event of the Exit button in any form. It can be placed in any Module.

Sub ExitProgram()
On Error GoTo Error_Handler

    Dim response As Variant
    If MsgBox("Are you sure you want to exit " & "My Application" _
    & "?", vbOKCancel, "My Application") = vbOK Then
        DoCmd.Quit
    End If

Exit_Procedure:
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell " _
    & "them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
    & Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume
End Sub

With this code, you can give your users a chance to stay in the application in case they didn't mean to exit all the way out. Since applications often take several seconds to start back up, they'll appreciate the chance you gave them to think about whether they really intend to exit.

I.1.3. Watch Your Punctuation

Most Access applications are used in a business setting, so it's best to keep a professional tone in your application. Proper punctuation is especially important, as it shows a level of polish and thoroughness.

For example, use exclamation points very sparingly. Don't kid yourself; it's unlikely that anything in your application is exciting enough to warrant one. Don't let the user think you excite easily.

It's much more professional to say: "All product records were imported successfully." than "All product records were imported successfully!"

Also, always end sentences and statements with periods. It looks more polished to say: "Products sales forecast calculations have been completed." than "Products sales forecasts have been completed"

If you keep your punctuation correct and your tone businesslike, your users will perceive your application as a competent business tool.

I.1.4. Use Consistent Button Placement

Be consistent with button placement, size, and color. Close and Exit buttons should always be in the same places on the screen. The "drill-down" form the user opens by double-clicking on a row should also be available by clicking the left-most button at the bottom of the screen. See Figure I-1.

Also, provide Alt keys for your buttons. It's easy: In the caption of each button, add an ampersand (&) before the letter you want to use for the Alt key combination. Try to make them consistent across all your forms, and make sure that you don't use the same letter for two different buttons.

I.1.5. Hide Higher Level Forms

Hide previous forms as you drill down, unless the next one is directly related to the previous one and you open the next one in Dialog mode. Don't let the user click between open forms; they may get lost or take actions you didn't plan for in your code. See the Daisy-Chaining topic later in this appendix for a method to do this.

I.1.6. Use Read-Only "Index" Forms

When your user needs to open a table or recordset to modify data, don't just dump the user straight into the detail form where the poor "alphabetically first" record always appears. This is very unprofessional looking, and it forces the user to constantly navigate to the desired record. In the meantime, they can mistakenly change the wrong record.

Instead, build an in-between index form showing all the records, read-only, in default Windows gray, in continuous form view (see the previous section Use Businesslike Colors). When the user double-clicks on a row or clicks the Detail button, show only that particular detail record by setting the WhereCondition on the OpenForm command to the key of the selected record, like this:

DoCmd.OpenForm FormName:="frmBusiness", _
wherecondition:="BusinessKey = " & Me!BusinessKey

A good way to distinguish between your index form and the detail form is this: Name the index form with the plural form, and the index with the singular form. For example, you can have frmBusinesses (read-only index form showing multiple records) and frmBusiness (editable detail form showing only one record).

Another advantage of using an index form (in case you need another one!) is that you can get some performance improvements by showing just a few fields on the index form and only opening up the whole editable record (often with performance-costly combo boxes, and so on) when the user drills down on it. This is especially noticeable if you are building a client/server application using SQL Server for the back-end database.

I.1.7. Check Your Table Linkage

You already have a database that has a separate front-end application and back-end database, right? Well, don't let the user see an invalid table message if the back-end database isn't in the location that it was last time. Check your table links every time you start your application. There are some common routines on the Internet or in other books to check table links, or you can write your own. Either way, if you handle your linked tables before your user sees a problem, your application will look a lot more professional.

I.1.8. Translate Default Delete Messages

Depending on how slick you want your application to be, you may want to replace the messages Access gives you when you delete records with a friendlier version of your own. You can confirm deletions for each record or for a whole group, and you can prevent the default Access messages from appearing. This takes a bit more work, but it looks more professional. (See Chapter 8 for more information about using the Delete and Before Delete Confirm events). Use wording like: Are you sure you want to delete product Widget125?

Showing your own message before deleting a record is especially helpful when you have enabled Cascade Deletes in back-end relationship. You may want to use wording like: Are you sure you want to delete business Joe's Tire Shop? This will also delete all the Payments made by this business.

By using your own friendly warning messages instead of the default Access messages, your application will look more professional and will be easier for your users to understand.

I.1.9. Looking Good

Remember that your users can't see your great code or beautiful database structure. They can only see your user interface—the forms and reports in your application. Much of their perception of your application will be determined by how it looks, so it's important to pay special attention to these areas.

Now that we have your applications looking good, let's explore some techniques that you can use to make your applications more powerful and easy to use.

I.1.10. Daisy Chain Your Forms

When your user navigates from one form to another, one of your jobs as an application developer is to keep things simple. When users have the ability to click between multiple open forms, they can lose track of their current form, or perform actions that you fail to handle properly. A safer approach is to carefully control which forms are visible at any one time.

Controlling the visibility and flow of one form to another is often called "daisy chaining." There are a few different types of daisy chaining. They involve two main choices: whether to hide the calling form, and whether to open the called form in Dialog mode. Let's call the calling form "Form A" and the called form "Form B."

If Form A needs to requeried after Form B is closed, then you should open Form B in Dialog mode to have Form A's code wait until the user is finished with Form B. This often occurs when Form A is an index form showing multiple records, and Form B is a detail form where the user can create, change, or even delete one of the records.

If Form A provides some contextual information that would be handy for the user to see while Form B is open (like which record they are currently working with), then you can leave Form A visible while Form B is open. However, in order to prevent the user from clicking between the two and possibly obscuring Form B, you should also use our old friend Dialog mode to prevent them from clicking on Form A.

If Form A does not need to be visible, nor does it need to be requeried when Form B closed, then the easiest form of daisy chaining is just to hide Form A when Form B is opened, then make Form A visible again when Form B closes. This is the most common form of daisy chaining and it works well when traversing menu or "switchboard" forms.

The main VBA components of the "hiding and showing" aspects of daisy chaining code fall into the following sections.

I.1.11. Form A Opens Form B

When Form A opens Form B, Form A also needs to "hide itself." However, to make Form A visible again, Form B needs to remember which form opened it. In order to remember this, Form A uses a global variable to "pass in" its own name. See Figure I-2.

Here is some example code in frmBusinesses (an index form showing many businesses) to open frmBusiness (a detail form to modify a single business).

Private Sub cmdDetail_Click()
On Error GoTo Error_Handler

    Dim stLinkCriteria As String

    If IsNull(Me!BusinessKey) Then
       EnableDisableControls
       GoTo Exit_Procedure
    End If

    gstrCallingForm = Me.Name
    stLinkCriteria = "[BusinessKey]=" & Me![BusinessKey]
    DoCmd.OpenForm FormName:="frmBusiness", _
        wherecondition:=stLinkCriteria
    Me.Visible = False
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell " _
    & "them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
    & Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume

End Sub

Note that before frmBusiness is opened, the name of the current form (Me.Name) is loaded into the global variable gstrCallingForm.

gstrCallingForm = Me.Name

Then, after the line to open frmBusiness, the current form is hidden using Me.Visible = False.

Me.Visible = False

At this point, the first form is hidden and only the second form is visible, as shown in Figure I-3.

I.1.12. Form B Opens

When Form B wakes up, it has a little housekeeping to do before anything else happens. In the On Open event, it needs to remember the name of the form that called it. Later, when Form B closes, it will use that name to make Form A visible again.

Private Sub cmdDetail_Click()
On Error GoTo Error_Handler

    Me.Caption = AppGlobal.ApplicationNameAndDB()
    mstrCallingForm = gstrCallingForm
    gstrCallingForm = ""

Exit_Procedure:
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell " _
information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
Err.Description, _
    Buttons:=vbCritical, title:="My Application"

Resume Exit_Procedure
    Resume
End Sub

To remember the name, the value in gstrCallingForm is placed safely into mstrCallingForm.

mstrCallingForm = gstrCallingForm

This module level variable (indicated with the "m" prefix) is declared at the top of the Form B's module like this:

Option Compare Database
Option Explicit
Dim mstrCallingForm As String

Notice that gstrCallingForm is set to an empty string right after its contents are saved into mstrCallingForm:

gstrCallingForm = ""

There is no programming logic reason for doing this; it's really just a message to other programmers that we are completely done with using the global variable, so we are clearing its value. It has done its job well (for the last few milliseconds) and can go back to being an empty string now that it has transferred its contents to the local module variable.

At this point, Form B is ready to continue opening and perform whatever functions it is designed to do. It will remember the name of the calling Form A until Form B closes.

I.1.13. Form B Closes

During the whole time that Form B (frmBusiness) is open, Form A (frmBusinesses) remains hidden. However, when the user closes Form B, we need to make sure that Form A becomes visible again. The following code is in the On Close event of Form B:

Private Sub Form_Close()
On Error GoTo Error_Handler

    If mstrCallingForm <> "" Then
        Forms(mstrCallingForm).Visible = True
    End If

Exit_Procedure:
    Exit Sub
Error_Handler:
    If Err = 2450 Then
        ' ignore error if calling form is no longer loaded
        Resume Next
    Else
        MsgBox "An error has occurred in this application. " _
        & "Please contact your technical support person and tell them

this information:" _
        & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " &
Err.Description, _
        Buttons:=vbCritical, title:="My Application"
        Resume Exit_Procedure
        Resume
    End If
End Sub

The operative code here is:

Forms(mstrCallingForm).Visible = True

This code uses the Forms collection (a collection of all currently open forms in the database) to locate the form with the name stored in mstrCallingForm and make it visible.

However, there are two other sections of code that are there just for you, the developer. The first is a check to make sure that mstrCallingForm actually has a value before attempting to make it visible. This is to allow you to open Form B directly during development (instead of from Form A), and not have to deal with the resulting error every time Form B closes.

If mstrCallingForm <> "" Then
    Forms(mstrCallingForm).Visible = True
End If

Along the same lines, the error handler code contains an exception for Error 2 4 5 0. This error will occur if the calling form is no longer loaded—again, this is to allow you, in development mode, to close Form A while Form B is open without seeing an error when Form B closes.

If Err = 2450 Then
    ' ignore error if calling form is no longer loaded
    Resume Next

When Form B closes and makes Form A visible again, Form B's link in the daisy chain is complete.

I.1.14. When Form A Is a Subform

Sometimes Form A is a subform. In this case, you cannot send Me.Name into Form B, because a subform cannot be made visible or hidden directly. Instead, the parent form name needs to be specified.

This is easy to do. Instead of setting the global variable and making the current form hidden, like this:

gstrCallingForm = Me.Name
Me.Visible = False

send use the name of the parent form, like this:

gstrCallingForm = Me.Parent.Name
Me.Parent.Visible = False

The calling form will never know the difference. When it closes, the parent form will be made visible again.