Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
Behind the scenes
How is it done
The Switchboard Manager in Access is a useful tool and you do not need to know anything about its internal mechanisms in order to be able to build your own system of switchboards. It does however show up some useful techniques in Access and in VBA program code. If you know how the system works then you can modify it to extend its capabilities and perhaps use some of these programming techniques elsewhere.
The system is based on a table and a form, both named "Switchboard". The table defines the structure of the switchboards, the form defines their appearance. If you open the Switchboard table you'll see that it has a simple structure:
This table has five columns:
- is the same for all rows because this system only has one switchboard and all the entries belong to that switchboard. More complex systems can have subsidiary switchboards with entries of their own.
- is the row number of this button on the switchboard. Together with the SwitchboardID it forms the primary key for the table. You can rearrange the order of the buttons on the form if you alter these values. The first row has an ItemNumber of zero because it is the switchboard itself.
- is the text that will appear alongside the button. For a switchboard it is the caption of the form.
- is a code that identifies one of the commands available. This sample shows that 3 must mean "Open FOrm" and 6 must mean "Exit Application". The others are documented in the VBA code of function HandleButtonClick of the Switchboard form.
- is the name of the form, report or switchboard to run. The final row has no argument because the command "Exit Application" does not need any more information.
The key feature on the switchboard form is the column of eight buttons. VBA functions on the form determine the appearance of these buttons at runtime and respond to the users' clicking the buttons.
Make the buttons visible
When the form loads, the FillOptions method creates a recordset from the Switchboard table and loops through it to decide which buttons should be displayed:
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
The loop starts at 2 because the first button must always be visible. A second loop then sets the caption associated with each button by reading the ItemText field from the recordset:
Deciding what action to take
Each button calls the HandleButtonClick method in its Click event and each passes its identifying number into that function. Inside the function there's some VBA code using DAO to find the record in the Switchboard table:
Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
It retrieves the contents of the Command field from that row and uses that number in a Case structure:
' Open a form in Add mode.
DoCmd.OpenForm rst![Argument], , , , acAdd
' Open a form.
' Open a report.
DoCmd.OpenReport rst![Argument], acPreview
In each of these cases, the DoCmd object open the selected form or run the selected report as appropriate.holds the name of a form or a report in the database and the methods of the
The constants such asare all defined at the top of the HandleButtonClick function. If you need to extend the range of actions available to the switchboard you could add a new constant, for example one to run an export into an Excel spreadsheet:
and extend the VBA case structure with a call to DoCmd.TransferSpreadsheet(...).
I can't guarantee that anything I've said on this page will work with your database.The usual warnings apply if you decide to bypass any wizard:
- Make sure you've got a good backup before you start.
- Read the details in Help.
- Make small changes and test often.