Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

A Macro is a set of actions you ask Microsoft Access to perform in order to automate some behavior in your application. A macro can be used to open a table, a form, a report, or a Data Access Page in any view of your choice.

By default, macros don't involve any programming. So, what you need to know is how to create them, then how to run (execute) them, and finally, how to use their behavior in your application.

The Basics Of Macros:

Macros are provided in various flavors for different purposes. The apparently most basic macro you will use can help you display a message to the user.

Open the Music Collection5 database. In MS Access 97 click the Macros tab. In 2000, click the Macros button.

Click the New button.

Click the combo box under the Action column to view some of the macros available. Type MsgBox (you could just type ms and press Tab). In the Comments field, type A simple message to the user. In the message box, type: Welcome to my Music Collection database. Enjoy!. Set the Beep field to No. Set the Type to None. The title of the message box will be Music Collection. Save the macro as mcrMessageToUser. Close the Macros window.

Double-click the mcrMessageToUser macro to run it.

Click OK to dismiss the dialog.

Close the Music Collection3 database.

Opening And Closing Objects:

You already know that macros can be used to automate some actions in your database. Now we are going to start executing some actions in our databases.

Open the DCMS1 database.

Press F11 to access the Database Window.

Click the Macros button, then click the New button on the Database Window.

In the Action column, select OpenForm. In the Comment field, type Used to open the Employees form. In the Form Name, choose Employees. Set the View field to Form and the Window Mode to Normal. The other fields will stay empty for now. Save the macro as mcrOpenEmployeeForm. Close the macro window.

Create another macro using the Close action. In the Comment field, type Used to close the Employees form. Specify the Object type as Form and the Object Name as Employees. Leave the Save field to Prompt. Save the macro as mcrCloseEmployeesForm and close the macro window.

Double-click the mcrOpenEmployeeForm macro to execute it. It should open the Employees form.

While the Employees form is opened, on the main menu, click View -> Design View.

If you don't have the Toolbox, on the main menu, click View -> Toolbox. On the Toolbox,

make sure that the Control Wizard is pressed.

Click the Command Button. Click on the form somewhere on the intersection of horizontal 4 and vertical 1.

In the Command Button Wizard, in the Categories click Miscellaneous. In the Actions list, click Run Macro. Click Next. Choose the mcrCloseEmployeesForm macro. Click Next. In the Button page, click in the Text box and replace the text with Close. Click Next. Name the button CloseEmplyeeFormBtn. Click Finish.

Page Up - Page Down:

A macro can be used to navigate up and down on a form with the click on one button.

Open the Associates1 database. The Associates are a group of people who organize to save money in an usual way. Every week, two weeks, or month, they get together and each member deposits a certain amount of money in the "cashier" where there is a minimum. The money is registered and deposited in an official bank account.

At a set period, which could be at the end of the year or in the middle of the year (they decide on the period when it would happen), the "cashier box" is broken and everyone gets what he or she has accumulated for the period. During the year, members are allowed to borrow money for emergency situations, and they will reimburse with a percentage. Above the minimum, members are allowed or encouraged to deposit any amount.

A variant of this system of saving would be to "give" that money to one of the members every week, every two weeks, or every month, in a rotation fashion, until the "circle" is complete. To participate in this organization, one has to be a registered member first. Our Associates database helps to register members and to keep track of all transactions involved in the organization.

Double-click the frmAccountTransactions form to open it. The top of the form shows the transactions related to the particular account displayed. The bottom of the form would allow the user to review information about the account owner. Notice that this form is too long to display everything at the same time on the screen. So, we will create two macros that would allow us to go up and down on the form (could you believe this action could be performed with only one line of VB code?). Close the form.

In the Database Window, click the Macros button.

Click the New button.

In the Action column, choose GotoPage. In the Description, type View Transactions related to this Account. In the Page Number, type 1.

Save the macro as mcrAccountTransactions.

On the main menu, click Insert -> Macro.

In the Action column, choose GotoPage. In the Description, type View Information about this Account's holder. In the Page Number, type 2.

Save the macro as mcrAccountInfo.

Now that the actions have been defined, all you have to do now is assign them the appropriate elements to perform. To do that, we will associate each macro to a button. Open the frmAccountTransactions form in Design View.

Scroll down, using the vertical scroll bar. Don't resize the form.

From the Toolbox, click Page Break. On the form, click at 2 7/16 of the vertical ruler just right of the vertical ruler. If you are using MS Access 97, place the Page Break right to dimension 3 of the vertical ruler (I didn't create these two exercises on the same day). Save and close the form.

On the Toolbox, make sure the Control Wizards is not pressed. Scroll down until you can see the Form Footer section.

On the Toolbox, click Command Button. Drop a button in the middle left side of the Form Footer section.

Right-click the button and choose Properties. Click the All tab. Name the button TransactionsBtn. In the Caption, type Transactions. In the Properties dialog, scroll down until you can see the On Click field. Click the combo box in the On Click field and choose mcrAccountTransactions.

From the Toolbox, click the Command Button again and click right of the first button.

In the Properties dialog, name that button AccountInfoBtn. The Caption will be Account Info. The On Click event will be mcrAccountInfo.

Close the Properties dialog. Switch to Form View.

Click the buttons to see how they work.

Switch back to Design View. When writing this database, we had decided that we would not allow the user to update an account's information from the frmAccountTransactions, to make sure that the records are separate.

If the user wants to update an account's data, for example if a member has changed her address or home telephone number, we will ask the user to "call" the registration form where data can be edited or updated. Remember, the only actions allowed to be performed from the transactions form are the transactions.

In the frmAccountTransactions Design View, click the Command Button from the Toolbox. Click right to the other existing buttons. On the main menu, click View -> Properties. On the Properties dialog, name that button UpdAccountInfoBtn. Set its Caption to Update Info. Close the Properties dialog.

Right-click the new button and choose Build Event...

From the Choose Builder dialog, double-click Macro Builder. Name the macro mcrUpdAccountInfo. In the Action column, choose OpenForm. In the Comment, type Update current account or create a new one. In the Form Name, choose frmMembersRegistration. Save and close the macro builder window.

Switch to Form View.

Click a few records. Move to record 4, then click the Update Info button. We have a problem here. If the user is on record 4 and wants to update info for member 4, the Update Info button should take her to record 4, and not to the beginning of the records.

Close the form. In the Database Window, click the Macros button. Click the mcrUpdAccountInfo macro, then click the Design button. What we want to use as a reference is the member's account number. When the user's is on a particular record and click the Update Info button, we want the "call" to land on the corresponding account number in the other form. The fact is, this will be on another form (although both forms use the same data), so the safest way to configure it is to build it.

In the Where Condition field, type [AccountNumber]. You could type the whole expression here, but to reduce the likelyhood of making a mistake, we will use the Expression Builder.

In the Where Condition field, click the Build button. Since the [AccountNumber] is highlighted in the Expression Builder dialog and we only want to add something to it, click at its end; you cursor is now at the end of [AccountNumber]. Click the = sign. In the left list, double-click the Forms folder. Double-click All Forms. Click frmAccountTransactions. In the middle list, double-click AccountNumber. Now your expression should read:

[AccountNumber] = Forms![frmAccountTransactions]![AccountNumber] .

Save and close the macro. The problem should be fixed now.

Save the frmAccountTransactions. After playing with it, close the form. Also close the database.

Opening Database Components:

The most usual use of macros is to open forms and reports.

Open the Music Collection 7 database.

From the Database Window, click Macros. Click New.

In the Action column, select OpenForm. In the Comment field, type Open the Albums form. In the Form Name field, choose frmEditAlbums.

Save your macro as mcrEditAlbums, then close it.

On the Standard toolbar, click the New Object arrow button and choose Macro.

In the Action column, select OpenReport. In the Comment field, type Preview the the Albums report. In the Report Name field, choose rptAlbums. In the View field, choose Print Preview.

Save your macro as mcrPreviewAlbums, then close it.

Open the frmAlbumsAndTracks form in Design View.

On the Toolbox, make sure the Control Wizard button is not enabled. Click the Command Button on the Toolbox and click left in the Footer Section.

Right-click the new command button and choose Properties. Name the button EditAlbumsBtn with a caption of Edit Albums. Set its Default to Yes. For its On Click event, choose mcrEditAlbums.

Add another button named PreviewAlbumsBtn with a caption of Preview Albums and an On Click event to mcrPreviewAlbums.

Add another button. Right-click that button and choose Properties. name the button PrintArtistsBtn with the caption Print Artists Names. Click the Events tab. Click the Build button in the On Click field. Click Macro Builder and click OK. Name the macro mcrPrintArtistsNames and click OK.

In the Action column, select OpenReport. In the Comment field, type Preview the the Artists Names report. In the Report Name field, choose rptArtists. In the View field, choose Print Preview. Save the macro and close it. Resize the button to fit the caption.

Multi-Action Macros:

Sometimes you will want the call of a macro to perform more than one action. In a simple form, we will display a message indicating to the user what he can do when he clicks the Update Info button.

Open the Associates1 database again.

From the Database Window, click the Macros button. Click the mcrUpdAccountInfo macro, then click the Design button.

In the 3rd line of the Action column, choose MsgBox. In the Comment, type Display an indication to the user. In the Message field, type The following form allows you to update information or to register a new member. Set the Beep field to No. Set the Type field to Information. For the Title, type Member Information.

Save and close the macro.

In the Forms category, double-click the frmAccountTransactions form. Click the Update Info button.