ACC: Writing Functions Called from Events or Expressions

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

Functions can be used in a variety of places in Microsoft Access. How you write your functions depends on where the functions are going to be called from.

This article assumes that you are familiar with Access Basic and with creating applications for Microsoft Access using the programming tools provided with Microsoft Access.

MORE INFORMATION

There are two main styles for writing Access Basic functions:

• Functions can be called from event properties, such as the AfterUpdate property for a control on a form. Typically, you would call the function with a parameter. The function then acts on or modifies the parameter.
• Functions can be used in expressions, such as calculated controls. The difference is in how the result is returned when the function exits.

The examples below use the Proper() function to illustrate the differences between the two function styles. Proper() converts the first letter of a word to uppercase and the other letters to lowercase.

Calling a Function from an Event Property

The Proper() function can be written so it can be called from an event, such as the AfterUpdate property of a control on a form. In this example we will call it ProperAU() as a reminder that it should be called from the AfterUpdate property.

Enter the following function in a module:

   Function ProperAU(Field As Control)
      Field=UCase(Left(Field,1)) & LCase(Mid(Field,2))
   End Function

Example

1. Open the Customers form in Design view.

2. View the Property sheet by choosing Properties from the View menu.

3. Add the following statement to the AfterUpdate property of the First Name field:

         Object: Text Box
         ----------------
         ControlName: First Name
         AfterUpdate: =ProperAU([First Name])
   

Calling a Function from an Expression

The Proper() function can be written so it can be called from an expression, or calculated control. In this example we will call it ProperCC() as a reminder that it should be used in calculations.

Enter the following function in a module:

   Function ProperCC(Field)
      ProperCC=UCase(Left(Field,1)) & LCase(Mid(Field,2))
   End Function

Example

1. Open the Customers form in Design view.

2. Add the following calculated control to the form:

         Object: Text Box
         ----------------
         ControlName: Proper Last Name
         ControlSource: =ProperCC([Last Name])
   

   Now when you type in the Last Name field, you will see the correct capitalization in the Proper Last Name field.

   NOTE: You will not be able to type in the Proper Last Name field. ProperCC() does not change underlying data like ProperAU() does. For this reason, ProperCC() is useful in reports and expressions and can be used more places than ProperAU().

   You can use ProperCC in the same manner as any of the built-in functions listed in the "Language Reference," such as UCase, LCase, and so on.

Determining the Type of Function You Need