databasedev.co.uk - database solutions and downloads for microsoft access

UI Builder for Microsoft Access
Create attractive databases with the essential features any Access application needs in minutes, without becoming an expert in Visual Basic. Configurable menu layouts, color schemes, and advanced features make UI Builder a must-have for every Access user. Free trial available

Switchboard Forms in Microsoft Access

What is a Database Switchboard -

A switchboard is essentially a Microsoft Access form that allows you to facilitate navigation or perform tasks within your database application. This form is basically a customised menu that contains user-defined commands; using either buttons, labels, images or hyperlinks, that invoke actions that will automatically carry out tasks for you such as opening other forms, running queries or printing reports.

The form will typically contain various command (buttons), which your users can then click on to carry out the pre-defined actions that you have associated with these commands.

You can create a switchboard form using various manual methods or by using the Microsoft Access Switchboard Manager. The form can use either VBA procedures, stored in modules behind the form or hyperlinks that either don't contain any associated code, or that can invoke functions from standalone code modules.

Using a switchboard form in your Microsoft Access database application allows you to tie together all of the other objects that you have created for your database users from a single form, and removes the need for the users to have to openly navigate from within the database window. You can also use this as the interface for your application, removing the chances of your users tampering with any of the objects that lie behind the scene of your application.

Using buttons on your switchboard form can also replace the many possible steps that it would take the user to navigate around the objects in the database. To simply open a form in the database, the user may need to switch to the database window, choose the Forms tab and open the form from there. Using a command on the switchboard interface, the user now only has to click one button to carry out the same action.

Creating a basic form for the Microsoft Access Switchboard

As switchboard forms are generally used as the application menu in Microsoft Access, they tend to contain a limited number of form controls. Typically, you will find they contain command buttons, labels, object frames (OLE objects, such as images) and other layout objects such as lines and rectangles. Usually, the switchboard tends to lack in other types of database form controls, such as text boxes that expect user input or checkboxes, combo boxes or lists.

Consider the following example of a Microsoft Access application switchboard:

Showing an example of a switchboard including several command buttons
Showing an example of a switchboard including several command buttons

You will see that this form contains various elements as previously mentioned; command buttons, images, labels and lines.

Navigating with Command Buttons and Code Behind the Form

Command buttons are the simplest type of form control, offering navigation from your switchboard with VBA procedures attached to the Click events of that command button. You can use this functionality to perform a multitude of tasks, including:

  • Opening and displaying other Microsoft Access forms
  • Opening and previewing or printing reports
  • Applying filters on a form or activating a search procedure
  • Quitting the application and exiting Microsoft Access

Using command buttons from our switchboard allows greater control than using hyperlinks as switchboard commands. Using hyperlinks as navigation merely allows you to transfer focus to another object from the current object.

When creating your switchboard for the first time, as simple way to add your command buttons is to use the Command Button Wizard. This will walk you through all of the steps and create the basic code that will allow you to open one form from another.

If we are on our main database form that we are going to use as the switchboard and want to create a button that will open another form, we add a command button from the tools available in form design, ensuring that the wizard is active.

The event procedure that is created for the On Click event of the button on the main form will be similar to the following VBA procedure:

Private Sub cmdOpenfrmStudentInformation_Click()
    On Error GoTo Err_cmdOpenfrmStudentInformation_Click


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmStudentInformation"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmStudentInformation_Click:
    Exit Sub

Err_cmdOpenfrmStudentInformation_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenfrmStudentInformation_Click

End Sub

This procedure, attached to the command button (cmdOpenfrmStudentInformation), will open the form specified (frmStudentInformation), whilst also leaving open the switchboard form.

If we want to close the switchboard form, whilst opening another form, we need to add a single line of code beneath the DoCmd.OpenForm action. We add the following to close the switchboard:

Docmd.Close acForm, "frmYour_Switchboard"

This shows a very simple example of adding command buttons to your switchboard to use as navigation in your database application.