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 and manually create all foreign key/primary key relationships and field validations.

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

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 but relying upon 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.

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 an analogy of a Car object that is easy to understand and then migrate your understanding to describe the Access object model schematic.

Session 4 - An Introduction to VBA

Session Objectives

• Step through code
• Understand 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 you’ll find them very useful as you learn 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 arguments
• Understand functions
• 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 :-


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



... but instead see elegant error messages such as this:-

Session 6 - Improving wizard-generated code

Session Objectives

• Understand the DoCmd object and some of its methods
• Understand all code previously generated
• Modify and improve wizard code functionality
• Improve wizard code error handling
• 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.

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.

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’s FindFirst method
• Understand and use the recordset’s Bookmark property
• Understand modal and modeless forms
• Pass information between forms
  

Most database applications include of 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.

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:

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 it is very easy to fix with a simple 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

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.

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.

Session 11 - Creating a dialogue-driven report

Session Objectives:

• 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).

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.

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

Session 13 - Adding Menus and Toolbars

Session Objectives:

• Create a global custom menu
• Create a custom toolbar
• Associate menus and toolbars with specific forms

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.

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.

While this ends the structured course's outline there is a lot more covered in the actual course. 

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

Feel free to telephone or Email at any time if there's anything else you need to know.