Using VBA Efficiently in Access 2010

By Alison Barrows, Margaret Levine Young, Joseph C. Stockman

Part of Access 2010 All-In-One For Dummies Cheat Sheet

When automating an Access 2010 database application using VBA, you can get lost with a blank page on which to begin writing code. Where do you start? Here are a few simple guidelines to follow that will have you writing VBA code like a pro:

  • Get help. Yes, this article lists a few ways to enhance using VBA in Access, but press F1 at any time to start Microsoft Office Access Help, where you can learn about and see examples of VBA code.

  • Use the Object Browser. The Object Browser lets you explore the different properties and methods of the objects in VBA. From the VBA Editor window, choose View → Object Browser — or simply press F2.

  • Get more help. Sure, the built-in help tools within Access 2010 VBA are wonderful, but you can also use your favorite browser to search the Internet for help on a variety of topics on writing VBA code. You can even find examples that you can steal — or borrow — for your own project.

  • Handle your errors. Even the most perfect programmer can’t stop errors from occurring, but he or she can stop them from bringing a program to a grinding halt. Use VBA’s built-in error handling with the On Error Goto and Resume statements to trap errors and change the program flow so that your applications don’t frustrate the people using them.

  • Use functions and sub procedures. Use functions and sub procedures to handle tasks that will be performed by different areas of your program. As a general rule, if you find yourself copying and pasting code from one area of a program to another, you might want to put that code in its own procedure.

  • Convert macros to VBA code. The Macro designer in Access 2010 lets you click and choose from lists of predefined tasks to automate your application. Build a macro that does what you want, and then convert that macro to VBA code so you can see how it would like if you typed it from scratch.