MS Access Beginners:  How to utilize an Experts solution in the VBA editor with MS Access

This tutorial will show you how to take a solution from an expert on EE in VBA and apply it to your database object; very good for the beginner, that has never had to mess with VBA before.

If you are reading this then you have probably at least played around with MS Access, Microsoft’s answer to locally accessible, record limited, relational database application.

Now I am going to make some assumptions, I know I shouldn’t it never seems to be a good thing but I must.  I am going to assume that you have read the above and that you have opened MS Access and at least attempted to create some tables, forms and report; that you know how to create a form or report and add controls to them (naming them as you go).

Sites with Naming Convention Information:
http://en.wikipedia.org/wiki/Leszynski_naming_convention
http://www.mvps.org/access/general/gen0012.htm
http://ezinearticles.com/?Naming-Conventions-for-Microsoft-Access&id=186934


Now is when a lot of newbies find their way to places like Experts-Exchange (EE) looking for answers to a question that has arisen as you attempted to do something new.  This is a good thing; that is why we sit here and monitor sites like these.  We were all new to this at one time as well, and we have all had problems that stumped us. If you do some digging you will find some very good tutorials for all skill levels on sites like EE, so read up, do your research and see if a solution is already out there.  If not then go ahead and open up a question.  Remember to be as specific as you can when you open a question.  Tell us what you are attempting to do, how you have attempted it (supply your example DB or code) and include what your desired answer would include.

That being said, when you get answers a lot of the time you will be provided with some fragments of Visual Basic for Applications (VBA) code, or sample SQL statements. That is where this tutorial comes into play.  My goal is to explain to you where you would use such code, how to access the VBA editor.

Lets talk a little bit about what Visual Basic for Applications (VBA) is - it is a built in (integrated development environment (IDE)) implementation of Microsoft’s Visual Basic programming language. It allows developers to create functions that can be used to integrate automation of procedures and access some of other functionality via Dynamic Link Libraries (DLL).  VBA can be used to manipulate the User Interface (UI) by use of custom menus and toolbars, as well as perform event based tasks throughout the application.  By event based or driven I mean that the VBA architecture technique is designed in such a was the it loops through a pre determined set of events, what is being requested will determine the what set of event are cycled through.  

Thus when a developer requires the application to take some sort of action, lets say to validate data that has been entered into a form field.  Multiple events would be run, the event chain would look like this.
Enter->GotFocus->Dirty->Change->BeforeUpdate->AfterUpdate->Exit->LostFocus
Each of the event would be evaluated and run if they had a function or procedure assigned to them.  Now you can start to see, that if I wanted to validate the users entry, I could place some code in the Dirty/Change or AfterUpdate events, and have the data just entered evaluated for validity.
For more information on events and their sequence see: http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx

Scenario: You have created a simple database with one table that contains the first and last name of your friends, and a form to enter and edit their information.  You are using the Autonumber function as a column in your table as a Key, uniquely identifying each friend.  Just in case you happen to know two John Smiths.  


   


 
Now let’s say that you have submitted a question on EE about how to create a command button that will create a new record so you can input a new friend.  But you want to use VBA to do this (most of these functions are now automatically assigned to macros in 2007 and 2010).

You receive a response from an expert and are provided this code to solve your question.


Expert:
In the onclick event for your button add this code.

 

Private Sub cmbAddNew_Click()
                      
                      'This code will add a new record to your table, and allow you to add a new friend
                      DoCmd.GoToRecord , , acNewRec
                      
                      End Sub
                      

Select all Open in new window

Now how do you do that? Where do you put that code, and how?

Answer:
Select the command button from the ribbon and draw it onto your form


 
If you have the Control Wizard turned on, just hit Cancel to close it out.

Now open the properties sheet

 
Name your command button appropriately (cmdAddNew), and update the caption to “Add New”

Click on the Events Tab then in the On Click event, in then the dropdown select [Event Procedure] then the builder button “…” to open the VBA editor (VBE).

You will be presented with the VBE, and the cursor will be flashing in the onclick event for your command button. Now you can copy paste the code provided for you by the Expert. Make sure to use the debugg funcion from the toolbar to compile your database.  This will find any code errors, before you try to implement the code.  See below for more detailed inoformation about the debugger.


Close the VBA editor, Save your form and test your new button.

Your form should go to a new record and be ready for you to input a new friend.


 
Did it work? If for some reason there was an error, and your code did not execute you will receive and error message.  These messages are normally comprised of at error number and a description of the error.  These descriptions can be very vague, and sometime not helpful at all.
This site can be helpful in determining the cause of your error:http://msdn.microsoft.com/en-us/library/bb221208(office.12).aspx

There are three types of errors:




The VBA Editor (VBE) will show you where in the error occurred, so that you have some idea of what the problem could be.  Sometimes they are easy, like an Error 3060 "Wrong data type for parameter <parameter>" means that you are trying to put text into a number filed or some variation of data types.  Simple fix.  Other times you may see something like this 2445 "Expression too complex", where would you even start to evaluate this error?  If you are not versed with programming, and know how to slowly step through your code, until you find the issue, or how to break it up in to pieces that accomplish the same goal, you would be incredibly lost right now.

There are some things that you can do to track down the culprit in your code. The first is to run the debugger after you update or edit your code.  This can be done using the built in debugger in VBE.  Select Debug in the main toolbar, then select Compile “your database”.  This will run through your code and find any errors before they become a nuisance.
See this site for detailed steps:
http://office.microsoft.com/en-us/help/debug-errors-in-office-visual-basic-for-applications-code-HA001042819.aspx


Now I know that this is a very basic example, but now you at least have an idea of how to get to the underlying VBA editor, for your controls.  You can play with this and learn how to manipulate it to meet your needs.  

I suggest this site for more info on the order of events in MS Access:
http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx

List of most commonly used events for a form:
On Current
On Load
On Click
After Update
Before Update
Before Insert
After Insert
On Delete
On Dirty
On Got Focus
On Lost Focus
On Dbl Click
On Open
On Close
On Activate
On Deactivate
On Error
On Timer
      Timer Interval

Events for a control on a form:
On click
Before Update
After Update
On Dirty
On Change
On Got Focus
On Lost Focus
On Dbl Click
On Enter
On Exit


If you are going to be serious about VBA I suggest that you buy a book, or take a class in some programming language.  VB would make the transition the easiest, but once you learn a language, you can pick up others much easier.

Good books:
A Beginner's Guide - Databases (Covers universal DB design, SQL, Access, Oracle and XML)
Access 2007 VBA - For Data-Centric Microsoft Office Applications
Microsoft Access 2007 All in One Desk Reference for Dummies
Microsoft Office Access 2007 Bible
Microsoft Office Access VBA Programming For Dummies 2007 Edition

Other good websites for reference:
http://www.blueclaw-db.com/
http://www.access-freak.com/
http://office.microsoft.com/en-us/access-help/CL010067704.aspx?CTT=97
http://allenbrowne.com/tips.html
http://www.databasedev.co.uk/
http://www.databasejournal.com/features/msaccess/
http://support.microsoft.com/ph/11265
http://technet.microsoft.com/en-us/default.aspx
http://support.microsoft.com/search/
http://www.mvps.org/access/

Enjoy Access

TLH