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

More Details



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

More Details



Age Calculations

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.

More Details



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.

More Details



Application.UserControl property

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:
a)    BlockDBOpenDirect
      - the Main app can be opened via the Starter app but cannot be run directly.
b)   BlockDBRemoteAccess
      - 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.

More Details



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

More Details



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.

More Details



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

More Details



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

More Details



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

More Details



Check Procedure Exists

Last updated: 4 Jan 2019

This article contains code to check if a procedure exists in an Access project

More Details



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

More Details



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

More Details



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

More Details



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

More Details



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

More Details



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

More Details



Database Statistics

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

More Details



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.

More Details



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

More Details



Easter Calculator

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

More Details



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

More Details



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.

More Details



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

More Details



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

More Details



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

More Details



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

More Details



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.

More Details



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

More Details



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.

More Details



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.

More Details



Get Network Path

Last updated: 5 Oct 2022

This includes two functions for obtaining the network (UNC) path for a mapped drive.

More Details



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.

More Details



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

More Details



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.

More Details



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

More Details



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

More Details



Is Form Loaded?

Last updated: 23 Oct 2022

This article contains code to check whether a form is loaded (opened)

More Details



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

More Details



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.

More Details



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.

More Details



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

More Details



List Access Error Codes

Last updated: 3 Jun 2018

This is a procedure to create a complete list of Access error codes and descriptions

More Details



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

More Details



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

More Details



List Environ Variables

Last updated: 3 Oct 2022

This article includes a simple procedure for listing all the Environ variables and their values

More Details



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

More Details



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

More Details



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.

More Details



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

More Details



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)

More Details



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)

More Details



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

More Details



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

More Details



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

More Details



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

More Details



Prevent Copy & Paste in Forms

Last updated: 29 Oct 2017

This article explains how to prevent users copying and pasting text in forms

More Details



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.

More Details



Progress Bar

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.

More Details



Rank Order in Queries

Last updated: 9 Jun 2018

This shows one way of creating rank orders in Access queries using the Serialize function

More Details



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.

More Details



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

More Details



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.

More Details



Set Default Printer

Last updated: 2 Dec 2023

This article explains how set the default printer using code.

More Details



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

More Details



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.

More Details



'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.

More Details



View Form Code

Last updated: 11 Mar 2022

This article describes different methods of viewing the VBA code for a form or individual procedure

More Details





Return To Home Page Return To Top