Microsoft Access: Index to VBA Functions

Provided by Allen Browne, November 2006, Updated April 2010.


VBA Function List

This index lists lists the VBA code (Functions and Subs) shown in the various pages of this website.

Use the your browser's Find (Edit menu) to locate what you are looking for.

Where noted, you will need to download the sample database referred to in the page, in order to get the function.

A C D E F G H I K L M N O P Q R S T U
FunctionDescriptionWeb page
AdjustAutoNum() Set the Seed of an AutoNumber, using DDL. DDL Code Examples
AdjustDateForYear() Adjust the text box value for change of year. Intelligent handling of dates at the start of a calendar year
AdoRecordsetExample() Open a recordset using ADO. ADO Programming Code Examples
Age() Calculate age based on date of birth, as of any date. Age() function
Append2Table() Use the NotInList event of a combo to add the new value to its RowSource table. NotInList - Adding values to lookup tables
AssignReportPrinter() Set a custom property for a report, so it remembers to use a particular printer. Access 2002 and later. Printer Selection Utility
AuditDelBegin() Audit trail for deletion of records in a form (part 1.) VBA code for auditing
AuditDelEnd() Audit deletion of records in a form (part 2.) VBA code for auditing
AuditEditBegin() Audit trail for edits in a form (part 1.) VBA code for auditing
AuditEditEnd() Audit trail for edits in a form (part 2.) VBA code for auditing
AutoNumFix() Resets the Seed of an AutoNumber when Access sets it incorrectly. Fixing AutoNumbers (negatives or duplicates)
Caption4Control() Identify which name (label) the user sees for a control. Code for Find As You Type utility
CarryOver() Assign the same values as the previous record as defaults when you start a new one. Carry data over to new record
CategoryID_NotInList() Add items to a combo's lookup table with its NotInList event. Adding values to lookup tables
CboMoveTo_AfterUpdate() Illustrates how to use a combo to navigate to a record. Using a Combo Box to Find Records
cboShowSup_AfterUpdate() Illustrates how to filter a form based on a value in the subform. Filter a Form on a Field in a Subform
ClearList() Deselect all items in a list box (whether multi-select or not.) See also SelectAll(). Select/deselect all items in a multi-select list box
Clipboard2Text() Paste text from the clipboard. See also Text2Clipboard(). Clipboard Functions (32-bit)
CloseAllClients() Illustrates how to close all instances of a form. See also OpenAClient() Managing Multiple Instances of a Form
cmdCreateBatch_Click() Assign a print run number to unprinted records Code for 'Has the record been printed?'
cmdDupe_Click() How to duplicate the main form record and the related records in a subform. Duplicate the record in form and subform
cmdFilter_Click() How to build a criteria string form the non-blank search boxes. VBA code for Search Criteria article
cmdPreview_Click() Illustrates how to use a multi-select list box to filter a report. Use a multi-select list box to filter a report
cmdPrint_Click() How to open a report to print just the record in the form. Print the record in the form
cmdPrintBatch_Click() Print a batch of records (using WhereCondition of OpenReport.) Code for 'Has the record been printed?'
cmdReset_Click() How to clear all the unbound controls in the header of a search form. VBA code for Search Criteria article
cmdSql2Vba_Click() Convert a SQL statement into a string to paste into VBA code. Copy SQL statement from query to VBA
cmdUndoBatch_Click() Undo the assignment of a print run number to records. Undo cmdCreateBatch_Click() Code for 'Has the record been printed?'
ComputerName() Return the name of this workstation. Code to log usage of forms and reports
ConcatRelated() Generate a concatenated string of related records. Concatenate values from related records
ControlTypeName() Return the name of the ControlType. Where is a field used?
ConvertLabelOnTabPage() Solve bug on Access 2003 forms where unattached labels cause the screen to flicker. Flicker with tab controls
ConvertMixedCase() Convert mixed case name into a name with spaces (for captions) DAO Programming Code
CountLines() Count the number of lines of code in an Access database. Count lines (VBA code)
CreateDatabaseAdox() Create a database file, using ADOX. ADOX Programming Code Examples
CreateDatabaseDAO() Create a new database programmatically, and set its key properties. DAO Programming Code
CreateFieldDDL() Illustrates how to add a field to a table, using DDL. DDL Code Examples
CreateFieldDDL2() Add a field to a table in another database, using DDL. DDL Code Examples
CreateIndexesAdox() Show how to create indexes, using ADOX. ADOX Programming Code Examples
CreateIndexesDAO() Create primary key, foreign key, and unique indexes; single- and multi-field Create primary key, foreign key, and unique indexes; single- and multi-field DAO Programming Code
CreateKeyAdox() Show how to create relationships, using ADOX. ADOX Programming Code Examples
CreateProcedureAdox() Create a parameter query or action query, using ADOX. ADOX Programming Code Examples
CreateQueryDAO() Create a query programmatically. DAO Programming Code
CreateRelationDAO() Create relations between tables. DAO Programming Code
CreateTableAdox() Create a table with various field types, using ADOX. ADOX Programming Code Examples
CreateTableDAO() Create two tables using DAO, illustrating the field types. DAO Programming Code
CreateTableDDL() Create two tables, their indexes and relation, using DDL. DDL Code Examples
CreateViewAdo() Create a new query using ADO. ADO Programming Code Examples
CreateViewAdox() Create a query, using ADOX. ADOX Programming Code Examples
CreateViewDDL() Create a new query, using DDL. DDL Code Examples
CurView() Return the CurrentView property of the form/report. Code to log usage of forms and reports
CustomerID_DblClick Open a form to add records to a combo's lookup table, and refresh the combo. Adding values to lookup tables
DAORecordsetExample() How to open a recordset and loop through the records. DAO Programming Code
DefaultZLS() Create a field that defaults to a zero-length string, using DDL. DDL Code Examples
DeleteAllAndResetAutoNum() Delete all records from the table, and reset the AutoNumber, using ADOX. ADOX Programming Code Examples
DeleteAllRelationships() Delete all relationships in a database. (For repair purposes.) Delete All Relationships
DeleteIndexAdox() Show how to delete indexes, using ADOX. ADOX Programming Code Examples
DeleteIndexDAO() Delete indexes using DAO DAO Programming Code
DeleteKeyAdox() Delete relationships, using ADOX. ADOX Programming Code Examples
DeleteProcedureAdox() Drop a parameter/action query, using ADOX. ADOX Programming Code Examples
DeleteQueryDAO() Drop a query DAO Programming Code
DeleteRelationDAO() Delete relations between tables. DAO Programming Code
DeleteTableAdox() Delete a table, using ADOX. ADOX Programming Code Examples
DeleteTableDAO() Drop a table DAO Programming Code
DirListBox() Call-back function for RowSourceType of list box, to load a list of files in a folder. DirListBox() function
DoArchive() Illustrates how to move old records to another table, using a transaction. Archive: Move Records to Another Table
DocType() Return the acObjectType for an object Code to log usage of forms and reports
DoMouseWheel() Scroll records with the mouse wheel in Access 2007 Scroll records with the mouse wheel in Access 2007
DropFieldDDL() Delete a field from a table, using DDL. DDL Code Examples
EAvg() Code to average a field in a table/query in Microsoft Access, with the option to count the TOP values. A replacement for DAvg(). Extended DAvg()
ECount() Extended replacement for DCount(), with the option to count distinct values. ECount()
ELookup() Extended replacement for DLookup(), with the option to specify which record of multiples to return. Extended DLookup()
EnumReports() Show all reports in a combo/list box. (Call-back function for RowSourceType.) List Box of Available Reports
EscChar() Replace a literal escape character with an escape sequence, unless followed by 2 hex digits, e.g. File#9.txt becomes File%239.txt in HTML. Hyperlinks: warnings, special characters, errors
EscHex() Replace special characters in a string with an escape sequence, e.g. %20 instead of space in HTML. Hyperlinks: warnings, special characters, errors
ExamineDatabase() Check database for issues. (Download sample database.) Utility to check the structure of a database
ExecuteInTransaction() Execute the SQL statement on the current database in a transaction. DAO Programming Code
ExecuteProcedureAdox() Execute a parameter/action query, using ADOX. ADOX Programming Code Examples
fGetProductVersion() Get the version number of an executable. Code for Splash screen with version information
FieldTypeName() Converts the numeric results of DAO Field.Type to text. TableInfo() function
FileExists() Determine whether a file exists Determine if a file or folder exists
FindAsUTypeLoad() Filter records in a form between keystrokes Code for Find As You Type utility
FindField() Search the current database for where a field name is used. Where is a field used?
FindInFormReport() Search for controls where the Name, Control Source, or Caption matches the string. Where is a field used?
FindInGroupLevel() Search the Control Source of each Group Level of a report. Where is a field used?
FindInTableQuery() Find fields where the Name, SourceField, or Caption matches the string. Where is a field used?
FindMacrosInFormReports() Identify the events in forms and reports that use macros. Where are macros used in forms or reports?
FirstVisibleColumn() Return the column number of the first visible column in a combo. Code for Find As You Type utility
FixBadAOIndex() Solve error, 'AOIndex is not an index in this table' Recovering from corruption
FixupFilename() Check path, file name, extension. (Part of Output HTML utility) Code for Output HTML article
FixZLS() Turn off the Allow Zero Length property of all fields in a database. Problem properties
FolderExists() Determine whether a folder exists Determine if a file or folder exists
ForceClosed() Close a form or report, even if it means losing the edit in progress. Code for Splash screen with version information
Form_BeforeInsert() How to cancel a new record in a subform if there is no record in the main form. Why does my form go completely blank?
Form_Open() How to cancel the opening of a form it it has no records. Why does my form go completely blank?
Form_Unload() Illustrates how to return to the same record next time the form is opened. Return to same record next time form is opened
FormatCell() Format an HTML table cell with the data in a field. (Part of Output HTML utility) Code for Output HTML article
FormHasData() Return True if the form has any records (other than new record.) Avoid #Error in form/report with no records
fReturnSysDir() Get the system folder name (typically C:\Windows\System32) Code for Splash screen with version information
fReturnTempDir() Get the temp folder name Code for Splash screen with version information
fReturnWinDir() Get the operating system folder (typically C:\Windows) Code for Splash screen with version information
GetAccessVersion() Show the version of msaccess.exe (to determine service packs) Code for Splash screen with version information
GetAutoNumDAO() Get the name of the AutoNumber field, using DAO. DAO Programming Code
GetDataPath() Determine the path to an attached table. Code for Splash screen with version information
GetFileFormat() Show whether the database is an MDB/MDE/ADP/ADP/ACCDB/ACCDE, and what version. Can check other files also. Code for Splash screen with version information
GetFilterField() Determine the field name to use when filtering on this control. Code for Find As You Type utility
GetIncome() Example of how to return multiple values using a custom data type. Returning more than one value from a function
GetJetVersion() Determine the version of the JET engine in use (to determine service packs.) Code for Splash screen with version information
GetMachineName() Determine the Windows workstation name on the network. Code for Splash screen with version information
GetNetworkUserName() Determine the Windows user name of the logged in user. Code for Splash screen with version information
GetPrinter4Report() Retrieve the custom property, indicating which printer to use for a report. Printer Selection Utility
GetSeedADOX() Read the Seed of the AutoNumber of a table, using ADOX. ADOX Programming Code Examples
GoHyperlink() Replacement for FollowHyperlink(). Handles security warnings, special characters in file name, return value. Hyperlinks: warnings, special characters, errors
HasBadChar() Test if an object name contains a poor character. (Download sample database.) Utility to check the structure of a database
HasDatasheetSchema() Test if user can add fields to datasheets in Access2007. (Download sample database.) Utility to check the structure of a database
HasNameAutoCorrect() Test if Name AutoCorrect is turned on (Download sample database.) Utility to check the structure of a database
HasNoPK() Test if a table has no primary key. (Download sample database.) Utility to check the structure of a database
HasProperty() Determine whether an object has the property.. Printer Selection Utility
HasSection() Return True if the form or report has a section number. Where are macros used in forms or reports?
HasSubdatasheet() Test if SubdatasheetName needs setting for tables. (Download sample database.) Utility to check the structure of a database
HasUnboundControls() Determine whether a form has all the unbound controls in a list. Code for Find As You Type utility
HIWord() Get the high order word from a 32-bit value. See also LOWord() Code for Splash screen with version information
IgnoreField() Return True for OLE fields, binary fields. (Part of Output HTML utility) Code for Output HTML article
IndexOnField() Return True if a single-field index exists on this field. DAO Programming Code
InitSplash() Display a splash screen for 2 seconds when database opens. Code for Splash screen with version information
InsertAtCursor() Insert the characters at the cursor in the active control. Insert characters at the cursor
IsAllowZeroLength() Test if a field has Allow Zero Length set (Download sample database.) Utility to check the structure of a database
IsBadWord() Test if an object name is a reserved word or problem name. (Download sample database.) Utility to check the structure of a database
IsCalcTableField() Returns True if fld is a calculated table field (Access 2010 and later only.) Carry data over to new record
IsCardValid() Determine if a number is a valid Credit Card number (Part of downloadable application. Check sum, digit count, correct prefix) Credit Card validation
IsComplexData() Test if a field is a complex data type in Access 2007. (Download sample database.) Utility to check the structure of a database
IsDecimalField() Test if a field is a Decimarl data type. (Download sample database.) Utility to check the structure of a database
IsNullableFK() Test if Nulls are permitted in a foreign key field. (Download sample database.) Utility to check the structure of a database
IsRichText() Return True if a memo is a Rich Text field (Access 2007 and later.) Code for Output HTML article
IsTooWide() Test if a record would be too wide if all fields contained data. (Download sample database.) Utility to check the structure of a database
Keep1Open() Open the Switchboard if nothing else is visible. Keep something open
LimitKeyPress() Limit the length of text permitted in an unbound text box/combo. Unbound text box: limiting entry length
ListFiles() List the files in a folder, and subfolders recursively. List files
ListFilesToTable() List the files in a folder and subfolders, and write the results to a table. List files to a table
LockBoundControls() Set the Locked property of all controls on a form, and recursively on subforms. Locking bound controls on a form
LockControls() Simulate field-level permissions (Download sample database.) Field-level Permissions in Microsoft Access
LogDocClose() Update the log entry created when the form/report was opened, to mark it closed. Code to log usage of forms and reports
LogDocOpen() Create a log entry for the form/report being opened. Code to log usage of forms and reports
LogError() Generic VBA error handler that logs the errors to a table. Error Handling in VBA
LOWord() Get the low order word from a 32-bit value. See also HIWord() Code for Splash screen with version information
MakeData() How to create a table containing consecutive numbers in a field. Printing a Quantity of a Label
MakeGuidTable() Create a table with a GUID field. DAO Programming Code
MakePercent() Treat an entry as a percent, e.g. 10 is 10%. (Access 2007 does this automatically.) Enter value as a percent
MakeRel() Illustrates how to create a cascade-to-null relationship (Access 2000 and later.) Cascade to Null Relations
MaxOfList() Find the highest number in a list of values or fields. MaxOfList() and MinOfList() functions
MaxParentNumber() Return the PageIndex of the tab page that the control is on. Code for Find As You Type utility
MinOfList() Find the lowest number in a list of values or fields. MaxOfList() and MinOfList() functions
ModifyFieldDDL() Change the type or size of a field, using DDL. DDL Code Examples
ModifyFieldPropAdox() Show how to alter field properties, using ADOX. ADOX Programming Code Examples
ModifyTableDAO() Add and delete fields to existing tables. DAO Programming Code
ModifyViewAdo() Modify a query using ADO. ADO Programming Code Examples
NetworkUserName() Displays the name the user is logged into Windows with. VBA code for auditing
NoData() Code for the NoData event of reports, that allows the report to remain lightweight. Default forms and reports
NoRecordNum() Illustrates how to traverse a recordset with bookmarks instead of record numbers. What, no record numbers?
OnHand() Calculate the quantity-on-hand for a product (inventory.) Inventory Control: Quantity on Hand
OpenAClient() Illustrates how to open multiple instances of a form. See also CloseAllClients(). Managing Multiple Instances of a Form
OpenTheReport() Wrapper for OpenReport. Avoids 2501 error. Returns True if opened. Printer Selection Utility
OutputHTML() Create an HTML file from an Access table/query. Code for Output HTML article
ParentIsTabPage() Determine whether a control is in the page of a tab control or not. Flicker with tab controls
ParseWord() Return a word (first, last, n-th) from a phrase, e.g. the last word from "John P Doe" Parse a word from a field or phrase
PlaySound() Play a sound (e.g. wav file) in Access events. PlaySound() function
PrepareAllFor2007() Set the properties of a whole folder full of databases so Access 2007 users do not make schema changes. Prevent Access 2007 users modifying existing databases
PrepareDbFor2007() Set the properties of existing databases so Access 2007 users do not make schema changes. Prevent Access 2007 users modifying existing databases
Quantity_AfterUpdate() How to calculate a value based on other values in a form. Calculated Fields
Reconnect() Reconnect attached tables at startup, assuming front end and back end are both in the same folder. Reconnect Attached tables on Start-up
ReloadSuburb() Illustrates how to delay-load a combo (list loads after 3 keystrokes.) Combos with Tens of Thousands of Records
RelReport() Print the Relationships report with information about field types and sizes, indexes, etc. VBA code to accompany AppRelReport.html
Replace() Access 97 version of the Replace() function in Access 2000 and later. Code for Find As You Type utility
ResetSeed() Reset the Seed of the AutoNumber, using ADOX. ADOX Programming Code Examples
RoundTime() Round a date/time value to nearest minute, 15 minutes, hour, … Rounding numbers in Access
SelectAll() Select all items in a multi-select list box. See also ClearList(). Select/deselect all items in a multi-select list box
SetAutoNumber() How to set an AutoNumber to start at a higher value. Set AutoNumbers to start from ...
SetFieldDescription() Assign a description to a field, based on the data type, size, and properties. DAO Programming Code
SetMarginsAndOrientation() Set the margins and orientation (landscape/portrait) for a report. Access 2002 and later. VBA code to accompany AppRelReport.html
SetPropertyDAO() Set a property for an object, creating it if necessary. Printer Selection Utility
SetSeed() Set the Seed of an AutoNumber, using ADOX. ADOX Programming Code Examples
SetupPrinter4Report() Set the application printer for a report. Access 2002 and later. Printer Selection Utility
ShowAllTables() List the tables (and optionally their fields), using ADOX. ADOX Programming Code Examples
ShowBand() Illustrate the binary operators BAND, BOR, BXOR, etc. in JET SQL. (ADO only.) ADO Programming Code Examples
ShowDatabaseProps() List the properties of the current database. DAO Programming Code
ShowFields() How to read the fields of a table. DAO Programming Code
ShowFieldsRS() How to read the fields of a recordset. DAO Programming Code
ShowFormProperties() Loop through the controls on a form, showing names and properties. DAO Programming Code
ShowHideControl() Show or hide a control on a form. Code for Find As You Type utility
ShowKeyAdox() List relationships, using ADOX. ADOX Programming Code Examples
ShowProps() List the properties of an object. Prevent Access 2007 users modifying existing databases
ShowPropsADOX() Show the columns in a table, and optionally their properties, using ADOX. ADOX Programming Code Examples
ShowProx() List the parameter/action queries, using ADOX. ADOX Programming Code Examples
ShowSchema() List the tables, using ADO. ADO Programming Code Examples
ShowUserRosterMultipleUsers() List the users currently connected to the database. ADO Programming Code Examples
Soundex() Fuzzy matches: find names that sound alike. Soundex - Fuzzy matches
Split() Access 97 version of the Split() function in Access 2000 and later. Code for Find As You Type utility
SQLDate() Format dates the way Access expects when you concatenate them into a string. VBA Traps: Working with Variables
StampRecord() Track who entered and last changed a record, and when. (Download sample database.) Field-level Permissions in Microsoft Access
StandardProperties() Properties you always want set by default. DAO Programming Code
StripNonDigit() Return only the digits form an argument. (Part of Credit Card validation application.) Credit Card validation
TableExists() Return True if the table exists in the database. Where are macros used in forms or reports?
TableInfo() Display the field names, types, sizes and descriptions for a table. TableInfo() function
TestBand() Illustrate BAND (binary AND) operator. (ADO only.) ADO Programming Code Examples
TestBnot() Illustrate BNOT (binary NOT) operator (ADO only.) ADO Programming Code Examples
Text2Clipboard() Copy text from Access fields to the Windows clipboard. See also Clipboard2Text() Clipboard Functions (32-bit)
TrailingSlash() Add a trailing slash to a folder name if not already present. Determine if a file or folder exists
TrueAsDefault() Illustrates how to assign values for optional arguments of different data types. VBA Traps: Working with Variables
TurnOffSubDataSh() Set SubdatasheetName to [None] for all tables in a database. Problem properties
UnpickAll() Illustrates how to run an action query to set a yes/no field to No. Action queries - suppress dialogs, but know results
UnusedMacros() Find macros not used in any form or report. Where are macros used in forms or reports?
UsePrinter() Assign the Printer object (Access 2002 and later.) Printer Selection Utility
UserCount() Count the number of distinct users connected to the database. ADO Programming Code Examples
Home Index of tips Top