Article ID: 97514 - Last Review: May 9, 2003 - Revision: 2.0

ACC: Writing Functions Called from Events or Expressions

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q97514
Moderate: Requires basic macro, coding, and interoperability skills.

On This Page

Expand all | Collapse all

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.
Back to the top

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.
Back to the top

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.
Back to the top

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])
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.
Back to the top

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.
Back to the top

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.
Back to the top

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
Back to the top
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
Back to the top
Keywords: 
kbinfo kbprogramming KB97514
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations