My Access Tips for Custom Microsoft Access

Application Development

by Matthew V Carmichael

Need Help?

My Tips



Coding Tips

Add the Option Explicit statement to modules

This option requires explicit variable declarations in modules. It can be added automatically to new modules by selecting the Require Variable Declaration option in the Options of the VB Editor.

Change the color of Identifier text

Under VB Editor Options, use the Editor Format tab to set the color (I like maroon) of the Identifier Text. This makes it visually easier to identify the variables in the modules.

Use Me. instead of Me!

This will ensure that references to controls on forms and reports are correct (provide a naming convention is followed).

Add comments to code to define what and why.

It is helpful to explain the purpose of specific functions and procedures. Defining business rules with in comments will make it easier to troubleshoot logical errors.

Do not comment the obvious.

Unless the application is to be used as a teaching tool, too many comments is overkill.

DoCmd.OpenForm "frmCustomers" 'Opens the Customer Form

Even the Geico caveman could have figured that one out.

Error Trap Functions and Procedures

Make sure functions and procedure have at least a basic error trap.

Sub Test()
On Error GoTo Err_Trap

   'procedure code goes here

   Exit Sub
   MsgBox Err.Description
   Resume Err_Trap_Exit
End Sub

Consider using string concatenation when the code extends many lines.

Instead of continuing a line of code with & _ - consider using string = strung & “more ”. It is easier to find the syntax error in 20 separate lines of code then it is in 1 line of code that extends over 20. (Especially if you copy and paste SQL from the query GUI like I do.)

Use the Call keyword to execute a Sub procedure.

I use the Call keyword to call a Sub but not to Call a function. This makes it easy for me to visually identify subs from functions within my code.