The Application Object Methods and Properties - MS Access

In this chapter, there are separate sections to deal with the more complex application objects. To begin though, we look at a variety of the simpler Application object’s methods and properties.

The Run Method

Using Application.Run, you can execute a custom Microsoft VBA procedure. The following example demonstrates how to dynamically execute a procedure and pass parameters to the procedure:

In the next example, we have copied the subroutine into another Access database. The code then opens the second Access database and runs the Public code:

The RunCommand Method

The Application.RunCommand mimics the actions that a user can perform by using the built-in Access menus. To invoke a specific menu action, you need to have a suitable context; for example, to display a find dialog, you need to have a form that contains controls that can be searched, and to perform an Undo record, the record needs to have been changed.

The sample form frmRunCommand includes some button examples, which are illustrated in the following code:

Unfortunately, the built-in Help system does not provide a good description of how and when to use the different commands. But you can pick up the use of the different commands by examining code snippets in this book and by searching online. If you search online for a specific command, such as acCmdSaveRecord, you will find sites that are dedicated to explaining the different commands.

Simplifying Filtering by Using BuildCriteria

The BuildCriteria method of the Application object is a tool that is designed to assist you in constructing valid Where clause syntax. The method call involves passing the FieldName, FieldType, and Criteria. This method can be combined with InputBox commands or dialog forms to build Where clauses for code and filters.

The frmEmployeesList form contains a button that uses an input box to apply a filter. If you enter an ”*“ (asterisk) character at the end of the name, the BuildCriteria method constructs an appropriate Where clause by using the like keyword, as demonstrated in the following:

The ColumnHistory and Append Only Memo Fields

In Table design, if you scroll to the very last property of a memo field, you will find the Append Only property, as shown in Figure.

The Append Only property of a memo field in Table design, for the field Notes in the table Customers.

The Append Only property of a memo field in Table design, for the field Notes in the table Customers.

With Append Only set to Yes, each time the field is edited, the previous contents are recorded in the column history. To display the column history information, right-click the memo field in Datasheet or Form view, and then choose Show Column History from the shortcut menu.

Displaying the column history for an Append Only memo Field.

Displaying the column history for an Append Only memo Field.

There is no feature to trim down or edit the column history information for a table. To clear the history you need to create a new field, update the existing data, remove the old field, and then rename the new field.

To display the column history by using an unbound control on a form or in a query, you can use a custom VBA function, as shown in the following:

This function can be enhanced to handle different types of primary key values and provide more sophisticated formatting by parsing the string for the appropriate version information tags, as shown in Figure.

Using a custom VBA function to display the column history information.

Using a custom VBA function to display the column history information.

Examining TempVars

Unlike VBA, when programming by using macros, you cannot define variables to hold values of controls and other settings. There are many circumstances in macro programming in which you need to save a temporary value for later re-use. You can use the TempVars collection to create these temporary variables. This collection also allows you to share values between macro and VBA code.

Using the following VBA code, you can quickly dump a list of the temporary variables to the Immediate window:

In VBA and macro programming, when you attempt to use a TempVar that has not already been used, it is automatically added to the TempVars collection. The collection supports methods to Add, Remove, and RemoveAll items (corresponding to the SetTempVar, RemoveTempVar, and RemoveAllTempVars macro commands).

Invoking the Expression Builder

The Expression Builder is an extremely useful tool to have when writing code that needs to refer to forms and reports. In the VBA Code Editor, there is no button to display the Expression Builder; it can only be displayed when you are in the Design mode of an Access object, such as a form, or in the Query grid.

This means developers often find themselves needing to go into a query or form in Design mode to display the Expression Builder.

It is possible to mimic this action by specifying a function key that will display the Expression Builder with a single keystroke, as demonstrated here:

Function modApp_InvokeBuilder() ' Create an Autokeys Macro using F6 to activate this code DoCmd.OpenForm "frmLaunchExpressionBuilder", acDesign ' This command launches the expression builder RunCommand acCmdInvokeBuilder End Function

This function opens a form called frmLaunchExpressionBuilder in Design mode and then sends the acCmdInvokeBuilder command. This command can only be executed when you have an appropriate object open in Design view.

A dummy form with no controls can be used to launch Expression Builder.

A dummy form with no controls can be used to launch Expression Builder.

To prevent a pop-up from appearing that prompts you to choose between displaying the Expression Builder, Macro Builder, or writing code, we have added an expression to the form's default load event. We next create an AutoKeys macro that ties the F6 key to executing the VBA code.

Using a Submacro name that corresponds to a keycode and saving the macro with the special name AutoKeys redefines how function keys and other keys operate.

Using a Submacro name that corresponds to a keycode and saving the macro with the special name AutoKeys redefines how function keys and other keys operate.

Now, when you display the Access interface you can launch Expression Builder by pressing the F6 key, as shown in Figure.

A quick technique for launching Expression Builder.

A quick technique for launching Expression Builder.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics