Home

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 (of a mouse), typing (with the keyboard), pressing a key, scrolling (a scroll bar), etc.

Practical Learning: Introducing Events

  1. Start Microsoft Access
  2. In the list of files, click Chemistry1 from Lesson 11
  3. On the Ribbon, click Create and, in the Forms section, click Form Design
  4. In the Controls section of the Ribbon, click the Button Button and click the form.
    If a wizard starts, click Cancel
  5. Click the Detail bar of the form

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 that something (or 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 not 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 or the All tab. The Event displays only the names of events while the All tab displays the names of events in its bottom part. In both cases, the events displayed are those associated with whatever is selected on the form or report. Here are examples of events:

Events

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, Microsoft Access (or rather the database engine) gathers the necessary information and composes the message to be transmitted where appropriate.

Again, remember that most events have to do with time (they are referred to as 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 and Message Boxes

One way you can use an event is to display a message box for the event. To do this, in the Property Sheet for the object, click the desired event. In its text box, type =MsgBox(). In the parentheses, type the message you want. If the message is normal text, include that text in double-quotes.

Events Categories

Introduction

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 of objects, such as only objects that can receive text. Some other events are very restricted because their object needs particular functionality.

The Click Events

Events

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 object 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.

Practical Learning: Introducing the Click Event

  1. On the form, double-click the button you added
  2. In the Property Sheet, click the Event tab
  3. Click On Click and type:
    =MsgBox("The button was clicked.")
  4. Click an unoccupied area in the Detail section of the form
  5. In the Event tab of the Property Sheet, click On Click and type:
    =MsgBox("Now, something has pressed the body of the form.")
  6. Switch the form to Form View
  7. Click the button
  8. Read the message box and click OK
  9. Click somewhere on the form
  10. Read the message box and click OK

Double-Clicking

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

Focus-Based Events

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:

Events

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:

Events

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.

Practical Learning: Introducing the Focus Events

  1. In the Navigation Pane, right-click the Elements form and click Design View
  2. On the form, click the Symbol text box
  3. In the Event tab of the Property Sheet, click On Got Focus and type:
    =MsgBox("The focus of this application is now on the Symbol text box.")
  4. Still in the Event tab of the Property Sheet, click On Lost Focus and type:
    =MsgBox("Aaaaaahhhhh!!! The Symbol text box has suddenly lost our attention.")
  5. Switch the form to Form View
  6. Click Symbol
  7. Read the message box and click OK
  8. Press Tab
  9. Read the message box and click OK
  10. Right-click an unoccupied area of the form and click Design View
  11. On the form, click the Symbol text box
  12. In the Event tab of the Property Sheet, click On Got Focus and press Delete
  13. Click On Lost Focus and press Delete

Mouse Events

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:

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 he 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.

Keyboard Events

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:

Events

. 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 an integer that specifies whether the user was holding a special key (Ctrl, Alt, or Shift) when the actual key was 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

Form Events

The Events of a Form

To use a form, the user muse open it, either from the Navigation Pane or from another object. 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 acquired 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, he must click either its title bar or an area of the form. When this is done, the operating system must paint the form's 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 and 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.

Practical Learning: Introducing Form Events

  1. Click the button at the intersection of the rulers of the form
  2. In the Event tab of the Property Sheet, click On Load and type:
    =MsgBox("The Elements form is going to be loaded in the computer memory.")
  3. Still in the Event tab of the Property Sheet, click On Unload and type:
    =MsgBox("The Elements form will be closed and stop using the computer memory.")
  4. Still in the Event tab of the Property Sheet, click On Resize and type:
    =MsgBox("The form used for chemistry elenents must be drawn and make its borders clear. The object will be seen on the computer monitor.")
  5. Still in the Event tab of the Property Sheet, click On Activate and type:
    =MsgBox("For our chemistry database, the form for the elements is activated to become the forefront of the database.")
  6. Still in the Event tab of the Property Sheet, click On Deactivate and type:
    =MsgBox("Ohhhhhhhh!!! The Elements object is not active anymore.")
  7. Still in the Event tab of the Property Sheet, click On Got Focus and type:
    =MsgBox("Now, the Elements form has the ultimate attention; it is now the focus of the application.")
  8. Close the Elements form (but leave the other form open)
  9. When asked whether you want to save, click Yes
  10. In the Navigation Pane, double-click the Elements form
  11. Read each message box and click OK
  12. Click the tab of the other form (Form1) to select it
  13. Read each message box and click OK
  14. Click the tab of the Exercises form
  15. Read the message box and click OK
  16. Close the form (Form1)
  17. When asked whether you want to save, click No
  18. Read the message box and click OK
  19. Right-click the body of the form and click Design View
  20. Read each message box and click OK
  21. In the Event tab of the Property Sheet, click On Load and press Delete
  22. Click On Unload and press Delete
  23. Click On Resize and press Delete
  24. Click On Activate and press Delete
  25. Click On Deactivate and press Delete
  26. Click On Got Focus and press Delete
  27. Close the form
  28. When asked whether you want to save, click Yes

Fields and Record-Based Events

Fields-Related Events

Because Microsoft Access is a database application, it provides some events 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.

Records-Related Events

If a record exists already, the user can open or access it, click one of its fields and start typing or editing it. 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 fires 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, she can navigate from one record to another. When the user moves from record to record, the form fires an event named On Current.

Record-Deletion Events

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.

Author Note Macros in Microsoft Access are different from macros in other Microsoft Office applications. For example, macros in Microsoft Excel and Microsoft Word directly lead to actual Visual Basic programming code (VBA). 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 macros to VBA code, but then they become VBA code and not real macros anymore.

Starting a Macro

To create a macro, 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 start a macro, on the Ribbon, click Create. In the Macros & Code section, click the Macro button Macro.

Practical Learning: Introducing Macros

  1. On the Ribbon, click Create
  2. In the Macros & Code section, click Form Design
  3. In the Controls section of the Ribbon, click the Macro button Macro

The Macro Window

When creating a macro, you use a window with a tab or a title bar:

The Macro Window

The Macro window provides the primary means of creating a macro. Like every window, it presents a tab or a title bar labeled Macro1. The Macro window starts with a default combo box.

Creating a Macro

To create a macro from the Macro window, click the arrow of its combo box to display the available actions:

The Macro Combo Box

If you see the action you want, click it. If you selected an action you don't want anymore, to cancel or remove it, click the Delete button Delete.

Practical Learning: Creating a Macro

  • Click the arrow of the combo box and click CloseWindow

The Actions of a Macro

If you select an action, the Macro 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.

If you selected an action you don't want anymore, to cancel or remove it, click the Delete button Delete.

To apply 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:

Macro Definition - A Simple Action

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

Macro With One Argument

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

Macro With Many Arguments

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, a text field would appear and display a gray Required text. You must type the necessary value:

Macro With Required Argument

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).

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. Here is an example:

Macro With Optional Arguments

Practical Learning: Creating a Macro

  1. Click the arrow of the combo box and click CloseWindow
  2. Click the arrow of the Object Type combo box and select Form
  3. Click the arrow of the Object Name combo box and select Elements

    Creating a Macro

  4. To save and close the macro, click the Close button Close on the far-right side of the Macro1 tab
  5. When asked whether you want to save, click Yes
  6. Type the name as mcrCloseElementsForm and click OK

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. You have various options. To assign a macro to an object:

  • Access the Property Sheet for the object and access the Event or the All tab. Click the arrow of the desired event and select the name of the macro:

    Assigning a Macro

  • Add a control that uses a wizard, such as a combo box or a button. In the wizard, select the name of the macro

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

Practical Learning: Assigning a Macro

  1. In the Navigation Pane, right-click the Elements form and click Design View
  2. Right-click the body of the form and click Form Header/Footer
  3. In the Controls section of the Ribbon, click the Button Button
  4. Click sometwhere below the Form Footer bar.
    If a wizard starts, click Cancel
  5. In the Property Sheet, click the All tab
  6. Click On Click then click the arrow of the combo box and select mcrCloseElementsForm
  7. Still in the Property Sheet, change the following characteristics:
    Name: cmdClose
    Caption: Close

    Chemistry - Elements Form Design

  8. Right-click the tab of the form and click Form View
  9. To close the form, click the bottom Close button
  10. When asked whether you want to save, click Yes

The Action Catalog Window

The combo box of the Macro window presents an alphabetic list of actions. It is helpful if you can easily locate and select the action you want to apply. To help you identify an action, Microsoft Access provides the Action Catalog window. It is used in combination with, or as an addition to, the Macro window:

Action Catalog

The Action Catalog window presents the actions organized in categories in a tree-like list where each branch is a node. To expand a branch or node, click its triangle button. When you do, the node would display its items.

The Program Flow node allows you to create a condition. The Actions node holds the same list of actions as the combo box of the Macro window but only the actions that are not condition-based. To access the actual action you want, expand its node. This would display the actions in that category. Here are examples in the Data Entry operations and the Data Import/Export groups:

Action Catalog

In the Action Catalog window, many names of actions are explicit or can be infered logically. Here is an example:

Macro Description

Otherwise, you can click an action. The bottom section would show a description of the action:

Macro Description

After locating an action, to use it, click and drag it to the Macro window.

If you selected an action you don't want anymore, to cancel or remove it, click the Delete button Delete.

Practical Learning: Ending the Lesson

  • Close Microsoft Access
 
 
   
 

Previous Copyright © 2000-2016, FunctionX, Inc. Next