Code Snippets
+ Tips
Tips To Help You With Microsoft Access
Get a list of form controls sorted by tab order
Scroll in a Microsoft Access text box using the mouse wheel
Microsoft Access does not have built-in mouse wheel scrolling in form text boxes. You can provide mouse wheel scrolling by writing some VBA code in the Form_MouseWheel() event procedure. Trying to scroll in a text box can be particularly frustrating if the bottom of...
Determine if a value is in a VBA array
This function will return a True if a particular value is found in an array. It works with one-dimension arrays only. For example, if astrCity is an array, you could check for the value Chicago like this: If InArray(astrCity, “Chicago”) Then ' Run logic here. End If...
How to time VBA procedures and create a log of elapsed time
This set of functions will let you track how long it took for one or more pieces of VBA code to run. The logic includes three sections: name and initialize the timer, end the timer, and write the results to a log file. Here is an example of timing part of the...
Get total hours and minutes from summing columns of Hours and Minutes
This function can be used to return total hours or total minutes from a column of Hours and a column of Minute values. For example, in the table below, an Hours value was captured separately from a Minutes value in a time clock application. If the columns are just...
Use VBA to add days to a date but exclude Saturday and Sunday
This function finds a date a certain number of days from a start date when weekends are not included. It can be used when your work week does not include Saturday and Sunday and you want to find a business day in the future. Microsoft Excel provides the WORKDAY...
“Folder In Use” Error After Using DoCmd.OutputTo for PDF Export
I got a “Folder In Use” error after using a loop that included the DoCmd.OutputTo macro action to export a Microsoft Access report to PDF files. The error occurred when I tried to rename the folder that the PDF files were exported to. Using the DoCmd.OutputTo created...
How to use VBA to minimize the Office 2007 Ribbon
If you've ever wished you could keep the Office Ribbon, but automatically minimize it to conserve space, this VBA function is for you. The same technique works for Microsoft Access, Microsoft Word, etc. The function includes two pieces of logic. The first piece...
Use VBA to get the maximum lengths of values in a MS Access table
If you've ever imported a text file or a Microsoft Excel worksheet into Microsoft Access, you have seen how Access defaults the text field lengths to 255 characters. This VBA function will display the maximum length of the values currently in each field of a table so...
Put your MS Access database on autopilot
This example shows how to use a command line switch and a scheduled task to start a Microsoft Access application unattended and run some logic automatically.
Perhaps you need a switchboard form to appear for users when they open a database during the day, but …
Remove replication from a MS Access database
This VBA function copies or imports all the objects and database startup properties from a Microsoft Access replicated database into an un-replicated database. It removes tablename_Conflict tables and removes replication-related fields like s_GUID, etc. Background:...
Use VBA to copy MS Access PivotTable values to the clipboard
This function uses a combination of Microsoft Access, VBA, and Microsoft Excel to transfer the values from an Access form in PivotTable view to the clipboard. It is a good example of automation using late binding. The Challenge My goal was to automate the process of...
Use VBA to return a column or row from a MS Excel range address
This VBA function returns the first row, last row, first column, or last column of a Microsoft Excel range address. It is also a good example of using a custom enumerated type. A typical range address is a string that defines a rectangle of cells. It includes dollar...
Find duplicates in a VBA array
This VBA function looks for duplicate values in an array. If it finds some, it produces a list of the duplicated values. It is a good example of how to loop through an array. The argument needs to be an array. Nulls in the array are ignored by the logic, and the...
Use a VBA function to open/get a Microsoft Word document and Find a string
This VBA function opens/gets a MS Word document and finds the next occurrence of a specific string. The function provides a good example of using late binding automation and the AppActivate() function. One advantage of late binding (using generic objects, like objWd...
Using VBA to fill and manage a Microsoft Access list box
This set of VBA functions and MS Access example show how to use code to fill a list box, clear a list box, and retrieve selected items from a list box. The code is composed of 4 functions: ClearListBox() - Clears a list box. It uses a For Next loop and the list box...
Generate a random set of characters using VBA function Rnd()
This VBA function uses the Randomize() and Rnd() functions to generate a random string of characters with a specific length. The included Microsoft Access example applies the function to a simple form that can be used to create a password. When the MS Access database...
Use a VBA collection to store messages
This set of procedures can be used in Microsoft Access to collect messages during a process to display to the user after the process is complete. It provides a good example of how to use collections in VBA. For instance, you could track the progress of some...
Use VBA to get the date of the last occurrence of a weekday
This VBA function passes back the last date that a particular weekday occurred. Code: Public Function LastWeekDayDate(CurrentDate, LastWeekDay) ' This procedure calculates the date of the last occurrence of the day of the ' week represented by LastWeekDay. For...
SQL differences between MS SQL Server and MS Access
Here are some differences in how SQL is written in Microsoft SQL Server versus Microsoft Access. Update query syntax is different. Use + instead of & in queries. Use ' around a date instead of #. Use GetDate() instead of Date(). Use CASE x THEN y ELSE z END...
Making Microsoft Access Work
No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.