Microsoft Access VBA

Session Topics

​

 

The VBA Code Editor
This session concentrates on the VBA code editor that is contained within MS Access. Once you are comfortable with accessing the code editor it moves on to show you how to create the modules that you will use to hold your code. It then discusses the project explorer which you will work in when writing your VBA code, and also to look at sub-routines and commenting your VBA code.

 

The key concepts trained are:

• Two ways to create modules

• Using Project Explorer

• Basic subroutines and comments

 

Messages & Variables
This session focuses on using message boxes to communicate with and get input from users and also on the basics of variables.

 

The key concepts trained in this session are:

• The DoCmd object

• Displaying messages

• Getting input from users

• Using variables

 

Creating Functions
Functions are one of the key elements of a VBA 
program. Functions take input data, carry out an operation on that data and then return a value when they complete. A simple example of a function would be a calculator to work out the area of a rectangle. The function would require as inputs the two 
different lengths of the sides. It would multiply them together and return the area of the rectangle. The input data into a VBA function are known as arguments which are passed through a function. 

 

The key concepts trained in this session are:

• Passing arguments

• Returning values

​

Visual Basic Explained
Now that you have been introduced to the nuts and bolts of VBA code this session steps back a little bit. It explains at a high level how VBA programs are organised and manipulated. This session introduces delegates to the object model in Access and also to collections of objects. It also introduces methods and property as well as other parts of the VBA framework.

​

The key concepts trained in this session are:

• Objects and collections

• Methods and properties

• The Access object model

• Getting help in VBA

 

Attaching Code To Forms
Once you are comfortable writing VBA code the next step is to understand how to connect it to forms. This is the most user friendly way to access the power of VBA on a day-to-day basis. It allows non-programmers to access your code and also for you to run your code without opening the code editor.

​

The key concepts trained in this session are:

• Attaching code to buttons

• Attaching code to events

• Making code not form-specific

• Manipulating Form/Control Properties

 

Looping
Often within VBA code you want as certain task to be repeated many times. This is known as looping. This session covers looping in detail including it’s application to forms and controls.

 

The key concepts trained in this session are:

• The four types of loop

• Looping over all open forms

• Looping over controls

• Looping over Access Objects

 

Modifying Queries
SQL (Structured Query Language) is the core database querying language. This session introduces SQL and then shows delegates how to amend the SQL of an Access query. This gives you complete control over queries in Access.

The key concepted trained in this session are:

• Basic SQL statements

• Amending queries’ SQL

 

Recordsets
Recordsets, along with SQL, are the most common way for VBA developers to interact with a database. A recordset is simply just a collection of records. They may either have come from a table, a number of tables or more often be the result of a query.
This session leads you through using recordsets to interact with your database. It starts by running through the differences between the major ways of using recordsets, ActiveX Data Objects (ADO) and Data Access Objects (DAO) before looking at the practicalities of adding, deleting and editing data.

​

The key concepted trained are:

• Choosing ADO or DAO

• Adding data

• Editing data

• Deleting data 

 

Debugging & Error Handling
Inevitably when you write VBA code in Access you will make errors. Learning to debug code is a key skill. The VBA code editor contains a number of tools to help you identify the pieces of your code that are  causing the issues. This session shows you the various methods that are available to you to help you isolate your problematic pieces of code.

​

The key concepts trained in this session are:

• Stepping through macros

• Setting breakpoints

• Trapping for errors

 

Linking to Excel & Word
The final session of this course covers working with Excel and Word. Inevitably as your programming skills develop you will want your Access code to interact with other Microsoft Office programs. Most like Excel and Word. This session covers how you can use Access to access other applications and how you can manipulate Excel from Access using your VBA code.

 

The key concepts trained are:

• Referencing other applications

• Manipulating Excel from Access