Download
1 / 34
340 likes | 688 Views
Automating Tasks With Macros. Design a switchboard and dialog box for a graphical user interface. Database developers interact directly with Access.
E N D
Design a switchboard and dialog box for a graphical user interface • Database developers interact directly with Access. • However, often you do not want the user of the database to interact directly with Access; rather, you would provide an interface that removes the user away from the Access interface. • A Graphical User Interface (GUI) is a collection of windows, menus, dialog boxes and other graphical components used to communicate with a program. • Often, the first view of a custom GUI is a switchboard.
What is a switchboard? • The switchboard is a form that opens when you start the underlying database and is usually used to provide the user with a set of choices. • This provides a well-organized interface for the user and eliminates the need for them to interact directly with the database window. • This also makes it possible to hide the functionality from the user so that they cannot make changes to the database objects. • The form you create for the switchboard is called a dialog box, which asks for user input in the way of a selection.
Run and add actions to macros • You can create a macro with a series of actions that will repeat these commands whenever it is invoked. • An action is an instruction to Access to perform an operation, such as opening a form or displaying a query. • You can also automate tasks with Visual Basic for Applications (VBA) but it is easier for a beginner to create macros. • With macros, you can simply select the actions you want from a list of actions. • Once the macro has been created, you can add actions to it by editing the macro in the Macro window.
Use the Macro window to add actions • It is within the Macro window that you will supply the action name (chosen from a list), any comments you want to make, and the arguments for the action. • Arguments are additional facts needed to run the action. • Each type of action has its own set of arguments. • A commonly used action is the Msgbox action, which will display a message to the user by way of a small form. • Another commonly used action is the FindRecord action that will find the first record matching a set of criteria.
Single-Stepping a macro • When you run a macro, the series of actions are executing one after the other. • When you are testing a macro, sometimes it is useful to run the macro one step at a time. • This is called single stepping and causes the macro to perform one action, then waits for you to step to the next action. • This allows you to gain a clearer view of how the macro is working. • When you single step through a macro, Access displays a dialog box called the Macro Single Step dialog box.
Use the Macro Single Step dialog box • This Macro Single Step dialog box displays details about the next action in the macro. • You have three choices as to how you want to respond: • You can step through the macro one step at a time • You can halt the macro • You continue the macro • Single-stepping is used to help you determine if you have placed the actions in the right order and whether the actions are working as you expect them to.
Create a macro • Start with a blank macro and then add the actions to it. • Drag an action from the database windows into the macro window. • Each type of object has a default set of arguments. • For example, if you drag a table into the macro window, the default arguments are to open the table in datasheet view in edit mode.
Create a macro • Drag as many objects as you want to the macro window. • You can either accept the default arguments or you can edit them to meet your needs. • Run the macro and observe the results of the macro.
Add a command button to a form • On the toolbox, you have a command button tool that allows you to place a command button on a form. • You can use the Command Button Wizard to help you place the command button or you can simply place the command button yourself.
Add a command button to a form • Click the command button tool on the toolbox, move your mouse to the form and draw a box where you want the command button to appear. • The default text on the command button will appear; however, you can change this and other properties on the command button's property sheet.
Attach a macro to a command button • Once you have added a command button to a form, you can attach a macro to it. • In most cases you will attach the macro to the command button's OnClick property. • Whenever the user clicks on the command button, the attached macro will be executed. • To attach the macro to the command button, right click the command button and then click on Properties to display the command button's property sheet.
Modify a macro’s property settings • You can change the OnClick property to the name of the macro you want to run when the user clicks the command button. • In the property sheet you can change the Caption property, which represents what is printed on the command button. • If you prefer to have a picture on the button, you can choose one from the Picture Builder dialog box. • For example, if the button will print a record, you might want to add a picture of a printer on the button.
Create a dialog box form • A dialog box is actually a form with which the user interacts. • You can add many different controls to the form such as command buttons, list boxes, text boxes, labels, etc.
Create a dialog box form • To create a dialog box, you begin by adding a blank form. • You will probably want to change some of the form properties before you begin adding controls to the form • To change the text that appears in the form's title bar, enter a new value in the form's caption property • There are several other properties that you might want to set for the form depending on the particular application • Each property can be set on the Property sheet
Add a list box to a form • On your dialog box, you might want to offer the user a list of choices. • A list box is a control that displays a list of values that a user can brows through. • You will usually add a label close to the list box to indicate what is contained in the list box. • To add a list box to a form, choose the List Box tool on the toolbox and then move your mouse to the form in the position where you want the list box to appear. • Once the list box is on the form, it can be sized and moved around just as you would any other control.
Use an SQL statement to fill a list box with object names • The standard language for querying, updating, and managing relational databases is SQL (Structured Query Language). • Whenever you create a query in Access, Access is creating SQL statements to display datasheets according to the Query specification. • If you want to view these SQL statements for a query, you can choose SQL view from the View menu. • SQL uses the SELECT statement to specify what data is retrieved from a database and how it presents the data.
Understanding SQL statements • Just like any other language, there are rules of the language called syntax. • In order to program in SQL you need to learn the rules. • However, you can read an SQL statement created by Access and get a pretty good idea of what the statement does. • The SQL statements match up with the query specifications; every choice made in the design window is reflected in the SQL statement.
Use the Switchboard Manager to create a switchboard • First, create all the macros you will need for the switchboard and then create the switchboard that will execute the macros. • You can use the Switchboard Manager to help you create the switchboard. • The Switchboard Manager allows you to specify what buttons should be on the switchboard and identify the command to execute when each of the buttons is clicked.
Switchboard considerations • The Switchboard Manager allows you to create only one switchboard for a database; however, the switchboard can contain multiple pages. • The main page of the switchboard will display when the switchboard opens. • You can place buttons on the main page that will cause other pages in the switchboard to open. • The switchboard manager is available on the Database Utilities option on the Tools menu.
