Microsoft Access VBA Functions

The links below lead to a few general-purpose VBA (Visual Basic for Applications) functions that I reuse in many of my Microsoft Access database applications. Each link leads to a page where you can view the code for a function, complete with explanatory comments. You can also download the code in plain text format, ready for import into your own applications. All of the functions have been tested in both Microsoft Access 97 and Microsoft Access 2000. I have not tested them in any earlier version of Microsoft Access. Some of the functions require a reference to the DAO object library, which is not included by default in Microsoft Access 2000. This is noted in the comments.

The code is copyright (c) Brendan Reynolds/Timarco Ltd 1999. All other material on this site is copyright (c) Brendan Reynolds, 1999. You may use the code in your own applications, and may distribute it only as part of an application, with all comments, including the copyright notice, intact. You may not publish or distribute the code except as part of an application. You are welcome to publish a link to this site. If your own site is of interest to developers working with Microsoft Access, Microsoft Office, or VBA, and you are interested in swapping links, contact me at [email protected]. Any comments, questions, criticisms, or suggestions for improvements to the functions are also welcome. (I can't guarantee individual replies, but will include the answers to any frequently-asked questions in updates to this site).

The AddItemToCombo Function

This function adds a new item to a Microsoft Access combo-box list, either by opening a form for the user to enter data, or by opening a recordset to add the data directly to a table or query. You can choose whether to prompt the user for confirmation before adding the new item, and can specify the prompt and title for the confirmation dialog box. View the code for the AddItemToCombo function here.

The DefaultClear Function

My Microsoft Access lookup tables often include a Yes/No field that marks one record in the lookup table as the default record. Combo-boxes based on the lookup table are sorted first by this Yes/No field, and forms that include combo-boxes based on these lookup tables use this Yes/No field to set the default value for the combo-boxes. The DefaultClear function ensures that only one record is marked as the default record. View the code for the DefaultClear function here.

The ErrData Function

Use this function in the Error event procedure of a Microsoft Access form, to handle any unanticipated Microsoft Jet database engine errors. You can choose to display an error message, to log error information to a text file, or both. If you choose to display an error message, you can specify the prompt, buttons and title to be displayed, and the function will return the value of the button clicked. If you choose to log the error information, you can specify the path and name of the log file. (If you don't specify a path and file, the file will be saved in the same folder as the Microsoft Access MDB file, and will have the same name, but with an extension of 'log'. The log file is created in CSV (comma-separated value) format, and can be easily imported into a database or spreadsheet for analysis. For VBA run-time errors, see the ErrGeneral function below. View the code for the ErrData function here.

The ErrGeneral Function

This function is very similar to the ErrData function described above, but is designed to handle unexpected VBA run-time errors. See the description of the ErrData function above. One of these days I'll get around to combining these two into one function that can be used for both types of errors � watch this space! View the code for the ErrGeneral function here.

The FocusSetOnCurrent Function

In many of my Microsoft Access forms, I like to set the focus to a different control depending on whether the current record is an existing record or the new record. For the new record, I usually set the focus to the first bound control, so that the user can immediately start entering data, but in an existing record, I usually set the focus to an unbound navigation control. You can use this function to specify the control that should receive the focus if the current record is the new record, the control that should receive the focus if the current record is not the new record, or both. View the code for the FocusSetOnCurrent function here.

The IsAlphanumeric Function

Use this function in the KeyPress event of a form or control to detect whether the key pressed is an alphanumeric key, i.e. 'a' to 'z' (upper or lower case) or '0' to '9'. By default, the function will also return True if the key pressed was the Escape, Tab, Backspace or Enter key. This behaviour can be changed if required using an optional argument. View the code for the IsAlphanumeric function here.

The IsNavigationKey Function

This function returns True if the user presses a key or combination of keys that will navigate to a different record (Control + Home, Control + End, Page Up or Page Down). Remember to set the Key Preview property of any form in which you want to use this function to Yes. View the code for the IsNavigationKey function here.

The ControlCaptionGet Function

This function returns the caption of the label (if any) attached to the specified control. View the code for the ControlCaptionGet function here.

(Since 21 October 1999)