The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
May 2011

Sometimes Things Aren't What They Appear - We Go Behind the Switchboard Façade

I recently had the pleasure of reading Alex Haley's book, Roots. It's a captivating story in which Haley traces his family tree back to the West African country of The Gambia. In the late '70s, it was made into a television miniseries that won wide acclaim.

Haley went beyond the standard genealogy research in tracing his ancestry. He relied on stories that had been handed down from generation to generation; stories he traced all the way back to his slave ancestor, Kunta Kinte, who was born in the West African Gambian village of Juffure. He was so fascinated by the stories of Kunta Kinte's capture and enslavement that he decided to visit Juffure and do some first-hand research.

According to Haley, he visited the village and spoke with a Griot, a village historian, by the name of Kebba Kanji Fofana. According to Haley, the Griot told him of Kunta's boyhood, life in the village, and eventual capture by the slave traders. Haley took these facts, wove them into the oral history from his relatives and integrated these findings with his research to produce Roots. If you haven't read the story or seen the miniseries, I urge you to check them out. Both the book and the mini-series paint a rich tapestry of American slavery as well as life in the Gambian village.

There is a problem with the story, however. I was a Peace Corps volunteer in Senegal, West Africa in the late '70s, where I lived in a small village not too far from The Gambia. While in Senegal, I got to know several Griots. They are in fact historians, but they are more - they are also jokesters, tricksters, and pranksters. The Gambia, Kunta Kinte's home country, is surrounded by Senegal. On a trip out of Senegal, I visited with another Peace Corps volunteer in The Gambia not far from Kunta Kinte's home village. This volunteer spoke to the Griot Fofana about Alex Haley's visit.

As it turns out, the Peace Corps volunteer learned that the story that Alex Haley was told was much more fiction than fact. When Haley visited the village, he had a translator to act as a bridge between Haley and the Griot. Haley related the family stories about Kunta Kinte and told the Griot that he wanted to learn more of his ancestor. At this point, the Griot turned to the translator and said "So this white guy (as far as the Griot was concerned Alex Haley looked white) wants me to tell him a story about his ancestor." The translator confirmed this and then, in best prankster mode, made up the richly detailed story of Kunta Kinte's boyhood, village life, and eventual capture by slave traders - a story that was no more fact than what you might find in a well-written novel.

In Haley's defense, he does not present roots as pure history. The term that he uses is faction - a mixture of fact and fiction. Although Roots is not entirely true, the family history told therein still has great value and is an entertaining read.

In this Issue
  • Tip of the Month: Coding Corner - Get the Description for an Access Error
  • Things That Are Not What They Appear To Be Can Still Be Useful
  • The Façade
  • Behind the Façade
  • Conclusion

  • Things That Are Not What They Appear To Be Can Still Be Useful

    This month, we'll take a look at something in Access that, like the story of Roots, isn't what it appears to be. No doubt you've used the Access switchboard, which I described in the March 2005 Wizard; it's a nifty tool. From the looks of things, you would think that each button in the menu has a specific label and code for each potential menu item.

    In fact, it's a façade; a very powerful façade, but still a façade. But just as Haley's story about his supposed ancestor Kunta Kinte is useful, if perhaps not entirely true; the switchboard, although it is not what it appears to be, is still a powerful tool. This month, we look behind the curtain and get into what's really going on in the Switchboard form.

    The Façade

    Here's a menu I use for keeping track of Recipes:

    If we look at the form in design view, we'd expect to see eight buttons with labels, the first of which would be "Find Recipes By Category." Further, we might think the event associated with that button would be along the lines of

    docmd.OpenForm "frmRecipeByCategory"

    When we go to the form's design view we see

    A menu with eight buttons but no labels. If we look at the on-click event of the first button we see

    Behind the Façade

    What's going on? Certainly not something simple and straightforward. Let's take a look at what happens when the form opens by looking at the open event for the form.

    Private Sub Form_Open(Cancel As Integer)

    On Error GoTo Form_Open_Err
        'Move to the switchboard page that is marked as the default.
        Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
        Me.FilterOn = True

        Exit Sub

        MsgBox Err.Description
        Resume Form_Open_Exit
    End Sub

    The first line "On error..." is a standard error handling statement.

    The next line, "Move to the switchboard page..." is a comment because it is preceded by an apostrophe.

    The next two lines are where the action takes place:

        Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
        Me.FilterOn = True

    First the form is filtered to item Number 0 and Argument = Default. Sounds a bit like gibberish until you dig a little further and check the Record source of the form "Switchboard Items" - the Switchboard table.

    So the record source is a single record from the switchboard table (which ultimately changes the caption of the form). Well, how then is the form populated so that the user sees something when the form is opened?

    A close tracing of the code shows that it's a combination of the on current event, an event that also gets trigged when the form opens, coupled with a piece of code named FillOptions. This particular chunk of code reads from the Switchboard Table and fills in the caption next to each button based on the second column in the Switchboard table "Item Number."

    When the user clicks the button, the Handle button code fires, once again inspects the table and item number to determine what type of command is requested, and then executes the code based on the entry in the table. It's really very clever.

    In essence, the developer uses a single form and manipulates the appearance and the behavior based on the entries in the Switchboard table. This approach makes the form flexible since it is driven from a table, which is a much easier approach to maintain and expand once the initial design and coding is complete. Essentially, the form has been "genericized" - it has become flexible and powerful, but not at all what it appears to be.


    So the next time something appears to be simple and straight forward, keep in mind that things are not always what they appear to be. As the story of Kunta Kinte is good a read, but is not necessarily true. What you see in Access may appear to be one thing, but in fact may be something completely unexpected.

    Tip of the Month: Coding Corner - Get the Description for an Access Error

    If you do any VBA coding in Access, you may run into obscure error codes coming from the Visual Basic engine.

    For instance, you may get a message "Error Number 3203." In many instances, you also get a description that will give you a clue as to the source of the error. Occasionally, however, you will get the error number by itself. Most times, you can still get to the description with a single command in the code window. Simply press Control-G to bring you to the Immediate window and type

    ? AccessError(3203)

    Of course you would substitute the error number you're trying to understand. In the case of 3203, the system will say "Subqueries cannot be used in the expression (|)" - still a bit cryptic, but much further along the road to understanding than the error message all by itself.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886