Breadcrumbs, or How I Learned to Love the Switchboard

Andrew Wrigley shows how to design a “Breadcrumb” control that will enhance your users’ experience whenever they need to navigate a hierarchical structure (and bitterly regrets his lack of documentation…).

For those of you who think that you don’t know what a Breadcrumb control is: You already do. Breadcrumbs are all over the Internet. Figure 1 shows a typical example.

Figure 1

Familiar, isn’t it? Breadcrumbs show you where you are in navigating through a series of Web pages organized into a hierarchical structure. Breadcrumbs show how you got to the current page and how to retrace your steps. The bonus associated with this UI feature is that Breadcrumbs are also familiar to your users. Access has no such inbuilt control, but I’ll show you how to implement one–and do it with an architecture that makes it easy for you to reuse Breadcrumbs in just about any navigational situation (whenever your users need to browse a hierarchical data structure).

The intricacies (and hacks!) in the code prevent a complete discussion, so I’ll concentrate on the design issues and thinking that allow the control to be easily reused. The code itself was written when I was still a novice and could be easily optimized, but as a proof of concept it works exceptionally well. The sample download for this article shows how to use the Breadcrumb to revamp the dreary old Switchboard form into a user-friendly gem. Feel free to incorporate the code from the download into your applications.

The architecture

To promote code reuse, I packaged the core functionality into layers (modules, classes, and subforms), shunting anything that’s customizable into “Configuration” modules. The actual interface of my Breadcrumbs is packaged into a subform that has no hard-coded knowledge of its parent Form until runtime. This makes it easy to drop the subform onto any Form–reuse with a minimum of fuss and customization. Also, once the subform is designed and tested, it never needs to be modified. The only change that you need to make to the parent form is to add a line of code to the events in the parent form that needs to update the Breadcrumbs’ trail. That code looks like this:

BuildCrumbs Me.Name, rst, idNode, idItem, _

                          idParentNode, textItem

This is a highly encapsulated design, with only two customizable layers:

  • The interface configuration layer, where you can set font sizes, colors, border widths, and so on.
  • The data layer, which formats a hierarchical data structure for use by the Breadcrumb control.

The third layer of the code is the “engine layer” that handles all the work of laying out the Breadcrumbs. This code should never, ever be modified. Strictly speaking, the subform is part of the engine layers, as it is once designed and never modified. However, I’ll treat it as part of the interface layer for this article to simplify the discussion.

The only caution that I’ll make is that the data being displayed must be hierarchical; otherwise, the data display will be nonsensical. A good example of hierarchical data is the nested menus on a Switchboard form: The Switchboard initially displays a set of buttons, and when the user clicks on a button they’re taken to a new set of buttons, and so on until the user clicks on a button that actually displays an application form.

The interface configuration layer

The Breadcrumbs interface is built with hyperlink controls (see Figure 2), packaged into a subform that can be easily dropped into as many of your forms as you please. I’ll refer to this subform as a Breadcrumb control and each copy of the Breadcrumb control on a Form as an “instance” of the Breadcrumb control. Figure 3 shows two such instances, running simultaneously.

Figure 2

Figure 3

Each “crumb” hyperlink represents a level (node) in your hierarchical data. For visual effect, the crumbs alternate with “separators” (plain labels), whose caption you can set to whatever you want. The standard separator is “>”, but use whatever suits your standards. Be careful to use a naming convention for the hyperlink controls that makes it easy to generate the names for the hyperlink controls. For instance, I used these names for the hyperlink controls on my Breadcrumb control:

Crumb0, Crumb1, … , CrumbN

Creating a flexible display in HTML is considerably easier than it is in an Access form (see the sidebar “The Difference Between Access Forms and HTML”). It is, for instance, very difficult to calculate the length of text strings in twips; I center my crumb captions and give them a border to tidy up the design. To set a border and background for the whole control, I use a Box (called BoxCrumbs). In terms of dragging and dropping controls, that’s all that’s required. The engine layers can now size and lay out the crumbs and separators as necessary.

To set display properties for the crumbs (colors, borders, and fonts), I call the FormatBreadcrumbs routine from the OnLoad event of the Breadcrumb subform. This routine calls the InitCrumbStyles routine in the vbaBreadcrumbsConfig module. In order to modify the appearance of the control to have the control match your visual style, this routine is the only one that you need to modify to change the appearance of the Breadcrumb control.

InitCrumbStyles checks to see which parent form the Breadcrumb is in and then sets a variable for each of the display properties of the hyperlinks. This example shows how the routine could be written to tailor the appearance for different forms:

Public Sub InitCrumbStyles(nameParentForm As String)

    Select Case nameParentForm

        Case "ContactsFrm"

            hFtSize = 8

            hFtBold = True

            hFtName = "Arial"

            hCrmb_fgcolor = eColor.BlueDark

            hCrmb_bgcolor = eColor.BlueGrey

            hSep_fgcolor = eColor.BlueDark

            Box_bgcolor = eColor.GreyBorder

            Box_BorderWidth = 1

            Box_BorderColor = eColor.GreyDark

            captionSep = ">"

            Detail_bgcolor = eColor.GreyBlue

    End Select

End Sub

You’ll see that I’ve used an enumeration (eColor) to make the colors I use in my apps more user-friendly.

An interface is nothing without distinctive behavior. I must be able to customize how each instance of the hyperlinks responds to the user interface events. At the very least, I’ll want to handle the Click event for each crumb and have different code called for different instances of the Breadcrumb control. In the Breadcrumb subform module I put this code in the Click event handler for each crumb:

In the Private Sub Crumb2_Click()

    Crumb2_HandleClick nameFrm, nameParentFrm

End Sub

As with tailoring the interface, you may want to tailor the actions on a form-by-form basis. The definition for Crumb2_HandleClick is in vbaBreadcrumbConfig and uses a Select Case statement to allow you to call a different Sub according to which instance of the Breadcrumb fired the event. You could also apply this to any of the events that a hyperlink control can fire:

With Forms(nameParentForm). _


   Select Case nameParentForm

        Case "ContactsFrm"

             Call ThisInstancesSub

   End Select

End With

This is as much customization as I need at the interface level, and you can now drop your Breadcrumb control into any number of forms and customize each one’s interface and behavior by simply adding new cases to the Select statements in InitCrumbStyles and the Subs called by the click handlers for each crumb.

The engine layers

Events fired in the parent form change the Breadcrumb by calling the FormatCrumbsData routine (defined in the vbaBreadcrumbDataLayer module). This routine gets the data for display and passes it to the BuildBreadCrumbs routine (defined in the vbaBreadcrumbsEngine module).

The length of each caption and how many crumbs are currently visible affect how the control is laid out, and this is implemented by the BuildBreadCrumbs routine. The vbaBreadcrumbsEngine module includes a few constants to make the code more readable and efficient, just one public routine (BuildBreadCrumb) and two private helper functions (LeftLabel and CheckBreadCrumbs).

BuildBreadCrumb does what it says on the tin: It builds the Breadcrumb. To do this, a ParamArray of the required captions must be passed to the routine and the code loops through the crumbs, working out which ones to display and, if so, calculating their position. The calling routine just loads the captions into the ParamArray that’s passed to BuildBreadCrumb.

One of the flaws of the design is that you must pass in as many captions as there are hyperlinks (for instance, if the subform has five hyperlinks, you must pass an array with five positions even if you have only two captions to display). Also, the caption of the first crumb must not be a zero-length string. In a general case, this shouldn’t be necessary, and you’re free to amend the code in the sample. As in all other regards, BuildBreadCrumbs can cope with an unlimited number of crumb captions so this isn’t a huge task.

Positioning the controls is calculated from the length of each caption and how many crumbs are visible. I worked out the details of my implementation using trial-and-error hack and, to be honest, it ain’t pretty. If you have a better algorithm, I’d love to see it. However, for bold Arial font at a size of about 8-10 points (the only range I have used it in), my Breadcrumbs work just fine for typical caption lengths, despite some hard-coded corrections that I used in the calculation of the crumb widths. These corrections are the result of letting a novice up to his neck in trial-and-error write this code. When I started to write this article, I found to my horror that I hadn’t documented these corrections, so I’ve lost all the insights and knowledge that I gleaned from making my Breadcrumb control work. The result: I can’t explain why I picked these “magic numbers.”

The first thing BuildBreadCrumbs does is check the number of captions that have been passed in the Captions parameter. As I only have hard-coded four crumbs into my subform, I must raise an error on any attempt to write too many crumbs:

Dim ubCaptions As Long

Dim errorMessage As String

ubCaptions = UBound(captions)

If Not CheckBreadCrumbs(ubCaptions, errorMessage) Then

    MsgBox errorMessage & vbCrLf & , _

    "Fatal error: Too many captions"

    vbCritical, _

    "BuildBreadCrumbs Error!"

    Exit Sub

End If

Next, I set the leftmost point of my control to the position of my first hyperlink. This is the point relative to which I’ll set all the positions of crumbs and separators:

With Forms(nameParentFrm). _


     leftStart = .Controls("Crumb0").Left

Next, the real fudge. Using the four correction constants defined in the Module header, the width of each crumb is calculated using a cryptic formula and the width is then stored in the widthHyper Array. The problem stems from character widths, in a proportional font like Arial, not being constant. It’s all a bit unscientific, but, as Joe Hacker always says when he’s sitting on my shoulder, “The important thing is that it works!”

For i = 0 To ubCaptions

   lenCaptionHyper = Len(captionHyper(i))

   Select Case lenCaptionHyper

     Case Is < 11

      widthHyper(i) = _

       Len(captionHyper(i)) * widthCorrectionFont10 _

       + correction

     Case 11 To 20

      widthHyper(i) = _

       Len(captionHyper(i)) * widthCorrectionFont20 _

       + correction

     Case Is >= 21

      widthHyper(i) = _

       Len(captionHyper(i)) * widthCorrectionFont30 _

       + correction

   End Select


Now that I have the widths of my crumbs, I just need to position them. I do this by calculating the .Left property of each crumb, using the LeftLabel function (another hack):

.Controls(hyp).Left = _

    LeftLabel(i, leftStart, correction, _

    leftSep, widthHyper)

As I loop along, I also calculate the width of my background box, BoxCrumbs, so that it will surround the full length of the string of crumbs:

For i = 0 To ubCaptions

   widthBox = widthBox + widthHyper(i) + widthSeparator


Finally, I position and size BoxCrumbs to neatly encompass my crumbs:

  .BoxCrumbs.Left = leftStart

  .BoxCrumbs.Width = widthBox

  .BoxCrumbs.Visible = True

End With

The data layer

All the data layer has to do is build the Captions array that’s passed into BuildBreadCrumbs. As I want to be able to use different data for different instances of the Breadcrumb (that is, different parent forms), I use a Select Case statement based on the Parent Form name and write code for each case.

The sample download for this article includes an enhanced Switchboard form as well as a form for editing and maintaining the menus to be displayed on the Switchboard. Both forms use the Breadcrumb control to enhance the user experience.

The sample Switchboard derives its data from SwitchboardTbl (see Figure 4). The primary key is composed of two fields: NodePK and ItemNumber. If any Switchboard item has a submenu associated with it, its ItemNumber is 0.

Figure 4

As discussed in the “Hierarchical Data” sidebar, if I can assign a unique parent node to every item, it follows that I have a hierarchical data structure. In Figure 4 you can see that the Parent column stores the ID of the parent of every item (the Root has a special parent ID of 0). In this recordset, I can find and store the parent node ID of all the predecessors of any node with the following code:

Do While Not idParent = 0

  idParent = DLookup("Parent", "SwitchboardTbl", _

        "ItemNumber = 0 And NodePK = " & idCurrent)

  idCurrent = idParent

  MyCollection.Add idParent


With this loop (or variations thereof), if I know the last crumb in the control, I can find all the previous ones that I need to display. The following code allows me to find the caption for each node:

captionCrumb = DLookup("ItemText", "SwitchboardTbl", _

        "ItemNumber = 0 And NodePK = " & idNode)

Therefore, SwitchboardTbl models the logic of a Switchboard and allows me to output a Breadcrumb that displays each of the Menu pages the user has navigated through to get to the page the user is currently on. Clicking any crumb will take you back to a previous menu page, making this Switchboard really user-friendly.

To get this to work, my problem is putting the right captions in the right order into the Captions array before passing it to the BuildBreadCrumb routine. You can take a look at how this is done in the SwitchboardCrumbs routine in the vbaBreadCrumbsDataLayer module. Please note that this is proof of concept code, and that I haven’t attempted to optimize it for maintenance and legibility.

I highlighted the sort of issues and decisions that go into building reusable components for Access Forms. The end product is a modernized Switchboard that’s much easier for users to navigate. In the sample download, you’ll see that I’ve added some other enhancements to the Switchboard, such as allowing OpenArgs parameters to be passed into forms, as well as rollover behaviors (discussed in my October 2003 Smart Access article).

I hope that this article has also underscored the importance of documenting your designs: You never know when you’ll want to write an article based on your code for Smart Access…

Sidebar: The Difference Between Access Forms and HTML

HTML is text-based, so Web pages can string together as many Breadcrumbs as are needed. Access Forms, on the other hand, use controls on a design surface, the number of which is difficult to change at runtime, other than to make unused controls invisible. You could write a procedure to create hyperlinks as you need them, but this would rule out distributing apps as .mde files, as they don’t allow runtime design changes to forms. Having said all that, if you expose your users to more than four levels of nested hierarchies, they’ll hate you and not use your app, so this limitation isn’t crippling. I live in hope that XAML and Avalon, Microsoft’s new presentation technology, will deliver the best of both worlds…

Sidebar: Hierarchical Data

As I’ve stated throughout this article, the data structure you use for the Breadcrumb control must be “hierarchical” in nature. What does this mean? In essence, hierarchical data is tree-like: It can be classified into nodes (or branches) and leaves. A leaf is a node that has no children. Any node can have both leaves and child nodes. Every node must have a unique parent, with the exception of the Root. In summary, any node other than the Root must have a uniquely determined parent and its position in the hierarchy uniquely determined by its parent (no distinction between siblings!). If you can classify your data in this way, then you have a hierarchical structure and can sensibly display it as such. Please note that the hierarchical structure may have nothing to do with the relational structure of your data. As you can see in the Switchboard sample, my “hierarchical” data is all contained in one table.

 Your download file is called  Wrigley_Switchboard.accdb


Other Pages You Might Like to Read

Access User Interface Design

Six Rules for Effective User Interface Design

Handling Visual Complexity

Navigation Through Recursion

Displaying Hierarchical Data in a TreeView

About Garry Robinson

He is the guy who originally put this Smart Access web site together. Head to for more about him and his company. His main claim to fame is winning the Microsoft MVP award for services to MS Access from 2006 to 2018.
This entry was posted in Forms. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.