Events and Macros

Events Fundamentals  

Introduction

An event is the result of initiating an action on an object. The action could be as simple as positioning the mouse on an object even without clicking. The action could consist of (left) clicking, right-clicking, dragging (with the mouse, or dragging with the mouse while holding a key on the keyboard), double-clicking, rolling the wheel (on a mouse), typing (with the keyboard), pressing a key, scrolling (a scroll bar), etc.

Sending a Message

When an action is performed on an object, the object must send a message to the operating system (OS), letting the OS know what happened. The OS must then decide what to do, whether to respond to the message or send the message to another object. Obviously, for a message to accomplish its purpose, it must carry some information. Because there are different types of objects and there are various types of actions that can be performed on them, there are also various types of messages.

Although events primarily have to do with computer programming, in our lessons, we will know write code. Instead, we will use other friendly means that Microsoft Access provides to deal with events.

To access the events of an object, display the form or report in Design View. In the Property Sheet, click the Event tab. This would display the names of the events associated with whatever is selected on the form or report:

The names of most events start with On, which means, "at the time this is done". Because most, if not all, events have to do with time, an event is said to be fired. That is, the object fires an event at the time something happens. When an event is fired, if necessary, it gathers the necessary message to be transmitted where appropriate.

Again, remember that most events have to do with time (they are occurrences). In some cases, something must be done before the actual action is applied. For this reason, the names of some events start with Before. When something must be taken care of after the action has applied, the event that must be used to implement the desired behavior starts with After.

Events Categories

Some events are general and they are shared by most objects. For example, almost all objects can be clicked. Some events are based on a category objects, such as only objects that can receive text. Some other events are very restricted because their object needs particular functionality.

As mentioned already, some events are shared by almost all objects.

Probably the most common event fires when an object is clicked. The event is called On Click. This event doesn't carry any information other than letting the target know that the object has been clicked. This event doesn't identify what mouse button was used or anything else. Therefore, use this event for a normal click action.

Another common event of Windows controls is fired when an object is double-clicked. This event is represented as On Dbl Click.

Some controls must be clicked before being used. A user can also press Tab a few times from one or other controls to move focus on a particular control. In both cases, when a control receives focus, it fires an event named On Got Focus:

Text-based controls are controls that a user can click to type text. Those controls are the text box and the combo box. When such a control is clicked, whether it already contains text or not, it fires an event named On Enter. Like On Got Focus, the On Enter event indicates that the control has received focus:

After using a control, a user can press Tab. In this case, the focus would move from the current object to the next control in the tab sequence. The control that looses focus fires an event named On Lost Focus. If the control is text-based, the control fires the On Exit event.

The mouse and the keyboard are the most regularly used objects. In fact, some applications can be completely used with the mouse only. This makes this object particularly important. In Microsoft Access, the mouse is responsible for at least three events:

If the user positions the mouse on top of a control but doesn't click, the control fires an event named On Mouse Move. Remember that this event fires when the mouse passes over an object, whether the user is doing anything on the object or not.

When the user positions the mouse on an object and presses a (mouse button), the object fires the On Mouse Down. To make its action effective, the message of this event holds the following pieces of information:

• Button: This is a natural number that specifies the button that was clicked
•  Shift: This is a natural number that specifies whether the user was holding a special key (Ctrl, Alt, or Shift) when the mouse button was pressed
• X: This a decimal number (Single type) that represents the left coordinate of the mouse cursor with regards to the container, which can be a form header, a report header, a detail section, a report footer, a form footer, or any object that can hold a control
• Y: This is a decimal number that is the vertical coordinate of the cursor from the top border to the current area where the mouse landed

If a user had pressed a mouse button, when she releases the (mouse) button, the control fires an event named On Mouse Up. The message of this event carries the same types of information as the On Mouse Down event.

There are various ways a user uses the keyboard. For example, a user can press a key on a control:

The user can press Tab to move focus from one control to another. A user can also click a text-based control and start typing. Either way when the user presses a key, the control that has focus fires an On Key Down event. The message of this event carries two pieces of information:

• KeyCode: This is a natural number that represents the integral (ASCII) value of the key that was pressed
• Shift: This is a natural number that specifies whether the user was holding a special key (Ctrl, Alt, or Shift) when the actual key pressed

After pressing the key, when the user releases or depresses it, the control fires an event named On Key Up. The message of this event carries the same types of information as the On Key Down event.

When the user presses a key, if you are interested only on the key that was pressed and not on any combination of keys, use the On Key Press event. The message of this event carries only one piece of information, which is the ASCII code of the key.

Control-Based Events

To use a form, the user muse open it, either from the Navigation Pane or from another object you provide them. When the form is being opened, it fires an event named On Open. As the form is opening, it must occupy memory. As this is happening, the form fires an event named On Load.

To make itself known to the operating system and to other applications on the same computer, the form must draw its border. When this is being done, the form fires the On Resize event.

After the form has had the size it needs, the operating system must activate it. If the form is being opened as the first object, it gets positioned in the interface body of Microsoft Access. If the form was already opened and there are other forms (or reports and/or tables), if the user wants to bring it to the front, she must click either its title bar or an area of the form. When this is done, the operating system must paint its title bar with a bright color. Either case, when a form comes to the front of other windows, it fires an event named On Activate.

Once a form has been loaded is currently the active form, the user can use it. After using the form the user can close (the user can either use the system close button or you must provide other means of closing the form). As this starts, the form must lose focus. If the form was the only object opened in Microsoft Access, the body of the application is emptied. If there are other objects, the form would be closed and another object would become active. As this is done, the form fires the On Deactivate event.

When the form is being closed, it must be removed from memory to release the resources it was using (so that those resources can be used by other applications). While this is being done, the form fires the On Unload event.

Once the form has been removed from memory, it (the form) fires an event called On Close.

As you know already, to use a text box or a combo box, the user can click the control and start typing. If the control already contained some text, the user can edit it using the Space, the Backspace, the Delete, and the other letter keys. When the text is being entered or edited, the control fires the On Change event.

A combo box is a control that holds a list of items. To use it, the user can click the arrow of the control to display the list and select an item. Some versions of the combo box allow a user to click the text box part of the control and start typing. The control would then try to find an item that matches what the user is typing or has typed. Sometimes, after the user has finished typing and press Enter or Tab to move focus, Microsoft Access (the database engine) may not find a match and would display an error. This means that the text the user typed did not match any of the items of the combo box. In this case, the control would fire an event named On Not List. You can use this event to friendly display a message to the user and to take an appropriate action.

The web browser has many events appropriate for its functionality:

We already know that you can submit the path of a file or a URL to it. When a file path or a URL is given to a web browser, before it processes it, the control fires an event named On Before Navigate. If there is no problem in this event, the control shows the file or the web page. When the control has finished displaying the document, the web browser fires the On Document Complete event. If there is a change on the document, the control fires an On Progress Change event.

When a web browser has receives a file path or a URL, it makes an attempt to show that file or the web page. If it encounters a problem, it fires an On Navigation Error event.

At any time, and if you allow it, the user can change the document the control is displaying. When a new document must be displayed, the control fires an On Updated event.

Fields and Record-Based Events

Because Microsoft Access is a database application, it provides some event that are particular to records and their fields on a form or report.

To create a new record, the user must move to an empty record on a form. The user can click a control such as a text box and start typing. When this happens, the form fires an event named Before Insert.

If a record exists already, the user can open or access, click one of its fields and start typing or editing. When at least one value in the record has been changed, the form fires the On Dirty event.

After a record has been changed and submitted to the database, the form would fire an event named Before Update.

When a new record has been created, it must be submited to the database. When this is done, the form fires an After Insert event. After an existing record has been modified, the change must be submitted to the database. In this case, the form fires the After Update event.

If a table contains more than one record, after the user has opened its corresponding form, the user can navigate from one record to another. When the user moves from record to record, the form fires an event named On Current.

We know that, to delete a record, a user can click the record and press Delete. This would display a warning message. Before that message comes up, the form fires the Before Del Confirm event. After the user has clicked one of the buttons on the message box, the form fires an After Del Confirm event.

If the user decides to delete a record, before the record is actually deleted, the form fired an On Delete event.

Macros Fundamentals

 

Introduction

A macro is an (automatic) action that must be performed on an object of a database. An example would consist of saving something when a key is pressed on the keyboard. Another example would consist of printing something when an object is clicked. Microsoft Access provides an easy and visual mechanism to create and manage macros.

Macros in Microsoft Access have nothing to do with macros in other Microsoft Office applications. In fact, it should be fair to state that they are completely different. For example, macros in Microsoft Excel and Microsoft Word directly lead to actual Visual Basic programming. Macros in Microsoft Access don't write real (VBA) code: they are actions you create and the database engine executes them behind the scenes at the right time. If you want, you can convert Microsoft Access to VBA but then, they become code and not real macros anymore.

The Macros Windows

To create a macro in Microsoft Access, you can use an intuitive dialog box that allows you to select the action to be performed and the options the action needs. In reality, when you create a macro, Microsoft Access creates a type of script that contains names, expressions, and operations for the action, sparing the details. Still, if you know what is necessary for the macro, you can "manually" create it.

To create a macro, on the Ribbon, click Create. In the Marros & Code section, click the Macro button . Two windows would display and they are separated by a split bar:

To give more room to one of the window, position the mouse between them, click and drag in the desired direction.

The left window presents a tab or a title bar labeled Macro1. By default, that window displays a combo box.

The right window displays buttons with +. This means that they are nodes. To expand a node, click its + button. When you do, the node would display its items.

The Program Flow node allows you to create a condition:

The Actions node holds most of the actions you will create for your macros. If if expand it, you will see that it organizes its actions in categories, each represented by a node:

 To access the actual action you want, expand its node. This would display the actions in that category:

Creating a Macro

To create a macro from the left window, click the arrow of the combo box to display the avilable actions:

If you see the action you want, you can click it. The left window would display the objects (controls) needed for the options of the action you selected. The objects in that window depend on the action you selected.

To create an action using the right window, expand the node(s). Many names of actions are explicit or can be infered logically. Otherwise, you can click an action. The bottom section would show a description of the action:

If you see the action you want, click and drag it to the left window.

In both cases, if you selected an action you don't want anymore, you can click the Delete button .

The Actions of a Macro

To actions its action(s), a macro may need some additional information. This information is referred to as the argument of a macro. The argument can be made of one or more values. The argument(s) depend(s) on the (type of) macro. Some macros take 0 argument while some others take 1, 2 or more arguments.

If you select an action that doesn't take an argument, its name would display in the top section and nothing else:

If you select an action that needs one argument, its name would display followed by a box for the corresponding argument:

If you select an action that needs more than one argument, it would appear, followed by a box for each argument:

An argument is said to be required if it must always be provided, otherwise the action cannot be performed. If you select a macro that takes a required argument, an empty text field would appear and you must type the necessary values:

An argument is referred to as optional if it can be omitted, in which case the macro would use a default value. Normally, when you are creating a macro, its corresponding box(es) would display the default value(s). If you select an action that takes one argument and the argument is optional, its corresponding arguments fields would display the default value:

When an action takes more than one argument, some arguments can be required while some others are optional. The person (Microsoft) who created the macro also decided what arguments would be required and what arguments would be optional. The macro creator also decided about the order of the arguments, which one(s) would appear first and which one(s) would appear last.

If you select a macro that takes more than one argument that are a combination of required and optional arguments, for each argument that is optional, the default value would appear in its placeholder of its corresponding boxes.

Using a Macro

After creating a macro, you can use it. This is usually done by assigning it to an event of a form, a report or a control. For example, you can first create a button that would be used to access a section of a page break. To assign a macro to an object, access the Property Sheet for the object and access the Event or the All tab. You can type the name of the macro in the event's field:

Instead of first creating a macro before assigning it to command button, as another technique, in the Design View of the form, you can right-click the object and click Build Events. In the Choose Builder dialog box, you can click Macro Builder and click OK. The new macro would be automatically assigned to the control.

In Lesson 15, we saw how to create a page break. To implement it in the Action combo box, select GoToPage. In the Page Number box, enter the desired number of the section, and close the macro. You would be asked to save it.

Practical Learning: Using a Macro

1. Start Microsoft Access
2. Open the Bethesda Car Rental1 database from Lesson 15
3. In the Navigation Pane, under Forms, right-click Customers3 and click Design View
4. On the Ribbon, click Create
5. In the Macros & Code section, click Macro
6. In the Macro1 window, click the arrow of the combo box and select GoToPage
7. In the Page Number text box, type 1
    
   
8. Click the Close button to close the macro window
9. When asked whether you want to save, click Yes
10. Set the name to mcrPage1 and click OK
11. To create another macro, in the bottom section of the form, right-click the Personal Information button and click Build Events...
12. In the Choose Builder dialog box, click Macro Builder
     
    
13. Click OK
14. In the Action combo box, select GoToPage
15. In the Page Number, type 2
16. Close the macro window
17. When asked whether you want to save, click Yes
18. On the Ribbon, click Create
19. In the Macros & Code section, click Macro
20. In the right window, click the + button of Actions to expand it
21. Double-click Database Objects to expand it
22. Drag GoToPage from the right to the left window
23. In the Page Number, type 3
24. Close the macro window
25. When asked whether you want to save, click Yes
26. Set the name to mcrPage3 and press Enter
27. In the bottom section of the form, double-click the Contact button
28. On the Property Sheet, click Event
29. Click On Click, then click the arrow of its combo box and select mcrPage1
30. In the bottom section of the form, click Comments
31. In the Event tab of the Property Sheet, click On Click, then click the arrow of its combo box and select mcrPage3
32. Close the form
33. When asked whether you want to save the form, click Yes
34. In the Navigation Page, under Forms, double-click Customers3 to open it in Form View
     
    
35. Click the Personal Information button to test the functionality
     
    
36. Click the Comments button
     
    
37. Close the form
38. Close Microsoft Office Access 2010