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=

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.
 * Access Built-in Functions
 * Access User Defined Functions
 * Access Windows API Functions

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.

Types of References

 * - Type Libraries (*.olb, *.tlb, *.dll)
 * - Executable Files (*.exe, *.dll)
 * - ActiveX Controls (*.ocx)
 * - All Files (*.*)

Using or Losing a Reference
After you set a reference to an object library by selecting the check box next to its name, you can find a specific object and its methods and properties in the Object Browser. You can't remove a reference for an item that is used in your project. If you remove a reference to an object that you are currently using in your project, you will receive an error the next time you refer to that object. References not in use are listed alphabetically. When you refer to an object in code, Visual Basic searches each referenced library selected in the References dialog box in the order that the libraries are displayed. If two referenced libraries contain objects with the same name, Visual Basic uses the definition provided by the library listed higher in the Available References box.

=Procedures=

=Windows API = Creates a reference to a procedure or function in an external DLL. The reason for calling an API function is to perform something that would otherwise be impossible.

This a process, used to reference a procedure or function in a DLL outside of the Office installation. This allows the use of procedures or functions not normally available to the Access Application. These functions are used by Windows programs to interact with Windows itself.

=References=