ACC: Writing Functions Called from Events or Expressions

ID: Q97514

The information in this article applies to:

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.

NOTE: This article does not address event procedures in Microsoft Access version 2.0, because these are Sub procedures and not functions.

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

NOTE: The result of the calculation updates the field that was passed as a parameter.

Example

Open the Customers form in Design view.

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

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


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

Now, whenever the employees name is typed into the Employee form, it will be converted to the correct format when the user presses TAB or ENTER.

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

NOTE: The result of the calculation is assigned to the function. This way, it can be used in an expression or calculated control.

Example

Open the Customers form in Design view.

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


   Where used                                   Function style
   -----------------------------------------------------------

   AfterUpdate, BeforeUpdate, and so on         Event

   RunCode macro action                         Event

   Calculated controls on forms and reports     Expression

   Calculated fields in a query                 Expression

   SetValue macro action expression             Expression

   Default values in a table or form            Expression

   Called from another function or sub          Expression

Additional query words:

Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0
Platform : WINDOWS
Issue type : kbinfo