Monday, August 27, 2012

Getting Started with VBA in Access

If you're new to programming, but want to make a start of it, Visual Basic in Access can be a great introduction.  In some ways VB can be easier to grasp and use than even Access macros, with enough time. No doubt there is a bit of a learning curve, but not only can you become more productive, but it can open the door to even more complicated programming (if you want to pursue that path).

Getting Started
The easiest way to get started is by example.  Here's how:

  1. Add a new button to your Access form.
  2. In the wizard, choose an option to have the button do something like close your form.
  3. Once the wizard is complete, double-click the button.
  4. In the properties window, select the "Events" tab.
  5. Notice the On Click line has "[Event Procedure]" in the field.
  6. Click the ellipsis "..." next to the box.
  7. The Visual Basic editor will open and you can see the code that Access created for you.
What Does it All Mean?
There is a lot to learn about Visual Basic, but with some simple background you'll know what matters and what doesn't.  In the VB editor, you'll see that your code is surrounded by:

Private Sub CommandButton1_OnClick()
  
End Sub


The "Private Sub" just means the code only applies to the current form, and can't be "seen" outside of the form.  The "CommandButton1" part just refers to the name of the button clicks.  And "_OnClick()" is the event that fires when you click the button.  You'll see that there are lots of events you can take advantage of, like when a user clicks, hovers over, presses a button.  There are tons of events you can fire in Access.  Finally, the "End Sub" just means it's the end of the subroutine (section of code that applies to the "OnClick" event.

So in between the "Sub" and "End Sub" you'll be able to tell Access what you want to do.

Docmd Is the Workhorse of Access VBA
There is one VB command every Access user should learn: Docmd.  Learning just that will take you very far.  For example:

Docmd.OpenForm "YourFormName"


Docmd.OpenForm will open a form you specify.  You can use the Intellisense to understand what Access needs to actually perform the command. And in many cases, will also give you dropdowns with options to choose from.  In the picture, you can see that the View options will let you open the form in Design view, as a datasheet, Normal, etc.

There's much more to learn, but this should get you started.  Google or Bing searches are a real treasure.  If you need to do something in Access, there's a 99.99% chance someone has had the same need, and someone has an answer out there.