<

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

Published on
16,699 Points
5,399 Views
13 Endorsements
Last Modified:
Awarded
Community Pick
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.  


 Friends Table  


 Friends Form
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

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


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

Now open the properties sheet

 Ribbon Prop Sheet Prop 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.


 New Friend Form
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:
Syntax errors:
A variety of errors related to entering the code itself. These include
incorrectly spelled keywords, mismatched parentheses, and a wide
variety of other errors. VBE flags your syntax errors and you can't
execute your code until they are correct.

Run-time errors:
These are the errors that occur while your code is executing. There are
a lot of different types of run-time errors. For example, if your code refers
to an object that doesn't exist, you'll get a run-time error. VBE
displays a message when there is a run-time error.

Logical errors:
These are errors that occur through faulty programming. Logical errors
may or may not cause a run-time error. In many cases they will simply
produce incorrect results.


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
13
Comment
  • 4
6 Comments
 
LVL 10

Author Comment

by:Tony Hungate
DrDamnit,

I actually used wiseGeek.com as the confirmation and source of my relational section.  They may have used or created the wikipedia content.  I will however add a "According to" into the two sentences that were quoted.

Thanks you for your input, and attention to detail.  I thought that I had referenced them as a source.  Wasn't sure how exactly EE wanted sources to be declared.
0
 
LVL 10

Author Comment

by:Tony Hungate
Thank you for your input, as usual you have brought to light some aspects that I hand not considered. You are correct about the focus of the articel, and I was kind of filling that way as I wrote it. I will lol at it again and see how it would best be broken up. Perhaps I will make a three parter, getting more and more spcesific has it goes.  You were spot on with it's intent. I plan on linking to it ia any solution where the user seems new to Access, databases, or VBA. I have mentioned these concepts to people so many times that I figured this would be a good way to address it in the future.

I will have look at breaking it up when I am back in the office. As for the naming conventios table, that was just to be an example of some of the names I had used in the past.  If you found it confusing, then I'm sure a reader will as well. I will remove it.

Thanks again.

TLH
0
 
LVL 10

Author Comment

by:Tony Hungate
harfang,

I have made some of the suggested changes, I did go ahead and cut the article into a couple of pieces.  Now I can spend a little more time on the very basic article describing design concepts and planning prior to implementation.  I have also included some of the info that you suggested, and went into a little more detail about some subjects.  It still felt like I could cut this up even more, but I don't want the reader to get to little information about some very related topics.

Thanks for your input.

TLH
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 10

Author Comment

by:Tony Hungate
Thanks again harfang.  I may message you for a little assistance with the part two, that have started working on.

TLH
0
 

Expert Comment

by:diakhate
I have a problem with my Form could you help!!!
I have two tables that are related by type and Number.
I would like to be able to see my subform populate every time my somebody enter on my main form the type and number I want the subform to show Type, Number, Description Weights Ect....
COuld you help
0
 

Expert Comment

by:Herbatron
The 2 links above the Events list did not work.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Join & Write a Comment

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month