Code Library

Code Library
  • RegOp Class for 64-bit VBA: (Feb 17, 2021) Updating a classic VBA registry reading and writing class module for 64-bit compatibility.
  • RegOp Quick Tip: (Feb 18, 2021) Be respectful of your user's registry. Store all your applications' settings in a dedicated publisher subkey.
  • My Dirty Little Secret: (Nov 2, 2020) I don't use Me.Dirty = False in my #msaccess code. I wrap it up and call it from a "guard clause" instead.
  • Filling in the Blanks: (Nov 3, 2020) One of the keys to being a good developer is to make the *easy way* also be the *right way*. Exhibit A: the ControlIsBlank() function.
  • Why so lazy, Access?: (Nov 4, 2020) Introducing FormIsOpen() and ReportIsOpen(), two simple functions to help deal with Access's laziness.
  • Clearing a List Box in Access: (Dec 21, 2020) Sometimes you need to wipe the slate clean and start fresh. Here's a simple function to do just that for an Access list box.
  • Unicode-Friendly MsgBox: (Dec 24, 2020) There's a great big world outside of the ANSI bubble. Make every MsgBox in your program Unicode-safe in no time at all with this drop-in replacement.
  • Setting MultiSelect At Runtime: (Jan 14, 2021) You can't actually set a list box's MultiSelect property at runtime. But there are a few ways to work around the limitation. Let's explore them.
  • PreviewReport Function: (Mar 16, 2021) This custom function is the simplest and safest way to preview reports in any Microsoft Access application.
  • The ArrowKeyNav Routine: (May 1, 2021) Enable Excel-like navigation in your continuous forms by overriding the default behavior of the up and down arrow keys.
  • ComboBox Dropped Down State: (May 10, 2021) Overriding the up/down arrow key behavior improves the user experience on a continuous Access form. But what if the user drops down a combo box?
  • Get Form By Control: (May 14, 2021) A VBA function that returns the first form parent of the control you pass to it.
  • Microsoft Access: Check if Form Object is a Subform: (May 15, 2021) How do you check if the form object is a subform in Access without triggering a runtime error? Hint: this is a trick question.
  • Get Top Form By Control: (May 17, 2021) A VBA function that returns the topmost form parent of any control you pass to it.
  • Combo Boxes and Target Sizes: (Jun 7, 2021) Just like in darts, it's easier to hit a larger target when using the mouse. With this in mind, let's make our combo boxes more user-friendly.
  • Avoiding the Error "invalid reference to the property CurrentRecord": (Jul 1, 2021) Error number 2455, "You entered an expression that has an invalid reference to the property CurrentRecord," is annoying, but there is an easy fix.
  • How to Highlight the Current Record in a Continuous Form: (Jul 2, 2021) Step-by-step instructions for applying a custom highlight to the currently selected record in a continuous form in Microsoft Access.
  • Quoth thy SQL? Evermore!: (Sep 29, 2020) Solving the "O'Malley problem" with dedicated functions to sanitize our strings. Little Bobby Tables would be proud.
  • Come Together: (Sep 30, 2020) Do you build strings in loops? Stop trimming the delimiter at the end of the loop. There's a better way.
  • Part and Parse()-l: (Nov 8, 2020) Take the Tag property and OpenArgs argument to the next level. This function makes it easy to assign and extract multiple values from a single string.
  • Now you have two problems: (Nov 16, 2020) Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. --Jamie Zawinski
  • Converting Numbers to Text: (Nov 28, 2020) Need to print business checks directly from Microsoft Access? You'll need a way to convert numbers into text. The NumToString function does just that.
  • A Function to Quote Literal Dates When Building SQL in VBA: (Dec 13, 2021) This improved version of my date-quoting convenience function eliminates a common source of date-handling ambiguity via the ISO date format.
  • How to Remove Special Characters from a VBA String: (Dec 23, 2021) A simple function to remove special characters from a string in VBA. Useful for sanitizing file names, sheet names in Excel, and lots of other stuff.
  • Reading Text Aloud in Microsoft Access: (Jan 7, 2022) Want a quick way to get started with Text-to-Speech in your VBA application? This 4-line method gets the job done and requires no references!
  • The Pluralize Function: (Apr 1, 2022) You don't have to settle for messages like, "1 record(s) inserted." This simple function is an easy way to format strings for pluralization in VBA.
  • Printf Function: (Apr 19, 2022) User @wqweto's string interpolation VBA function has some neat tricks, like using Unicode's Private Use Area to get safe temporary placeholder characters.
  • TagWrap() Function: (Jun 20, 2022) This convenience function helps you write more readable code when building strings with HTML-style opening and closing tags.
  • XmlWrap() Function: (Jun 21, 2022) This convenience function helps you write more readable code when building Office Ribbon-flavored XML strings.
  • CreateGuid: A Reliable Way to Generate GUIDs in VBA: (Aug 5, 2022) A well-tested method to generate GUIDs in both 32-bit and 64-bit VBA.
  • Fun with Form Windows: (Nov 26, 2020) Using my FillAccessWindow function to resize Form and Report objects relative to the amount of available space on the Access canvas.
  • Get Back Here, Form!: (Nov 27, 2020) What happens if the user resizes their Access window so that our form can't open in its entirety? KeepFormOnCanvas() to the rescue!
  • Get a Handle on Window State: (Dec 28, 2020) Using the IsZoomed and IsIconic API calls to return the window state (minimized, maximized, or restored) of a form, report, or Access itself.
  • UseHand(): Changing the Mouse Cursor in Microsoft Access: (Aug 31, 2022) A classic Microsoft Access mouse cursor trick gets simplified and updated for 64-bit VBA compatibility.