ACCESS VBA BASIC

Course Overview

The Access VBA Training Course completes the necessary learning skills required to build any serious database. This course is suitable for students who confidently understand and utilise Access to an advanced level on a daily basis and wish to automate and customise complex calculations and processes in their current work flows.At the completion of this course the participant will be able to confidently navigate and utilise the VBA editor to run, edit and debug VBA code.

Participants will also have a solid foundation on the Access Object Model along with their associated properties, methods and events which are the building blocks of the VBA programming language.

Course Prerequisites

Microsoft Access Advanced Training Course 2010 or equivalent

Raising the Bar

1300 937 782

www.rtb.com.au

VBA Basics

The Programming Environment
Object Models
Navigate With Forms
StartUp Forms
VBA Editor Window
Project Explorer
Properties Window
Modules Basics
View Forms Code
Standard Code Modules
Naming Conventions

VBA Subroutines

Access Modules Explained
Procedures Explained
Module Breakdown
Subroutines
Functions
Call Procedures
Pass Parameters
Return Values
Scope Procedures

Variables

Variables Explained
Data Types Explained
Create/Use Variables
Implicit/Explicit Declarations
Numbers In VBA
Overflows
Decimals In VBA
Dates In VBA
Default Values
Scope Of Variables Explained
Procedure Level Scoping
Module Level Scoping
Pass Variables By Reference
Pass Variables By Value

Making Decisions

If Statement Explained
Single-Line If
Block If
Multiple Ifs
If Statements Assignment
If For Multiple Text Conditions
Select Case Statement
Simple Select Case Statement
Value Ranges In Select Case Statements
Select Case Statements With OR

User Interactions

Input Boxes Explained
InputBox Function
InputBox Function Arguments
Message Boxes Explained
Simple Message Box
Longer Messages
Break Message Lines
Buttons And Icons
Message Box Function
Different Buttons

The DoCmd Object

DoCmd Explained
Navigate With DoCmd
Move Through Records
Filter Records With DoCmd
Open/Close Form With DoCmd
Open Report With DoCmd
Create Variable Reports

Looping in VBA

Loops Explained
For Loops
While Loops
Counters
Until Loops
For…Each Loops
Exiting From Loops
For...Next Loops
Creating Loops Assignment

Debugging VBA

Debugging Explained
Set Breakpoints
Additional Breakpoints
Step Through Procedure
Call Stack
Locals And Immediate Windows
Add A Watch

Form Validation

Data Validation Summary
Validate Between Fields
Highlight Current Field
Highlight ALL Required Fields
Add to Combo Boxes using NotInList Event
Value Lists
Single Selection in ComboBox
Multiple Columns in ComboBox
Request a Change via Email

Control User Access Level

Retrieve User Roles
Allow… Properties
Disable/Enable Controls and Menu Options
OpenArgs
Tag Property
Form Timer Event

References

Set References
Available References
Other Libraries
Late/Early Binding
Binding with Outlook

External Reference - File Dialogs

FileDialog Object Members
Create File Dialog
Select Single File Only
File Type Filter
Select Multiple Files
Generic File Dialog module
Define File Types
Store returned file paths
Store Multiple Values with Array
Extend Projects

Files and Folders

MS Scripting Runtime Library
FileSystem Object
Folders
Create/Delete Folders
Check for Folder Contents
Consolidate Create/Delete/Review Routines
Copy/Move Folder
Next Steps
Files
Add Files
Write To Text File
Copy/Move/Delete Files

Audit Logging Routines

Capture User Login/Logout Details
User Login/LogOut Routines
Log Form/Report Interaction
Log Opening/Closing Time
Retain Full Data History
Additional Aspects

Error Handling

VBA Error Handling
Error Handling Logic
Standard Error Handling Routine
Logging Errors
Extend Error Handling
Keep User Informed of System State

Code Comments & Templates

Add Detailed Comments
Create Database Templates
Import Objects
Export Objects as Text Files

Access Developer Tools

Create Database Template

Access Recordsets

ADO
Connection Object
Command Object
RecordSet Object
Remove References to SET Variables
DAO
Create DAO Recordset
Using DAO Recordsets
Bound Forms & SubForms
Convert Bound To UnBound
Forms RecordSet
Navigation Elements
Add/Edit New Records
Delete Records
Complete Unbound Form
Add Error Trapping
Code Duplication