Microsoft Access Blog

Use TryGet pattern for transient values

A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:

For Each ctl In Me.Controls
  Debug.Print ctl.ControlSource 'A potential error
Next

For Each ctl In Me.Controls

Debug.Print ctl.ControlSource 'A potential error

Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:

For Each ctl In Me.Controls
  On Error Resume Next
  Debug.Print ctl.ControlSource 'A potential error
  On Error GoTo 0
Next

For Each ctl In Me.Controls

On Error Resume Next

Debug.Print ctl.ControlSource 'A potential error

On Error GoTo 0

This will work…

Access 2003 Technical Articles

In this section of the MSDN Library, you’ll find technical articles that demonstrate how to build and deploy complex Access 2003 runtime-based solutions, automate Access from another application; use the new XML features in Access 2003, and even how you can animate Access 2003. You can use the TOC to your left to navigate through…

Module level variables

It is common to create a big wall of declaration in a module such as this:

Private strCompanyName As String
Private dteOrderDate As Date
Private lngOrderID As Long
Private objEmail As Object
Private objOutlook As Object

Private strCompanyName As String

Private dteOrderDate As Date

Private lngOrderID As Long

Private objEmail As Object

Private objOutlook As Object

While the code will compile and work fine, the discoverability and naming of the module level variables does become difficult and unwieldy. To help cut down on this ambiguity, it is recommended to create a private user-defined type…

Access 2007: Access Developer Extensions

The Microsoft Office Access 2007 Developer Extensions make it easy to deploy and manage solutions built using Microsoft Access. The Access 2007 Developer Extensions provide packaging and deployment tools and licensing and distribution agreements to make it easier for developers to bring solutions to market. Whether you are working in a small business or a…

Using Custom Functions in Calculated Controls

Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database. Today I am going to explain how custom functions can be…

Self-documenting code

Strong preference should be given to writing code that is self-documenting. A common approach is to write a lot of comments, but the problem with comments are twofold: It actually can harm the readability because it adds to the overall length of code to read and understand. It must be correct or it can end…

Fixes or workarounds for recent issues in Access

Access crashes and slow performance Error: “Automatic configuration of the current version of Microsoft Access has failed.” occurs when you try to start Microsoft Access after updating to Version 1802 [FIXED] ISSUE You may get the following error when start Access after updating to Monthly Channel Version 1802 (Build 9029.2167 or higher): “Automatic configuration of…

Access Web Apps to be retired

It was announced that Access Services is going to be retired. Details of the announcement may be found here: Updating the Access Services in SharePoint Roadmap. Additional timeline information along with possible methods for exporting your data out of an Access web app may be found here: Access web apps no longer supported. While these articles…

#LateBind Constant

In standard code, we make use of #LateBind conditional compilation constant, which is typically defined via project’s Conditional Compilation Argument in the dialog: NOTE: The dialog can be found via Tools → <project name> Properties, which is located below the Options menu item. The menu item caption will be different as it includes the project…

Drag & Drop and Conditional Sorting in Microsoft Access

Drag and Drop In MS Access Doug was on a roll in January of 2004 when he wrote his article on drag and drop in MS Access. He tested it now using Access 2016 and it still works. This article shows you how to add drag-and-drop to your Access application using combinations of multi-value and…