Create a Visual Basic procedure

By extending your database to include Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) procedures (procedure: A sequence of declarations and statements in a module that are executed as a unit. Procedures in a Visual Basic for Applications (VBA) module include both Sub and Function procedures.), you can customize the way the tables, forms, reports, and queries in your database work together. There are several types of procedures. You can create an event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) by adding code to an event on a form or report. You also can create your own Function procedures (Function procedure: A procedure that returns a value and that can be used in an expression. You declare a function with the Function statement and end it with the End Function statement.) or Sub procedures (Sub procedure: A Visual Basic for Applications (VBA) procedure that carries out an operation. Unlike a Function procedure, a Sub procedure does not return a value.) in standard modules (standard module: A Visual Basic for Applications (VBA) module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.), or in class modules (class module: A module that can contain the definition for a new object. When you create a new instance of a class, you create the new object. Procedures defined in the module become the properties and methods of the object.) (which include form (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) and report modules (report module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific report or its controls.)).

ShowCreate a custom function

  1. To open a module (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.), do one of the following:

To open a new standard module (standard module: A Visual Basic for Applications (VBA) module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.), in the Database window, click Modules Button image under Objects, and then click the New button on the Database window toolbar.

To open an existing standard module, click Modules Button image under Objects, select the module you want to open, and then click Design.

To open a form module (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) or report module (report module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific report or its controls.), open the form or report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), and then click Code Button image on the toolbar.

To open a new class module (class module: A module that can contain the definition for a new object. When you create a new instance of a class, you create the new object. Procedures defined in the module become the properties and methods of the object.) that isn't associated with a form or report, in the Database window click Class Module on the Insert menu.

To open an existing class module, in the Database window, click Modules Button image under Objects, select the module you want to open, and then click the Design button on the Database window toolbar.

  1. Declare the function by typing the Function statement.
  2. Type a function name immediately followed by any function arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) in parentheses. For example, the following declaration for the IsLoaded function specifies strFormName as an argument:
Function IsLoaded (strFormName As String) As Boolean
                  
  1. Add the Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) code that performs the operation or calculation that you want the function to perform.

ShowCreate a custom Sub procedure

  1. To open a module (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.), do one of the following:

To open a new standard module (standard module: A Visual Basic for Applications (VBA) module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.), in the Database window, click Modules Button image under Objects, and then click New on the Database window toolbar.

To open an existing standard module, click Modules Button image under Objects, select the module you want to open, and then click Design.

To open a form module (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) or report module (report module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific report or its controls.), open the form or report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), and then click Code Button image on the toolbar.

To open a new class module (class module: A module that can contain the definition for a new object. When you create a new instance of a class, you create the new object. Procedures defined in the module become the properties and methods of the object.) that isn't associated with a form or report, in the Database window click Class Module on the Insert menu.

To open an existing class module, in the Database window, click Modules Button image, select the module you want to open, and then click Design on the Database window toolbar.

  1. Declare the procedure by typing the Sub statement.
  2. Type a procedure name, immediately followed by any arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) in parentheses. For example, the following declaration for the ShowEvent Sub procedure (Sub procedure: A Visual Basic for Applications (VBA) procedure that carries out an operation. Unlike a Function procedure, a Sub procedure does not return a value.) specifies EventName as an argument:
Sub ShowEvent(EventName As String)
                  
  1. Add the Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) code that performs the operation that you want the procedure to perform.

ShowCreate an event procedure

You can set an event property (event property: A named attribute of a control, form, report, data access page, or section you use to respond to an associated event. You can run a procedure or macro when an event occurs by setting the related event property.) for a form, report, or control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) to [Event Procedure] to run code in response to an event. Microsoft Access creates the event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) template for you. You can then add the code you want to run in response to the particular event.

  1. Open a form or report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Display the property sheet for the form or report, or for a section or control on the form or report.
  3. Click the Event tab.
  4. Click the event property for the event that you want to trigger the procedure. For example, to display the event procedure for the Change event, click the OnChange property.
  5. Click Build Button image next to the property box to display the Choose Builder dialog box.
  6. Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) or report module (report module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific report or its controls.). These statements define, or declare, the event procedure.

Microsoft Access automatically declares event procedures for each object and event in a form or report module by using the Private keyword to indicate that the procedure can be accessed only by other procedures in that module.

  1. Add the code to the event procedure that you want to run when the event occurs. For example, to produce a sound through the computer's speaker when data in the CompanyName text box changes, add a Beep statement to the CompanyName_Change event procedure, as follows:
Private Sub CompanyName_Change()

    Beep

End Sub
                  

The event procedure runs each time the Change event occurs for the object.

ShowCreate a class module that is not associated with a form or report

 
 
Applies to:
Access 2003