Adding simple, sexy help to your Access application

I was recently granted the honor of taking over maintenance of an internal Access application at my day job. One could view this as a side effect of openly knowing things about many technologies where others don’t. Anyways, this application was written by a project management group and was not created by experienced programmers. They did an okay job with the system, but one useful feature that was missing was any sort of help information for the different forms in the application. One of the first things I did was add this help feature to every single form.The help I was going to add had to meet basic requirements:

  • Help shall be simple to add to any form in the application
  • Help text must be easily updated without the need for any special editing tools
  • Help must be maintainable without the need for exclusive locks or replacing the database
  • Help text must be visually pleasing

This really left one option for implementing it. I would have to use a web browser control hosted in an Access form. The control could be easily pointed to different HTML files for displaying the help information. These help files can easily be edited using any text editor (even notepad), quickly maintained, and using a little style they will even look good.

Step 1: Creating the help form

For the purposes of this article, I will show how to implement this help in the Northwind sample database in Microsoft Access 2003. This database is provided with your copy of Access 2003. To open it, select the Help menu, then Sample Databases and finally Northwind Sample Database.

Add a new form to your database in design mode and save it with the name Help.

New Help Form

Northwind database showing the newly added Help form

With the help form open in design mode, click on the More Controls button in the toolbox toolbar. This will display a list of ActiveX controls that are registered on your machine. Scroll down and fine the Microsoft Web Browser in the list and select it. Click your form to add the control to your form.

Help Form With Browser Control

Access help form with the Microsoft Browser Control shown in design mode

Step 2: Set properties and add help form code

Now that we have the browser control on your form, we must add some code to make sure that your browser control fills the form whenever the form is resized. We will also set some properties to ensure that our form shows up the way we want it to.

So first things first, select the form detail by clicking that square in the top-left corner and press F4 to set the properties as follows:

  • Format tab
    • Caption: Help
    • Allow Datasheet View: No
    • Allow PivotTable View: No
    • Allow PivotChart View: No
    • Scrollbars: Neither
    • Record Selectors: No
    • Navigation Buttons: No
    • Dividing Lines: No
    • Min Max Buttons: None
  • Event tab
    • On Load: [Event Procedure]
    • On Resize: [Event Procedure]
    • On Unload: [Event Procedure]
  • Other tab
    • Pop Up: Yes

Now select the browser control and set the following properties under the Other tab:

  • Name: browser
  • Silent: Yes
  • RegisterAsDropTarget: No

And finally, using the VBA Editor, paste the following code to supply all the help functionality.

Option Explicit
Option Compare Database

'// The name of the form that help is being shown for
Private m_helpFormName As String

Private Sub Form_Load()
    '// Make sure the browser control is in the top-left corner
    browser.Left = 0
    browser.Top = 0

    '// Default URL shows a page that says "Loading help ..."
    Call browser.navigate("about:Loading help ...")
End Sub

Private Sub Form_Resize()
    If (Me.InsideHeight > 0) And (Me.InsideWidth > 0) Then
        '// Resize the browser control to the form size
        browser.Width = Me.InsideWidth
        browser.Height = Me.InsideHeight

        '// Resize the detail form section to the size of the form
        Me.Width = Me.InsideWidth
        Me.Detail.Height = Me.InsideHeight
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    '// When the close button is clicked, just hide the form
    '// unless it has never been displayed. This allows the
    '// form to easily be redisplayed in the same state that
    '// it was last shown in.
    Me.Visible = False
    If Not (m_helpFormName = vbNullString) Then
        Cancel = True
    End If
End Sub

Public Sub ShowHelp(forForm As Form)

    '// Navigate to the help. Prevent renavigating if we are
    '// just redisplaying the help for a form.
    If Not (m_helpFormName = forForm.Name) Then
        m_helpFormName = forForm.Name
        Me.Caption = "Help - " & forForm.Caption
        Call browser.navigate(Nz(DLookup("Value", "Configuration", "[Name] = ""Help:" & m_helpFormName & """"), "about:Error locating help file"))
    End If

    '// Make the form visible and give it focus. The focus
    '// part is useful for when a form is still visible but
    '// not in the foreground and the user clicks the Help
    '// button. In this case, we just want to bring the already
    '// visible help form to the front.
    Me.Visible = True
    Call Me.SetFocus
End Sub

Step 3: Create a table for storing help file paths

Avoiding hard coding is generally a good practice. As a part of wanting to be able to easily maintain these help files, I want to make sure that I can change where these files are located without having to change code. Placing the paths in a table is the best way. I like to use a table named Configuration to store application configuration information. Create a configuration table in your database with the following two fields:

  • First Field
    • Name: Name
    • Type: Text
    • Length: 50 (default)
    • Primary Key
  • Second Field
    • Name: Value
    • Type: Memo

The configuration table will store links to our help files by using the name of the form that we are displaying help for as part of the Name to the configuration entry. We will prefix it with “Help:” so that we distinguish these configuration records. As an example, a form named “Main Switchboard” would have a pointer to its help file store with the name “Help:Main Switchboard“. The Value for this configuration entry will be the URL to the HTML file that we will point the web browser control to. You will notice in the code above that we use a call to DLookup to retrieve the URL.

Step 4: Creating a help HTML file

Now we’re starting to get to the fun part! Using HTML as our media for help and a browser control to render it really opens up the possibilities. As a barebones example I will provide you with an example help file that incorporates the jQuery library for some simple animation and a basic structure in the HTML file that can be duplicated to create many help sections within a help HTML file.  Go to the jQuery website and snag a copy of the latest library (I’m using jquery-1.4.2.min.js in this example).

The basic structure for a section of help text uses a DIV which contains child element DIVs for the Section Title and Section Contents. Copying and pasting this structure makes it very easy to add new help sections.

<div class="helpSection">
  <div class="sectionTitle">
    <strong>Title Goes Here</strong>
  </div>
  <div class="sectionContents">
    Contents Go Here
  </div>
</div>

Here is the HTML for creating an example help file for Northwind’s Main Switchboard form. I will leave it as an exercise for the reader to move the style and javascript code to externally included files. Save this file to the same location you placed your jQuery download at and then link to it in your Configuration table as described above. I saved it on my system to “C:\Help\Main Switchboard.html“. My Configuration table entry has a Name of “Help:Main Switchboard” and a Value of “C:\Help\Main Switchboard.html“.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head xml:lang="en-us">
    <meta content="text/html; charset=utf-8" http-equiv="content-type" />
    <title>Northwind: Main Switchboard Help</title>
    <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
    <style>
      /********* Basic Styles *********/
      BODY    { font-family: Arial; font-size: 10pt; }
      A       { text-decoration: none; }
      A:hover { text-decoration: underline; }
      OL      { margin-top: 0; }

      /********* Help Sections *********/
      .helpSection { padding-bottom: .5em; }
      .sectionContents { padding-bottom: .5em; }
      .sectionContents > DIV { padding-bottom: .5em; }
      .sectionContents > UL { margin-top: 0; }

      /********* Help section Title *********/
      .sectionTitle { font-weight: bold; color: Blue; cursor: hand; font-size: 12pt; }
      .sectionTitle:hover { text-decoration: underline; }

      /********* Hide All / Show All link styles *********/
      #showHideAll { font-size: 8pt; color: Blue; text-align: right; }
      #hideAll:hover, #showAll:hover { text-decoration: underline; cursor: hand; }
    </style>
    <script type="text/javascript">
      $(document).ready(function() {

        // Disable right-click context menu
        $(document)[0].oncontextmenu = function() { return false; }

        // Hide help section contents and setup toggle functionality on links
        $(".helpSection > .sectionContents").slideUp("slow");
        $(".helpSection > .sectionTitle").click(function() {
        $(this).next().slideToggle("slow");
        });

        $("#showAll").click(function() {
            $(".sectionContents:hidden").slideToggle("slow");
        });

        $("#hideAll").click(function() {
        $(".sectionContents:visible").slideToggle("slow");
        });
      });
    </script>
  </head>
  <body>

    <div id="showHideAll">
      [All: <span id="hideAll">Hide</span> / <span id="showAll">Show</span>]
    </div>

    <div class="helpSection">
      <div class="sectionTitle">
        <strong>Overview</strong>
      </div>
      <div class="sectionContents">
        The <strong>Northwind</strong> database contains example tables, queries,
        reports, forms, pages, macros and modules as an example of what can be done
        in Microsoft Access. The <strong>Main Switchboard</strong> is the first form displayed after
        the <strong>Startup</strong> form. It allows easy navigation to the other areas of the
        application via command buttons on the form.
      </div>
    </div>

        <div class="helpSection">
      <div class="sectionTitle">
        <strong>Command Buttons</strong>
      </div>
      <div class="sectionContents">
        The switchboard contains the following buttons:
        <ul>
          <li><strong>Categories</strong>: Displays a form for editing categories.
          <li><strong>Suppliers</strong>: Displays a form for editing suppliers.
          <li><strong>Products</strong>: Displays a form for editing products.
          <li><strong>Orders</strong>: Displays a form for editing orders.
          <li><strong>Print Sales Reports</strong>: Displays a form for viewing and printing reports.
          <li><strong>Display Database Window</strong>: Hides the switchboard and shows the database window.
          <li><strong>Help</strong>: Displays this help information.
          <li><strong>Exit Microsoft Access</strong>: Closes out of Microsoft Access.
      </div>
    </div>

  </body>
</html>

Step 5: Using the help form

With the help form configured and ready to go, adding help buttons and code to display it is very simple. We can use a Static variable inside the Click event for our button. The static variable will be of the same type as our help form, Form_Help. This will allow us to easily have all our code inside of the help button’s click event, making it easy to copy from one form to the next.

Add a Help button to the Northwind’s Main Switchboard form. Name this button cmdHelp and then enter code for the Click event as follows:

Private Sub cmdHelp_Click()
    On Error GoTo HelpErr
    Static help As New Form_Help

ShowHelp:
    Call help.ShowHelp(Me)
    Exit Sub

HelpErr:
    If (Err.Number = 2467) Then
        On Error GoTo 0
        Set help = New Form_Help
        Resume ShowHelp
    End If
    Call MsgBox("Error showing help: " & Err.Description, vbCritical, "Help Error!")
End Sub

This code, without modification, can be copied and pasted in to any form under any help button throughout your application. At this point, adding help to a form is a matter of adding the button, copying the code, creating the help text file, and entering a Configuration table entry to point to it.

I’ll admit that my example may be leaving some sexy to be desired. You have the power of full HTML at your finger tips for these help files. Use that to add the sexy, I’ve given you enough simple here. This just makes the sexy the only thing left for you to really focus on.

Main Switchboard With Help Button

Northwind's Main Switchboard showing the new help button

Main Switchboard Help Window

The help window showing the help text for the Main Switchboard form

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s