All code samples are free to download and free to use in your own applications providing any copyright notices are included without any changes made.
All code will work in both 32-bit and 64-bit Access unless stated otherwise.
We do not give the same support to these code samples as for our commercial programs. However, please contact us if you have questions or suggestions for improvements.
Add colour to queries, combos & listboxes
Last updated: 14 Feb 2018
This article explains how colour formatting can be used for formatting text, number and date fields in queries and tables.
A similar approach can be used for text boxes, combo boxes and list boxes in forms
Add references using code
Last updated: 25 Sept 2022
This article explains how to list references in a table or save these to a text file. It also describes 2 methods of adding references in code - using GUID or file name
Last updated: 28 June 2023
This article gives various functions for calculating age in years, years and months, years, months and days or even calculated to the nearest second.
Align Text With Spacers
Last updated: 23 Nov 2022
This article provides example code showing how you can align text list using monospaced fonts and spacers.
Last updated: 29 Nov 2019
This shows how the UserControl property can be used to restrict how an application can be opened. Just one line of code is required in the Form_Load event of the startup form or in an autoexec macro.
Two examples are provided each containing a Starter app and a Main app:
- the Main app can be opened via the Starter app but cannot be run directly.
- the Main app can be run directly but cannot be opened remotely using automation
Possible uses include using method b) in conjunction with other security measures such as disabling the shift bypass to help prevent hacking using automation.
Autofit or Zoom? Match Text to Form Control
Last updated: 10 Nov 2022
This article describes 3 different methods of ensuring all text is visible in a form control:
a) adjust the width of the textbox to fit the text
b) adjust the font size to fit the control width
c) use a zoom box
The first two methods are achieved using the hidden, undocumented WizHook object.
The third method requires little or no code
Case Sensitive Password Validation
Last updated: 7 May 2023
By default, Access is case insensitive. In certain situations, you may need to check the case of the entered string is correct for each character e.g. when a password is entered on a login form. In such cases, use the StrComp function with a binary comparison.
Change Query Views Using Command Bars Code
Last updated: 31 July 2023
This article explains how to change between the query design, datasheet & SQL views using code
Change Resolution Using Code
Last updated: 4 Oct 2022
This article provides code to change the screen resolution using VBA and to restore the default resolution
Check Internet Connection Using Code
Last updated: 2 Oct 2022
This article provides code to check whether an Access app is connected to the internet
Check Procedure Exists
Last updated: 4 Jan 2019
This article contains code to check if a procedure exists in an Access project
Clear the Immediate Window Using Code
Last updated: 30 Jan 2023
There is no built-in menu item or VBA command to clear the immediate window. This article shows several ways it can be done using code
Close All VBE Windows
Last updated: 18 Apr 2017
This is a simple function to close all VBE windows. This is very useful when so many windows are left open you can't find anything. It also significantly speeds up loading the VBE editor if it doesn't need to load lots of unwanted 'legacy' windows
Compile Modules Using Code
Last updated: 22 Sept 2022
This article explains how to save and compile all VBA modules using code. This requires the use of an undocumented SysCmd function
Count Jobs in Print Queue
Last updated: 19 July 2023
This article provides code to count the number of jobs waiting in the print queue for the default printer
Create ACCDB & ACCDE Files Using Code
First Published: 22 Sept 2022
This article explains how to create both ACCDB & ACCDE files in code. Creating an ACCDE file requires the use of an undocumented SysCmd function
Create Index on linked SQL View
Last updated: 25 Nov 2017
This article explains how to add an index to a linked SQL Server view so that the data can be edited in Access
Last updated: 30 Jan 2022
This includes a procedureGetDatabaseStatistics which examines all tables, forms, reports, macros and modules in a project and produces statistics about the number of fields, controls, VBA functions, lines of code etc.
The results are saved to the VBE immediate window and to a message box
Detailed System Info
Last updated: 20 Feb 2022
This uses Windows Management Instrumenation (WMI) to obtain detailed information about your computer system / processor / BIOS / each hard drive or logical disk.
Disable Design View In ACCDB Files
Last updated: 7 Oct 2022
This article explains how you can use code to disable design view to prevent changes to the design of all database objects in ACCDB files
Last updated: 7 Jul 2017
This is a 3 line function which calculates Easter Sunday for any year up to 2368.
It is adapted from code originally done for Excel by Chip Pearson
Edit Linked Excel Data in Access
Last updated: 26 Feb 2022
Linked Excel files have been read only in Access since 2002 when Microsoft removed the ability to edit them directly.
Normally developers either use automation or import the file, edit then export back to Excel.
However, there is a much better method which is not widely known about. This article shows how linked Excel files can be directly edited in Access. New records can also be added. This approach relies on setting the IMEX value = 0
Fast File Copy using a Windows API
Last updated: 22 May 2022
This article describes a very fast method of copying files using a Windows API. This is particularly useful if you have many large files to copy. A progress dialog is shown automatically.
Encode Map Path Co-ordinates
Last updated: 21 May 2022
This article explains how to encode a series of map co-ordinates as a single string to save space and ensure it fits within the URL character limit
Fill down in tables, queries & forms
Last updated: 31 Jan 2022
This explains a simple method of copying field values from one record to the next
Fix Google maps display issues in Access web browser control
Last updated: 29 Nov 2022
This article explains how to fix Google maps display issues in Access web browser controls based on Internet Explorer
Fix shrunken navigation pane
Last updated: 11 May 2018
Sometimes the width of the navigation pane can become so small that it cannot be expanded by standard methods.
This article describes 3 methods of solving the issue if it happens to you
Formatted Message Box
Last updated: 29 Jan 2023
This provides a simple method of using BOLD text in a standard message box
Unlike standard message boxes, it can also handle text in languages that use Unicode character sets such as Japanese, Greek, Bengali etc without needing to change locale or keyboard settings. This includes right-to left languages such as Arabic and Hebrew.
Get Computer Name
Last updated: 8 Dec 2018
This describes 3 different methods of getting the current computer name using VBA and compares the effectiveness of each method
Get Default Printer
Last updated: 12 Oct 2022
This article provides code that lists all installed physical and virtual printers and identifies the default printer.
Get list of VBA References & locations
Last updated: 5 Nov 2017
This article describes two different routines giving a list of VBA references & their locations.
This can be useful if you need to create a fresh copy of your database or deal with reference issues on a client machine. In such cases, it can be difficult to identify where certain references are located and the small size of the reference window doesn't help.
Get Network Path
Last updated: 5 Oct 2022
This includes two functions for obtaining the network (UNC) path for a mapped drive.
Get User Full Name
Last updated: 11 Mar 2022
This shows how Windows Management Instrumentation (WMI) can be used to get the full name of the user belonging to the network login profile.
Get User Name
Last updated: 12 Sept 2018
This describes 3 different methods of getting the current user name using VBA and compares the effectiveness of each method
Get Value in Previous Record
Version 2.0 Last updated: 3 Dec 2018
This shows a method for calculating the difference between values in the current record with those in the previous record. For example, this approach can be used for calculating energy consumption between meter readings.
Group Page Numbering in Report
Last updated: 21 Dec 2017
This article explains how you can reset the report page numbering at the start of each new group
Hide Access Splash Screen
Last updated: 25 Feb 2022
This article explains how to remove the splash screen that normally appears when you open any Access application
Is Form Loaded?
Last updated: 23 Oct 2022
This article contains code to check whether a form is loaded (opened)
Is Form Open as a Subform?
Last updated: 11 July 2023
This article contains code to check whether a form is open as a subform
This can be important to determine whether or not other code should run.
For example in automatic form resizing
Is Report Open as a Subreport?
Last updated: 11 July 2023
This article contains code to check whether a report is open as a subreport
This can be important to determine whether or not other code should run.
Is Location Trusted?
Last updated: 9 July 2023
All Office apps must be trusted before code can be run. The app itself can be trusted or it can be saved to a trusted location.
This article includes code to check whether an Access app is running from a trusted location.
Linked No Tables
Version 2.0 Last updated: 6 Feb 2019
This DEMO application shows a method of running a split database where the connection strings cannot be viewed by end users
The front end (FE) database has no linked tables.
The FE file has 2 forms and 1 report none which have a permanent record source
The backend (BE) contains 1 ‘deep hidden’ table though it would work equally well with a standard table.
The record source for each FE object is set automatically to the BE table when the object is opened. It is cleared again when the object is closed
List Access Error Codes
Last updated: 3 Jun 2018
This is a procedure to create a complete list of Access error codes and descriptions
List All Installed Apps
Last updated: 6 Oct 2022
This article provides code to get a list of all installed apps and program updates as displayed in the Apps section of Windows Settings
List All Module Procedures
Last updated: 6 June 2023
This article contains code using Visual Basic Extensibility to list all procedures in each code module (form / report / standard / class) to the Immediate window
List Environ Variables
Last updated: 3 Oct 2022
This article includes a simple procedure for listing all the Environ variables and their values
List Tables with Complex Fields
Last updated: 21 Dec 2022
This article provides a simple method of listing all tables with complex fields in a database.
This can be useful when analysing client databases e.g. before upsizing to SQL Server
Manage Nav Pane / Ribbon / Taskbar / Application Window
Last updated: 25 Oct 2017
This article explains how to manage parts of the application interface using VBA:
a) Navigation Pane - hide / mimimise / maximise
b) Ribbon - hide / minimise / maximise
c) Taskbar - hide / show
d) Application window - hide / show
Mamage Totals Row Using VBA
Last updated: 30 Oct 2023
This article discusses how totals rows can be added/modified/removed from Access tables & queries using VBA.
Masked Input Box
Last updated: 28 Aug 2018
This is an updated version of the masked input box code originally written by Daniel Klann in 2003 for 32-bit Access only.
This updated version will work in both 32-bit and 64-bit Access
Message Box Constants & Values
Last updated: 26 Nov 2022
This page provides a convenient reference list of message box constants and their equivalent values. You can use either values or constants in your code (or use them interchangeably)
Missing Trigonometric Functions
Last updated: 20 May 2022
Four of the most commonly used trigonometric functions are built in to the standard Access VBA reference library:
- atn (arctangent of a number = angle whose tangent is a specified number)
- cos (cosine of an angle)
- sin (sine of an angle)
- tan (tangent of an angle)
This article contains 6 missing trigonometric functions:
- ASin (arcsine = angle whose sine is a specified value)
- ACos (arccosine = angle whose cosine is a specified value)
- ACot (arccotangent = angle whose cotangent is a specified value)
NOTE: cotangent = 1 / tangent = adjacent / opposite in right-angled triangle
- ASec (arcsecant = angle whose secant is a specified value)
NOTE: secant = 1 / cosine = hypotenuse / adjacent in right-angled triangle
- ACsc (arccosecant = angle whose cosecant is a specified value)
NOTE: cosecant = 1 / sine = hypotenuse / opposite in right-angled triangle
- Atn2 (arctangent of two values - latitude/longitude)
Module Type Property
Last updated: 5 July 2023
This article describes a simple way of identifying standard and class modules by using the module Type property
Negative Total Pages on Report
Last updated: 17 Jan 2019
If you have a VERY large report with more than the integer limit of 32767 pages, Access will display the total pages as a negative number
For example: Page 1 of -15440 pages
This article explains how to deal with that situation if it arises
On/Off Toggle Slider Control
Last updated: 23 May 2022
This article is the first in an occasional series of additional controls that can be added to Access without requiring ActiveX functionality. It shows how to create a simple on/off toggle control
Open Database with Shift Bypass using Code
Last updated: 3 Nov 2023
This series of three articles shows how to open an external database using automation to press the shift key and bypass startup code. Code is provided to handle password protected databases and optionally open them exclusively
The articles also include code to enable / disable the shift bypass properrty and help protect your databases
Prevent Copy & Paste in Forms
Last updated: 29 Oct 2017
This article explains how to prevent users copying and pasting text in forms
Prevent Opening Objects From the Navigation Pane
Last updated: 5 Nov 2023
This article demonstrates 2 ways of preventing forms and reports from being opened directly from the navigation pane. This may be useful for security or if other code needs to run prior to the object being opened.
Last updated: 9 June 2022
This is a simple but effective progress bar for use in Access forms. Several different versions are provided based on a timer or a series of events. The progress bar can use a solid bar, image or gradient fill.
Rank Order in Queries
Last updated: 9 Jun 2018
This shows one way of creating rank orders in Access queries using the Serialize function
Remove core Windows 'modern' apps
Last updated: 16 Jun 2018
Certain core Windows modern apps CANNOT be removed using Add/Remove programs.
However if you have a problem, these cannot be installed from the Windows Store as they are already installed! CATCH-22!!!
This article explains how to remove these core Windows modern apps using Powershell.
Reset the Property Sheet
Last updated: 24 Jun 2023
This article explains how to reset the property sheet position if it cannot be found after resizing or relocation
Set Character Limit in Long Text (Memo) Fields
Last updated: 26 Mar 2022
This article explains how the number of characters in long text fields can be limited using VBA code. It also includes code to check the spelling of entered text. This is particularly useful if text entry is done using the Windows dictation tool.
Set Default Printer
Last updated: 2 Dec 2023
This article explains how set the default printer using code.
Unicode Input Box
Last updated: 29 Jan 2023
This function allows input boxes to handle unicode character sets used in languages such as Bengali, Chinese etc without changes to locale or keyboard settings. It can also handle right-to-left languages such as Arabic and Hebrew
The standard input box only works with Latin script languages such as English, German, Spanish unless additional changes are made to locale and keyboard settings
Update All Form & Report Fonts
Last updated: 11 Oct 2022
This article contains code that will globally change the selected font across all forms and reports in the database.
It is strongly recommended that you backup the database before running the code.
'UPEND' or 'UPSERT' Query
Last updated: 18 Sept 2018
A very common database task is to both append new records to a table and update existing records. The standard approach is to use an APPEND query and an UPDATE query.
This article explains how to combine both of these into a single UPEND (AKA UPSERT) query.
View Form Code
Last updated: 11 Mar 2022
This article describes different methods of viewing the VBA code for a form or individual procedure
Return To Home Page
Return To Top
Complete List of Code Samples