Microsoft KB Archive/97514

= ACC: Writing Functions Called from Events or Expressions =

Article ID: 97514

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q97514



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



SUMMARY
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: <pre class="fixed_text">     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 &quot;Language Reference,&quot; such as UCase, LCase, and so on. </li></ol>

Determining the Type of Function You Need
<pre class="fixed_text">  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

Keywords: kbinfo kbprogramming KB97514

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.