Microsoft Access Switchboard Form
"Every system should really use an Access switchboard form to help users navigate from one task to another without being left wondering what to do next..."
An Access Switchboard form is a type of Microsoft Access form that allows users to navigate to and from other forms, queries and reports without the need to gain access to the Access 2003 (or earlier) database window or Access 2007/2010 navigation pane.
This type of form will contain command Buttons or Hyperlinks to allow other objects to load and open from pre-defined actions supported either with a macro or VBA procedure.
Other controls could act as an (click) action too including Labels and Images to give that extra polished look and feel.
There are several ways to create an Access Switchboard form and versions up to and including Access 2007 have a utility called Access Switchboard Manager which will guide you through the wizard to create a quick but basic menu system form.
In Access 2010, this has been updated to a new feature called Navigation Form which generateS tabs.
In this article, I going to build it manually as this is about form designing showing how to add controls and actions (using embedded macros).
Additionally, I will also show you how Access 2010 has a new feature called Navigation forms which is an alternative way to create a menu system.
I'm using Access 2010...
Access Switchboard Form
In this example, I want to load a switchboard form when you open the Access database file (ACCDB) using the Display Form feature in Access options.
Here's a quick sneak preview of the form I want to finish up with:
Note: This form example was illustrated for the Dialog Box Form example and the start-up form would therefore be pointed to the login form first. This article assumes there is no login required.
Access Switchboard Form - Step 1: In this version of Access (and as with Access 2007), you now have the standard template form called Modal Dialog which is a form with two button controls and certain properties set for you.
From the Ribbon Bar choose the Create tab and click the More Forms icon which expands the drop-down and choose the Modal Dialog icon.
This will appear with the detail section (no headers or footers), two Button controls (OK and Cancel) and other properties preset for you.
Note: In previous versions of Microsoft Access (2003 or earlier), you had to create a standard blank form (design) and set the properties manually - there was no template available (unless you created one!).
What's Modal? Modal means the freezing of a window (or form) which means that you have to end the window before being allowed to continue - It's just rude to interrupt a conversion (dialog)!
Access Switchboard Form - Step 2: With the dialog form design canvas, let's set some of the other properties that we need. Any property from can be found via the Property Sheet pane.
First, make sure the 'Property Sheet' is visible by locating the Property Sheet icon from the Design tab on the 'Ribbon Bar' where a pane (normally on the right hand side) is displayed showing the properties.
In this example we do not require any data to be bound to the form as we are not going to choose and store record information. Therefore the 'Record Source' is left blank.
Set the Caption property of the form (making sure you have first selected the type 'Form' in the 'Property Sheet' pane to "Menu Switchboard".
Click here for more information about form designing and their controls.
You will need to resize the form's dimensions first to approximately 10.2cm wide by 7.7cm high (Height property is found via the 'Detail' section of the 'Property Sheet').
Delete the OK button it's not required. With the Cancel button, we need to set the following properties:
| Tab || Property || Value |
|Format ||Caption ||Exit |
| ||Width ||5.582cm |
| ||Height ||0.714cm |
| ||Top ||8.697cm |
| ||Left ||1.099cm |
|Other ||Name ||cmd_Exit |
Delete the [Embedded Macro] attached to the Event - On Click of the above button. We will cover this later.
Tip: It is worth getting into the habit of learning to save your work as you develop to avoid any heartache of losing your hard work should Access fail to respond and crash - which can happen! Save your file as frm_MenuSwitchboard.
Access Switchboard Form - Step 3: Now we are going to add 7 new controls; six Command Buttons and a Label.
In fact, the number of Command Buttons you add is up to you really. How many forms do you want to open from this form? You can decide - I'm sticking with six!
Since I already have the dimensions of the Command Button required (from the Exit button), I'm going to simply copy and paste six times.
Select the Command Button (Exit) and press CTRL + C ('Copy' command keyboard shortcut) and then click into the Detail section first before pressing CTRL + V ('Paste' command keyboard shortcut) six times.
Six new Command Buttons should be visible.
Again, I will add code to each button (to open another form) later on. For now, let's position them as required. As a guide space them out and centralise them.
You can select all the buttons first and then right mouse click the selection or better still from the Ribbon Bar locate the Arrange tab and from the Sizing & Ordering section use the icons Size/Space and Align to position neatly into place.
The properties for each Command Button are already complete with the exception of a meaningful name and its caption. Do not leave them unnamed; it makes it untidy and harder to manage. Use the prefix cmd_ followed by a useful Name (property) i.e. New Customers form might have "cmd_NewCustomers" and its Caption property to show "New Customers...".
Add a Label and set the caption to "Menu" and position near the top of the Detail section.
Access Switchboard Form - Step 4: The next step is going to deal with some coding (for all seven Command Buttons).
I'm going to show you the new Macro Designer tool for Access 2010 (previous versions of Access used an older style interface). In all cases, the actions in this example are the same (or the equivalent keywords) to perform the same action.
The Exit button will simply close the database altogether. The other buttons will open the chosen form followed by the closing of this form therefore two actions will be applied to the [Embedded Macro].
In design view mode, select the Exit button and from the Property Sheet pane locate the Event and for the event On Click click into to it and the button control to its immediate right (button with three dots - ellipses) and from the pop-up screen choosing the Macro Builder option.
You are now taken into the Macro Designer tool for the selected control's event.
Choose the action CloseDatabase which has no arguments (extra parameters) and then close and save the prompt that follows.
Alternatively, you could use the action QuitAccess which will close the database and the Access application too.
The other [Embedded Macro] is to handle the opening of a selected form and then close this form (frm_MenuSwitchboard) as we want to navigate away from the form. It's worth mentioning that this is not a mandatory action as this form could stay open but behind the pop-up form you are going to call. But there are two reasons why I'm opting to close it:
1. If this form is a modal pop-up form it will always stay on top and the calling form will sit behind it.
2. I want to show you an action macro with arguments!
Select the Command Button and create a new macro for the On Click event.
The following illustration shows you two actions; the first opening a form called frm_Customer_Orders and the second action closing this form:
Close and save the prompt and repeat for each button action required.
Save the form and test.
Note: In previous versions of Microsoft Access (pre 2007), There were no [Embedded Macros] but were stored as separate (Macro) objects and could be found in the Database Window and created and edited in the same way as described above. Also, action keywords may vary slightly - just look at the list available list for the matching commands.
Access Switchboard Form - Final Step: Make any alterations to the look and feel as your see fit and if you want this form to automatically start up when you load the database file, there is an Access option available.
Close all forms (and any other objects that maybe open).
Go and choose the File tab which takes you into Microsoft Access's application menu section and click the Options icon (second from bottom) and you will be taken to Access Options screen.
Click the second item Current Database to view the following options:
Select the form frm_MenuSwitchboard from the Display Form: option and click the OK button to save changes.
Now, the next time you start this database file, it will load the Access Switchboard form.
Note: This will vary for different versions of Microsoft Access in terms of how to find this option - look up the applications help system for further guidance.
Access 2010 Navigation Form
New to Microsoft Access 2010 is the Navigation Form which replaces the Switchboard Manager utility (a wizard based table and form builder tool).
With this type of Access switchboard form, the design method is the same as with designing most forms covered in this article and other topics in Access Forms section.
Here are the simple steps which gives you a taste of what is different about this type of form.
Step 1: From the Ribbon Bar click on the Create tab and under the Forms section click the Navigation drop-down icon to reveal the various types of forms to pick.
I going to choose the Horizontal Tabs icon and the following screen appears with Form Layout Tools three tabs appearing on the Ribbon Bar containing Design, Arrange and Format tabs.
Step 2: Next, we need to add and name each of the horizontal tabs.
I'm going to click on the only (first) tab labelled [Add New] and single click again to edit the name.
The first tab is going to be called Customers as I want to have a division to select and look at customers for each country (UK, USA and Canada).
If you add a label (name) which has the already been used for a form or report, that object is automatically added to your new tab.
I'm going to delete this form as it's not what I'm after. I will be adding to this is my next step.
To remove an unwanted form or report from a tab, first load the Property Sheet pane via the Design tab, Property Sheet icon (from the Ribbon Bar).
Make sure you have clicked on the tab (Customers) which is a Navigation Button and in the Property Sheet pane under the Data tab delete the value set in the property: Navigation Target Name (which is set as Customers).
Add a new label to the second tab and call it Orders. Add one more new tab and call it Products.
Make sure all three tabs are currently empty and if necessary use the above method to delete any automatically targeted source names.
Step 3: Next, I'm going to add a second Navigation Control to sit just below the horizontal one already in place. This is because I want to link the second control's tabs with the top line of tabs to list subsets of information (i.e. Customers for UK, USA and Canada).
Back in design view, locate and click the Navigation Control icon from the Design tab on the Ribbon Bar. Position anyway in the form detail (away from the first Navigation Control) for now.
Back in Layout View you should see a new tab with the caption [Add New]. Make sure the Customers tab from the first Navigation Control is selected and then drag and drop your first report or form onto the second control tab.
I'm going to add three reports (one for each country) so that the Customers tab will link to each report added.
Step 4: Repeat for each main tab by first clicking on the main tab and then drag n drop desired forms or reports into the second tab.
Automatically, each main tab when clicked will filter to the second tab's related objects.
If you then switch back to design view, you can resize and position the second tab into place.
Run the form and modify the format tabs to taste.
I've change the button, shape and captions...
Return from Access Switchboard Form to Access Forms
Return from Access Switchboard Form to About Access Databases