Ms Access Guru

thank you for helping more VBA code get posted

VBA Code

Here is VBA code you can use in your projects. A lot of this code works no matter where you are in a Microsoft app with VBA (Office) ... Excel, Word, PowerPoint, Project, Visio, more, ... and Access!

If you have trouble with any of the downloads, remember to Unblock (remove Mark of the Web); steps are here if you need them: https://msaccessgurus.com/MOTW_Unblock.htm

categories

API Application Color Control DAO Data Date Document Draw Excel automation File fso FileSystemObject Form Function Image Information International Macro Media Module PowerPoint Properties Query Registry Report SQL SQL Statement Table Word automation Word

contributors

Geoff Griffith (GG) crystal long (s4p) Branislav Mihaljev (BM) Gustav Brock (GB)

If you have visited before ... Refresh the page to see the latest, F5 or Ctrl-F5 on a Windows PC.

VBA

API     

Goto Top  

Get Computer Name

Get the computer name using a Windows API (application programming interface) function. String s4p

GetDeviceCaps: get pixel/inch

Get the pixels per inch for the screen using a Windows API (application programming interface) function. Long s4p

Sleep

Pause executing VBA to let something else happen, like wait for a form to close, or something external like Transfer Spreadsheet from Excel, or copy and move files. Better to Sleep than to loop DoEvents. s4p

Application     

Goto Top  

Set Application Title

Set the application title to a specified string. Easily show the path and filename of the database when it is opened using the AutoExec macro to run a function. Boolean (ignored) s4p

Color     

Goto Top  
Access Command Button Color Properties, built-in Color-Picker Make command buttons on Access forms come alive by changing colors as a user hovers over a button or clicks it. Popup the built-in color picker. Download sample database to use for picking colors you like. s4p

Control     

Goto Top  

Control BeforeUpdate - stay if unique, go if duplicate

Ensure value is unique -- Use the Control BeforeUpdate event move to existing record if the value is a duplicate, or stay and edit if it is unique. s4p

Clear List

Clear the selection of a listbox control s4p

Drop down

Drop the list of a combo box control. s4p

Go Start

Go to the start position if there is no value. Useful for controls with an Input Mask, so no matter where you click, you're at the beginning when you start typing! s4p

NotInList

Add a choice to a combo box list by adding a record to the table for the rowsource in the NotInList event of the combo box. s4p

RowSourceType Function

Callback function for RowSourceType to populate rows and columns of a combo box or list box s4p

DAO     

Goto Top  

Create Table and Make records

Create a table if it doesn't already exist, define a field, make a PrimaryKey index, and put records in. This example builds a table for storing sequential numbers that can be used for printing multiple copies of a report, getting records for every day even if there is no data, finding gaps, ... useful for all kinds of things! s4p

Table Exist?

Return True or False if table exists, or does not, in the current database, or other database. Optionally, you can pass a parameter that causes query names to also be checked. Boolean s4p

Query Exist?

Return True or False if query exists, or does not, in the current database, or other database. Boolean s4p

Data     

Goto Top  

Make Cross-Reference Records

Make cross-reference records by separating values from a field with multiple values. s4p

Date     

Goto Top  

Date and Time functions

A vast collection of functions for all sorts of handling Date and Time in Microsoft Access and Microsoft Excel by Gustav Brock GB

Error Handling      

Goto Top  

Error Handling

What is an error handler? How do you set it up? Article and code. s4p

Document      

Goto Top  

Document Access tables to Excel

Document your Access database tables to Excel. Automatically make a list of all the tables, create a data dictionary, and more. Documentation is written to an Excel workbook. Long s4p

Document Fields in Access tables to Debug window

Document the fields in an Access table to the Debug window. s4p

Excel automation     

Goto Top  

Export Query by Groups to Excel Sheets and/or Files

Export records from a Query, by whatever grouping you want, to Excel on different sheets in same file, or different files. Long s4p

Get International Windows Region setting

VBA to automate Excel to get an International Windows Region setting, such as a country code or list seperator. List of index numbers to get specific information from the Excel application International property, and values for US. SQL for a query in Access using a table with sequential numbers to show international settings for your Windows Region
Benefits and drawbacks of using Excel to get this information. Alternate methods.
variant s4p

Import Excel Worksheets

Open an Excel workbook, loop through its sheets, and write data to a table. This makes it easy to summarize, forecast, slice and dice, make charts, and calculate metrics. s4p

File

Goto Top  

Count Files

Count the number of files in a specified folder path. Long s4p

Count Folders

Count the number of folders in a specified path. Long s4p

Loop and Rename

Loop through a folder of files and rename them to include date and time -- especially nice for those hundreds of pictures! s4p

Make a Path

If path doesn't exist, then make each folder it needs. Return True if path is good. Boolean s4p

Read Text File

Open a text file and assign its contents to a string that is returned. String s4p

Save String as a File

Create or modify a file whose contents is the text you specify. s4p

Save File with Unicode characters

Create or replace a file from data that has Unicode characters s4p

Remove  from beginning of a text file

VBA to remove  from beginning of a text file so you can link to it without getting odd, unwanted characters. s4p

File

Goto Top  

Get Folder with Office.FileDialog

Get a folder path using the Office file dialog box s4p

FileSystemObject     fso    

Goto Top  

CopyFolderFilesToFolder

Use the FileSystemObject, fso, to copy files from one folder to another, one at a time. Show progress to user using the Status Bar. Option to preface new filename with whatever text your specify, Option to use a mask to limit whick files are copied, like *.jpg. Default is *.* s4p

Form

Goto Top  

Filter a Form
(or Subform)

Filter a form OR subform. Build a criteria string that only considers what is filled. Set Filter property and turn the form filter on or off. s4p

Filter a Subform using controls on Mainform

Filter a form or subform by user-specified values collected with checkbox, option group, combobox, or textbox. Concatentate criteria when it isn't Null using an exact value, LIKE operator and wildcards, or IN to match several values. s4p

Subform Filter using controls on Mainform

Filter a form or subform by user-specified values collected with checkbox, option group, combobox, or textbox. Concatentate criteria when it isn't Null using an exact value, LIKE operator and wildcards, or IN to match several values. s4p

Use Control BeforeUpdate to check Unique

Use a control BeforeUpdate event to ensure that a value is unique, not a duplicate. If another record has the value already, give the user a choice of undoing changes and moving to that record, or staying to edit the value s4p

Find a Record

Find a Record. Often called on AfterUpdate event of Combo Box. s4p

Go to First

Go to the first record on a form. Byte (not used) s4p

Go to Last

Go to the last record on a form. Byte (not used) s4p

Go to Next

Go to the next record on a form. Byte (not used) s4p

Go to Previous

Go to the previous record on a form. Byte (not used) s4p

Go to New Record when Form Loads

When you want a form to open to a new record use VBA in the form LOAD event. s4p

List Hot Keys

List hot keys for the active form in Access. Message box shows a list of the characters that have been assigned. Immediate (Debug) window – Ctrl-G – also shows control name and caption. Not only see what's been assigned, but also discern what's available to use. + Add-in for Access + VBA procedure to bubble sort a passed array and correlate 2 other passed arrays. s4p

Make Letter Buttons

Make it easier to jump and find! Automatically create controls for Letter buttons B-Z after you make a command button and write code for button A, which is used as a pattern for the rest. Optionally, also make command buttons for numbers 0-9. Be in the design view of a form that is active. s4p

Rename associated Labels

Rename associated Labels on active Access form. Depending on how you create forms, label controls can have wonky names. Loop through each control on a form with VBA. If there's an associated label, rename it to correlate to the control it's tied to. s4p

Sort Form by Column of Combo Box

No need to add more to record source of a form to sort ... if a combo box can see it, you can use it! s4p

Function

Goto Top  

CorrectName

Replace bad and unwanted characters in a string with underscore. String s4p

Get Age

Age in years given a date of birth. Optionally, specify date to calculate age as of. Integer s4p

Get Distance

Distance in miles, kilometers, or nautical miles from two points of Latutude and Longitude Double s4p

Get DataType

Return the string name or abbreviation of a numeric data type String s4p

Get Long Integer From String

Get whole number from a string with other characters. Long s4p

Get Nth Part

Get the Nth part in a in a string, number, or date. String s4p

Get Nth Word

Get the Nth word in a string. String s4p

ProperCase lowercase UPPERCASE

Convert a string to ProperCase, lowercase, or UPPERCASE ... in expressions and VBA. Use built-in functions StrConv, UCase, LCase, or a custom function (user-defined function --UDF), to make more adjustments depending on what you need. String s4p

International      

Goto Top  

Get International Windows Region setting

VBA to automate Excel to get an International Windows Region setting, such as a country code or list seperator. List of index numbers to get specific information from the Excel application International property, and values for US. SQL for a query in Access using a table with sequential numbers to show international settings for your Windows Region
Benefits and drawbacks of using Excel to get this information. Alternate methods.
variant s4p

Image

Goto Top  

Resize Image WIA

Resize an image with VBA using capabilities provided by Windows using the Windows Image Acquisition (WIA) library. Boolean GG

Resize Image Irfanview

VBA code to use Irfanview (freeware image editor) behind-the-scenes to resize an image and save it as a new file. Double s4p

Convert Image to B&W Irfanview

Change the color of an image to black and white. This VBA shells to Irfanview (freeware image editor) and converts an image by specifying bpp (bits per pixel) to be only 1, which means just 2 colors (black or white), then saves the converted file with a different name. s4p

Information

Goto Top  

What is Ascii?

What is the ASCII value of each character? s4p

Macro

Goto Top  

Convert Macros To VBA

This code converts Macros To VBA for embedded macros on forms and reports, and stand-alone macros. s4p

Media

Goto Top  

Play Sound

VBA code to play a sound every second is in my analog clock in Access. The example I intended to post here, which is not done, has turned more into managing sound files ... including metadata such as duration, number of channels, and sample size in Hz. Metadata can be gotten in Access using Wayne Phillips' File Property Viewer on EverythingAccess that is amazing, free (donations appreciated), and (somewhat) open! With it, you can look up detailed file information provided by the Operating System for any file. Wayne also built in capabilities for developers to use VBA to get these properties, given a path and filename. It is fantastic. s4p

Registry

Goto Top  

Get Windows List Separator

VBA function to read the Registry to get the Windows List Separator character. By Branislav Mihaljev, with thanks to Daniel Pineault. String BM

Report     Draw

Goto Top  

Report Draw VBA Reference

VBA syntax to draw on reports. VBA Methods to draw shapes and write text on Access reports: lines, rectangles, circles, elipses, arcs, points, write text and get its size. Properties to position, size, scale, style, and color. Color functions to get long integer color values, and trignometric functions to calculate angles and positions. s4p

Draw Circles

Practical application with sample database and VBA listing. Visualize Yes/No data with something better than a tiny checkbox. Use the VBA CIRCLE method to draw circles in the Detail section of an Access report so the drawing changes according to the data on each record. A simple circle can do a lot of different things, and this is just the tip of the iceburg! s4p

Draw Lines and Boxes

Draw on Access reports! VBA examples using the report Line method to draw lines and boxes. Draw in a detail event to visualize data on each record, and to conditionally draw a border around the section. Draw in Page event for page border and dividing lines. s4p

American Flag

Draw an American flag on an Access report, any size, any position. s4p

Birthday Balloons

Draw Birthday Balloons on an Access Report s4p

Christmas Tree

Draw a Christmas Tree with a star on top! s4p

Dial

Draw Dial showing needle pointing to value. Visualize fractions and percentages. Call code to draw a dynamic dial on an Access report based on your data. s4p

Meter

Visualize fractions and percentages! Call code to draw a dynamic meter on an Access report based on your data. Zero is at the top with values increasing clockwise. Value is a fraction greater than or equal to 0 and less than or equal to 1. Choose colors and size. s4p

Moon phases

Draw moons in any phase on your Access reports! s4p

Rainbow

Liven up your Access reports with rainbows! s4p

Rounded Rectangle

Draw rounded rectangles alone or as part of another drawing. s4p

Snowflake

Add fun snowflakes to your Access reports to make them more festive. VBA procedure that's easy to call. s4p

Snowman

Draw a Snowman! Make your Access reports fun. Change colors to indicate different things. VBA procedure that's easy to call from code behind your reports. Store colors in a table to make things more flexible. If you can imagine it, Access can do it. s4p

Solstice

Draw the Earth on its orbital path around the sun when it's at the position for the Winter Solstice in the Northern hemisphere. See how Earth's tilt causes part of the planet to not get any light, such as in Northern Norway. s4p

Stoplight

Draw Stoplight showing 1=Green, 2-Yellow, 3-Red. s4p

Shortcut Keys

Create a handy reference with Access and VBA shortcut keys on a report. Write text using the Print command. Draw rectangles using the Line command. Download database has a table with shortcuts. s4p

Module

Goto Top  

Close open Modules

Close open standard modules. Optionally keep a module open by passing a module name or determining the active module (default), or close them all. Specify whether to save now, not save, or prompt (default) to save later. This is especially nice for developers so you can then open just what you want to focus on, switch faster, and realistically tile windows. Integer (and String) s4p

Remove Line Numbers

replace Line Numbers in VBA from all classes and modules within the current database file with spaces GG

ASCII for each character?

When strings don't look like you expect, use VBA code to show each character, its ASCII value, and position in the string. Write results to the Debug/Immediate window. s4p

PowerPoint

Goto Top  

Slide Index

Before your PowerPoint presentation, run VBA to create an index with slide number and title. If you want to go to any slide during your presentation, you can just type its number (if you know it!) and press ENTER. Updated to include Master Layout. s4p

Presentation Shortcut Keys

Helpful shortcut keys to know before you give a PowerPoint presentation s4p

Properties

Goto Top  

Properties

This module has procedures to read and write properties ... custom and built-in, for the databases and objects. Also show what properties are set, and delete properties. Property settings are persistent when the database is closed and opened again, so it's a good place to put information specific to a user's front-end. s4p

Query

Goto Top  

Query_Make

Make a query or change the SQL of a query. Send query name and SQL.

SQL

Goto Top  

Create SQL to Link to a CSV file

VBA to create an SQL statement that links to a CSV file. String s4p

Drop a table

Delete a table using an SQL statement with DROP TABLE. Optionally, specify another database (default is current database). s4p

Loop and Trim Text

Loop through all the tables and fields. Trim leading and trailing spaces from text by constructing and running SQL for an update query to Trim. Optionally, then also replace ZLS ("") with Null. This helps data compare and sort better. s4p

SQL Statement

Goto Top  

Basic SQL Syntax

Syntax for basic SQL statements in Access.
SQL = Structured Query Language
s4p

Document Query Tables

Get a list of each query's underlying tables and queries. s4p

Document Query Expressions

SQL to list Expressions in queries of an Access database s4p

Document Relationships

SQL to list Relationships in Access database s4p

Document Object Names

SQL for Query to List Object Names in Access database s4p

Table

Goto Top  

Set SubDatasheet None

Set SubDatasheet to [None] in all tables where needed to enhance performance. s4p

Whistles and Bells      

Goto Top  

Whistles and Bells

Extra touches for slick database designs that work and won't take a lifetime to create -- free VBA code s4p

Word automation

Goto Top  

Word Automation VBA Code

VBA syntax to create Word documents ... make beautiful reports from Access! or Excel, PowerPoint, and even Word! Documents showing examples of Word documents created by Access. Download ACCDB that creates Word documents. s4p

Word Make Table with optional borders

VBA code to create a table in a Word document with a specified number of rows and columns. Optionally add borders, shading for the first row, and specify column headings. The doument object is sent so you can use in Word or automate from Access, Excel, PowerPoint, or something else. s4p

Listbox with Open Word Documents

VBA code that runs from Access to look at Word and get the list of open documents for the RowSource of a listbox. Select the active document. s4p

Modify Word Document Properties with Access

Why use a Word Document Property? Why not bookmarks and/or merge fields. Sometimes those will be just fine. The purpose of understanding Document Properties is to give you flexibility. Easily and quickly change ActiveDocument properties in Word using Access. Update everwhere. s4p

Save as PDF using Word

Convert anything Word can open to a PDF file! Use Word to open a file and then save it as PDF using VBA. Run in any Office application such as Access, Excel, PowerPoint, Visio, Project, ... or from Word on the active document using the special version starting with "RunFromWord". s4p

Word Set Margins

After setting your Word document object, it's good to set the margins for it. This is done by passing the Word document object so PageSetup can be done s4p

Word

Goto Top  

List Built-in and Custom Word Document Properties

Document Properties in Word are a great way to substitute data in your document all over! ... in many places or just one. You can also specify format when inserting such as how to write dates -- and further, select it and make it look different. VBA code you can run from Word and to get a list of the built-in and custom properties in your document, and steps to use them. s4p

Count Fields

Count the number of fields in a Word document or the number of fields that contain a particular property. s4p

Field Shading

Quickly change your view of field shading in Word between always, never, and when selected using VBA. Run code or assign keyboard shortcuts to trigger your macros. s4p

Selection Summary

VBA to get summary information about a Word selection such as where and what it is, and number of paragraphs, words, characters, bookmarks, comments, and fields. Runs from Word. s4p

Unlink Fields in Word

Convert updated fields in Word to text. s4p

VBA in Access download Goto Top  

Highlight Current Record

Make it easier to see the current record on your Microsoft Access continuous or datasheet forms by adding a yellow highlight behind the current record. Uses Conditional Formatting, a control to keep track of the primary key value, a control to change color, and some VBA. See how to do it in a sample database you can download, and watch the video tutorial. s4p

Goto Top  

Backstory

In December 2018, to get a good foundation for the code portion of this site, a VBA code snippet was posted for each day of Advent. Here is a calendar of those posts, made with Access!

AdventCalendar

Download the calendar tool to use for yourself. Source code is open, so you can learn and customize. Html Calendar Report from Access

Since then, there is more code, and code from another too (thanks Geoff). I hope more will contribute ... Access is the best product ever!

Goto Top