Provided by Allen Browne, November 2006, Updated April 2010.
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 |
| Function | Description | Web 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 |