Access VBA Code

=Event-Handlers=

What is an event?
Events are, well events that are associated with the objects in your development framework. In this case that is Access. In Access only the user interface elements (forms and report) have events. But the objects from additional libraries you can add as references may have events as well. An event occurs when a button is clicked, when a form is loaded or a textbox is updated with a new value.

There are hundreds of predefined events available in Access. And these event occurs nonstop during an access application is used by a user.

While these events occur all the time, they do not have any effect on your application unless you handle them. You can use either a predefined macro or you can write VBA code to handle an event. The procedures that are tied to events in VBA are called event procedures. The macro or code you bound to an event is executed each time the event occurs.

A VBA code event handler
A form with a button named btnMyButton when clicked will execute the VBA code, provided the OnClick property is set to [Event Procedure] beforehand, and the elipsses button was pressed to create the event scaffold in VBE.

This empty event scaffold can contain an operation, command, function, subroutine, etc... Access will try and interpret and execute any code inside the event handler scaffold.

=Formulas= Unlike Excel where any cell can contain a formula that references a value in any other cell, Access allows formulas that reference other cells of a table only the same record(row). The reference is constructed from the Field(Column) name. For Example: The formula for the field 'Calculated Area' would be: The cells can also include calculations based on Access functions. When editing a table, simply select Calculated as the data type and under the general tab enter your formula or function reference in the Expression Field Property.

Criteria when writing formulas

 * It is not necessary to precede the expression with an equal sign (=).
 * The calculation cannot include fields from other tables or queries.
 * The results of the calculation are read-only.
 * Calculated fields are only available in Access databases using the .accdb file format.
 * You must be using Access 2010 or higher.

=Functions=


 * Access Built-in Functions
 * Access User Defined Functions

Visual Basic for Applications has functions either built-in or user-defined (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). It supersedes and expands on the abilities of earlier application-specific macro programming languages such as Word's WordBASIC. It can be used to control many aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes.

Function vs. Sub
The difference between a function and a sub in VBA is that a function can return a value while a sub (subroutine) cannot. Functions and subs become very useful as program size increases. If you want VBA to perform a task that returns a result, you can use a function.

Public vs. Private Function
The terms Public and Private are used in relation to Modules. The basic concept is that Public functions can be seen and used by all modules, forms, or reports in the database while Private functions can only be used by code within the same module, form, or report.

Private Function
Function to calculate Area

Public Function
Function to calculate Area

Calling a Function from a Sub
=Methods=

=Object Library Reference = If you use the objects in other applications as part of your Visual Basic application, you may want to establish a reference to the object libraries of those applications. Before you can do that, you must first be sure that the application provides an object library.

To see if an application provides an object library

 * 1) From the Tools menu, choose References to display the References dialog box.
 * 2) The References dialog box shows all object libraries registered with the operating system. Scroll through the list for the application whose object library you want to reference. If the application isn't listed, you can use the Browse button to search for object libraries (*.olb and *.tlb) or executable files (*.exe and *.dll on Windows). References whose check boxes are selected are used by your project; those that aren't selected are not used, but can be added.

To add an object library reference to your project
•Select the object library reference in the Available References box in the References dialog box and choose OK. Your Visual Basic project now has a reference to the application's object library. If you open the Object Browser (press F2) and select the application's library, it displays the objects provided by the selected object library, as well as each object's methods and properties.
 * In the Object Browser, you can select a class in the Classes box and select a method or property in the Members box. Use copy and paste to add the syntax to your code.

=Procedures=

=References=