If you have visited before ... Refresh the page to see the latest, F5 or Ctrl-F5 on a Windows PC.
VBA |
|||
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 | |
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 |
| 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 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 | |
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 |
Make Cross-Reference Records |
Make cross-reference records by separating values from a field with multiple values. | s4p | |
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 |
What is an error handler? How do you set it up? Article and code. | s4p | |
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 | |
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 | |
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 | |
Get Folder with Office.FileDialog |
Get a folder path using the Office file dialog box | s4p | |
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 | |
Filter a Form
|
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 | |
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 |
Get Query Type + SQL |
VBA function to Get Query Type in words, and SQL to list query information for an Access database. | String | s4p |
Get Unique Filename |
Send path and filename to VBA function, modify filename if not unique in path, respecting extension. Return path and unique filename according to logical rules. | 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 |
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 |
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 | |
What is Ascii? |
What is the ASCII value of each character? | s4p | |
Convert Macros To VBA |
This code converts Macros To VBA for embedded macros on forms and reports, and stand-alone macros. | s4p | |
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 | |
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 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 | |
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 | |
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 |
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_Make |
Make a query or change the SQL of a query. Send query name and SQL. | ||
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 | |
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 | |
Set SubDatasheet None |
Set SubDatasheet to [None] in all tables where needed to enhance performance. | s4p | |
Whistles and Bells |
Extra touches for slick database designs that work and won't take a lifetime to create -- free VBA code | s4p | |
Windows Clipboard |
Use VBA to set text, get text, and clear text on the Windows Clipboard. Works in Access, Excel, Word, PowerPoint, Visio, VBA applications, is fast, and can handle thousands of characters. | s4p | |
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 | |
Query To Word Bookmark |
Write the results of a query in Access to a new table in a Word document after a specified bookmark. | 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 | |
Toggle Bookmark Display in Word Document |
Show or not show bookmark indicators in the active Word document from Word, Access, or another VBA application. | 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 | |
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 | |
