Access VBA Course Outline. Summary of Microsoft Access Visual Basic for Applications training course content.

Learn Access VBA With The Smart Method.  Microsoft Access tutorials, Discussion Forums, Training Courses and Support info@learnaccessvba.com
Home Home  Site Map Site Map  Free Tutorials Free Tutorials Application
Development
application development Buy The Book Buy My Book Classroom
Courses
Classroom delivered courses Contact Contact Details
Course Outline for Access VBA Visual Basic for Applications Training. Training course by Microsoft Master Instructor
All training is not the same
  High quality 100-200 page course manual
  Buffet lunch
  Laminated quick-reference card
  Instruction by Mike Smart (A Certified Microsoft Master Instructor)
  Held at excellent air-conditioned training venues throughout the UK and the world
Access Training Course by Microsoft Master Instructor.
Information
About our courses
Terms & Conditions
Course FAQ
Course Level Duration Prerequisites Covers
Microsoft Access Logo Professional
Access VBA
Programmer
Can be run across
Two or Three
Days
Must have Expert Access skills Advanced Features
What is Access VBA

VBA stands for Visual Basic for Applications.  Visual Basic is, by far, the most widely used programming language in the world.  It has been estimated that Visual Basic programmers outnumber every other programmer added together!

VBA is no different from regular Visual Basic except that it can only be used to add features to Microsoft Office applications (such as Access, Excel, PowerPoint, Word, Project and Visio) rather than to write free-standing applications.  You don't have to buy Access VBA - it is included in the Access product (though you may not have noticed it was there).

Our course teaches you both the VBA language and the Access Object Model (the way in which your VBA code interacts with Access).

Who is this course for?
We call this our Professional Access VBA Programmer course because that's exactly what you will be at the end of the course.

The best Visual Basic programmer in the world couldn't do a thing with Access VBA unless they were also an expert in interactive Access.  That's because you use VBA to improve, extend, combine and automate existing Access features and without being an Access expert you wouldn't know how each feature worked. To get value from this course you'll need to already be an accomplished Access expert user (preferably by taking our Competent User and Expert courses).  We even offer a five-day complete developer course that, with no assumed existing Access skills, will take you from first principles to professional programmer level.

Much of what you learn in our VBA course will also be relevant to writing custom VBA extensions to other office applications too.

  • If you are an Expert-level Access user and still can't make Access do what you need then this course is definitely for you. 
  • If you want to develop robust, professional and polished Access applications for re-sale this course will show you how.
Lessons and Sessions
We structure our courses into sessions.  A session usually takes about an hour and a half and we have coffee/cigarette breaks between sessions.  Each session has a set of objectives that you'll achieve during the session (there's no doubt about this, the teaching method ensures success)! 
Course Outline
<% ' End of header... now onto the course outline %>

Session 1 - The Sample Database

Session Objectives:

  • Understand the sample database
  • Create simple and concatenated lookup fields
  • Create one-to-many table relationships
  • Set default and required values
  • Understand table design rules
  • Create a general purpose query to underpin forms and reports
  • Create a prototype form using a wizard
  • Optimise the form for keyboard input
  • Create a simple report using a wizard

During the VBA course you will build a professional (and useful) application.  The application manages a database of movies on DVD, VHS or other media.  This session empowers delegates to thoroughly understand the database underpinning the application (and its relationships that include one-to-many, many-to-many and a cascading delete).  Delegates are also given an appreciation of the various design rules that should be observed when constructing a robust normalised database while manually creating the foreign key/primary key relationships.  The database is then made even more robust by implementing field-level validations (later in the course VBA will be used to implement table-level validations).

Access schema used to show relationships and constraints in the sample database.

When the database is understood and constrained a complex query is constructed and a prototype form is wizard-generated and then further refined.

Wizard-generated form showing form controls prior to refinement

The form is then optimized for keyboard input and a simple report is added to provide a bare-bones application to work on during the following sessions.

Session 2 - Adding advanced features using wizards

Session Objectives

  • Add a switchboard using a wizard
  • Add a command button using a wizard
  • Add a combo box lookup feature using a wizard

In this session we are still "running before we have learned to crawl" by adding a Delete button, a combo box that allows fast navigation to individual records, and a switchboard by utilising the magic of the wizards.  In a later session the wizard-generated code will be understood and improved - but first we'll need to learn VBA!

Session 3 - Understanding the Object-Orientated Paradigm

Session Objectives

  • Understand object properties
  • Understand object methods
  • Understand object events
  • Understand some of the objects in the Access object model

Before you can understand how to use VBA you must first thoroughly understand the Object Orientated paradigm.  Fortunately it is very simple and your instructor will use some nifty animated slides to convey the concepts needed in a simple and intuitive way.

Properties and Methods of Access VBA Objects.  The Object-orientated nature of Access allows propeties, methods and events to be controlled programatically.
One of the above-mentioned "nifty slides!

By the end of this session you will be completely comfortable with the object-orientated paradigm and understand the concepts of methods, properties and events.  We begin by keeping within the analogy of a Car object that is easy to understand and then migrate your understanding to describe the Access object model schematic.

Microsoft Access VBA Object Model


Another slide showing how easy it is to understand an object model when put into the context of an everyday object that you already understand (a car)!

Session 4 - An Introduction to VBA

Session Objectives

Understand procedures and sub-procedures
  • Understand subsw
  • Step through code
  • Understand stepover, step into and step out
  • Understand variables
  • Use the immediate window to view and change variable contents
  • Use the locals window to view and change variable contents

Visual Basic is the most widely used language in the world for building business applications.

We used to teach the entire VBA language in one session but after years of running our VBA courses in both Excel and Access flavours, decided that this was too ambitious.

This session will get you started in VBA. The code you’ll write won’t be professional grade yet but you’ll be well versed in the basics and be able to write some elementary code. We’ll also introduce the debug tools in this session. It may seem odd to learn debug tools before you’ve even understood the language but we find them a very useful teaching tool to help you to understand more advanced language elements.

The session that follows this one (“Professional Grade VBA”) will build upon the basic skills learned in this session and add some vital techniques to make your code professional and robust. This will allow you to begin to do some really useful things with VBA.

Session 5 - Professional Grade VBA

Session Objectives

  • Understand data types
  • Understand and implement strong typing
  • Understand and implement explicit variable declaration
  • Understand arguments
  • Understand functions
  • Understand ByRef and ByVal argument types
  • Set and retrieve form control values from within VBA
  • Understand scope
  • Implement error handling
  • Use the help system and the object browser

In the previous session we covered the bare bones of the VBA language.

Unfortunately the code you have been writing so far is well below professional standards. It is, however, the type of code that many beginner and self-taught programmers might write.

So what’s wrong with the code? You’ll find out in this session when we migrate our low-grade code into something a professional would be proud of.

In order to write high quality code there are a few new concepts we need to take on board. You’ll learn it all in this session and will then be able to write robust bug-free error-resistant code that will be wonderful to work with.

You'll code simple test forms to demonstrate the skills you are learning such as this one :-
Microsoft Access form showing how to program event handlers for command button controls on an Access form.

You'll learn professional error-trapping so that your users never see Access errors such as this  :-

Microsoft Access standard error handler form, we show how to program custom error handlers for Access VBA subs, functions and event handlers.

... but instead see elegantly handled error messages such as this:-

The Microsoft Access VBA MsgBox showing more sophisticated custom error handling

Session 6 - Improving wizard-generated code

Session Objectives

  • Improve wizard-generated error handling
  • Understand the DoCmd object and some of its methods
  • Replace standard warnings and error messages with custom dialogs
  • Understand all wizard code previously generated
  • Modify and improve wizard code functionality
  • Correctly name controls

This session may make you revise your opinion of the Access wizards.  You'll discover a few rather serious shortcomings in the code generated back in session two and slowly convert the code into something a professional programmer would be proud of.  Along the way you'll have fleshed out your understanding of the Access object model, understood the indispensable DoCmd object and added some seriously cool features to your film management application.

Fleshing out the DoCmd object by modifying wizard code on generated forms

Session 7 - Speeding up data entry

Session Objectives:

  • Understand filtering events
  • Add an option group control
  • Set and remove filters using VBA code
  • Understand how to program the Enter and Exit events attached to form controls
  • Understand how to use the Keydown event to respond to keyboard input

It is often said that time is money. You can save an enormous amount of time for your data entry personnel if you optimise your forms for speed and empower your users to work smarter.

Effective database applications must enable users to quickly and intuitively find the record or records they are interested in. When dealing with small sets of test data you must always remember that the final application can potentially contain millions of records.

We get a fantastically useful and powerful filter feature for free when developing with Access. This is the built-in Filter By Form facility. In this session you’ll learn how to make the form more Filter By Form friendly.

You’ll also add an option group driven filter to allow the user to quickly filter by media type (DVD, VHS or All Media) with a single click.

Another way to speed things along is to provide interactive help so that the user is constantly aware of the type of information that should be entered into each field. You’ll implement this with a context-sensitive help box to provide help information about the field that currently has focus.

You’ll also provide the user with shortcut keys to automatically enter commonly required data, or sets of data, into the form with a single keystroke.

The Option Group control and use of shortcut keys to provide interactive help using Microsoft Access VBA code

Session 8 - Implementing a popup browse form

Session Objectives:

  • Use a listbox control to browse a dataset
  • Convert a form to a dialog box
  • Use the Recordset object's FindFirst method
  • Understand and use the Recordset’s Bookmark property
  • Understand and use the Recordset object's Clone method
  • Understand modal and modeless forms
  • Pass information between forms

Most database applications include a data store (the tables and data) and a collection of forms to maintain the data. Programmers call these forms Add/Edit/Delete or CRUD (Create, Read, Update, and Delete) forms.

As well as having a form to efficiently Add, Edit and Delete records we need to allow the user to quickly locate the record that needs to be Edited or Deleted.

The generic Access approach is to use a navigation bar (only useful for very small data sets) and the Filter By Form facility (useful for data sets of any size). This can work well but many commercial applications (whether or not they are written in Access) take a two-form approach. The two-form approach splits the Finding of the record and the Edit and Delete functions.

We’re not going to convert the main form of this application to the two-form paradigm. We will instead implement an Advanced Find form that will allow the user to select a record from a scrolling browse list of all records in the Film table. If you like this approach you shouldn’t have any difficulty in creating an application that uses the two-form paradigm as you’ll already understand all of the required techniques. techniques.

Microsoft Access popup browse form using listbox control and VBA code

Session 9 - Maintaining static data

Session Objectives:

  • Create self-updating bound combo boxes
  • Create and use a global constant
  • Create and use a globally visible utility function
  • Create a pop-up form to add static data with multiple fields
  • Use the Requery method to refresh a control’s dataset
  • Make SQL expressions “Apostrophe Safe”

One of the features that is most-requested by users is a simple way of updating Static Data. The term Static Data is used to refer to data that is not normally expected to change on a regular basis but, a method is needed to allow users to change static data when requirements change.

In our application a good example of Static Data would be the different film ratings. The British Board of Film Classification grades UK films into the following ratings:

Logos

Perhaps they will add other classifications in the future. This would result in the user having to edit the static data in the Rating table.

One approach would be to provide the user with a simple update form for the Rating, Media and Studio tables but, thanks to VBA, we can provide a much more efficient method by creating self-updating combo boxes that allow all static data to be maintained from the main form. In the case of the Director combo we can’t take this approach because the Director’s name isn’t a simple field but a concatenation of the Director's first and last name. In this case we code a pop-up form to allow the user to add new directors.

This session also introduces the concept of globally visible constants and globally visible functions and we make a start on a library of generic utility functions by solving the "apostrophe" problem.  This can be seen when wizard-generated code crashes when asked to search for values such as "Schindler's List".  Fortunately this shortcoming is very easy to fix with a simple VBA function.

Session 10 - Validations and sub-forms

Session Objectives:

  • Add an advisory validation using VBA code
  • Add a VBA table-level business rule to a form
  • Create a sub form with self updating combo box
  • Add a sub form to a form

It is easy to add simple validation to forms without using VBA. As business rules become more complex it may not be possible to implement the required logic without hand-crafting the validation rules in VBA. In this session we’re going to add some sophisticated VBA validation rules to our form.

Most Access developers will be familiar with adding a subform to show and update records on the many side of a one-to-many relationship but find themselves struggling when building a user interface to maintain data within a many-to-many relationship. We have such a relationship in our database.

Modeling a many-to-many relationship using Microsoft Access with constrained relationships between primary key and foreign key pairs

One Film has many Actors (each having a Role) and one Actor may appear in many different Films.

Our users want to be able to quickly associate an Actor with a Film (stating the Actor’s Role in the film) and also want to be able to quickly add new actors to the Actor table.  In this session you'll code an elegant and efficient solution.

Microsoft Access many-to-many relationship modeled with subform and self-updating combo box controls

Session 11 - Creating a dialogue-driven report

Session Objectives:

  • Create and format a report
  • Use a tab control
  • Use a dialogue box to gather and set report parameters

For many of our delegates their need to master VBA derives entirely from reporting requirements.  A single report with a sophisticated dialogue to set report parameters can often replace dozens of special-purpose reports and be a delight for users.

This session gives you all that you need to develop dialogue-driven reports (impossible without hand-crafted VBA code).

Access dialog driven report using custom form and VBA code

Session 12 - Using Word with Access

Session Objectives:

  • Create a Word Document Template
  • Create a command button that will open Word
  • Transfer data from Access to Word

Access is a wonderful tool for storing and retrieving data. But Access is only good at what it is designed to do.

Word is also very good at what it is designed to do: Formatting and printing text and graphics to produce perfectly printed output.

Excel is the star of the desktop when it comes to analysing the data that Access is so good at storing and retrieving.

One of the most wonderful, and rarely exploited, features of the Office suite is the use of VBA code to mix and match features from all Office applications in order to produce custom-built applications that address specific needs. We’ll do just that in this session when we leverage upon Word’s superb page layout capabilities to output film records as Word documents.

We often go off at a tangent in this session and data-drive Excel from Access if the group includes Excel experts.  For example, you can leverage upon Excel's fantastic graphing capabilities that are greatly superior to Access.  Needless to say, you need to have expert-level Excel skills in order to data-drive it from Access using VBA.

how to integrate word and Access to program Word bookmarks using VBA code

 

A click of the button and the current record is neatly formatted into a Word document!

A word document generated from Microsoft Access using VBA and bookmarks

Session 13 - Adding Menus and Toolbars

Session Objectives:

  • Manually create a switchboard form
  • Create a global custom menu
  • Create a custom toolbar
  • Associate menus and toolbars with specific actions

While switchboards are fine if you need to get an application up and running as quickly as possible your users might think them a little old-fashioned and clunky.

Today’s computer users are used to sophisticated applications such as the Microsoft Office suite and expect applications to have the same familiar system of drop-down menus and toolbars.

In this session you’re going to begin by manually creating a Switchboard form. You’ll then provide a better replacement for the Switchboard approach by defining your own custom menus and toolbars to create a truly professionally user interface that (after a little further polishing) will feel greatly superior to a traditional Access application.

Access custom menu showing main and submenus

Acess custom toolbar using VBA code

Session 14 - Finalising the application for deployment

Session Objectives:

  • QA an application prior to deployment
  • Create user and development versions of an application
  • Set an application's start-up options

This session gives you the skills needed to package your application for distribution.  The final application gives away few clues that it was even written in Access and hides all Access features from the user to ensure that users do not inadvertently damage the application.

Access applications can be distributed royalty-free.  The users do not even have to own Access licences or have it loaded on their computers.

An example of a professional Microsoft Access application written using Visual Basic for Applications

There is a lot more covered in the actual course than in the above summary but it does give you a good idea of the breadth of VBA skills you'll have after completing it. 

While we can run this course across two days we can transfer a lot more skills if you have three days to spare.  As with all of our courses it is always possible to deviate from the course outline in any way you need if you have specific requirements.

Where you will be at the end of the course

The Smart Method's Access VBA course is quite remarkable. The ambitious remit: to teach both Visual Basic and the Access Object model in just two days, would be unrealistic and unachievable using other less advanced teaching methods.

At the end of the course you'll be quite within your rights to describe yourself as a Professional Access VBA ProgrammerYou'll be in the tiny elite of Access power users that are able to write hand-crafted VBA code to create polished and professional Access applications.

Our Access VBA course is available throughout the UK, Europe and the World.

If you have any other questions about this course feel free to Email or Telephone at any time.

This course is unique to The Smart Method
This course was developed entirely in-house and isn't available from any other training provider.  While most of our training is delivered in the UK It is so difficult to obtain quality instruction in Access VBA that we've travelled the world delivering this course.  Recently we were flown to Washington DC to deliver a VBA course for the US Government.

After much pressure from attendees of our Access VBA course we published this courseware as a book that you can buy from Amazon  Buy it from Amazon.co.uk Buy it from Amazon.com. We were amazed when this book became Amazon's top selling Access VBA book.   Some free sessions from this course (and the book) are also available on this site.