Programming & VBA in MS Access — Form 3 Online Course
COMPUTER STUDIES — FORM 3 (CBE)
MODULE 2
Microsoft Access & the VBA Editor
Opening the door to writing and running real VBA code inside Access
Teaching & Learning Notes — Programming & VBA in MS Access
Prepared for online Form 3 CBE learners
Page 1
Programming & VBA in MS Access — Form 3 Online Course
Learning Outcomes
1. Recall the six main objects in a Microsoft Access database.
2. Open and navigate the Visual Basic Editor (VBE).
3. Explain the difference between a standard module and a form module.
4. Use MsgBox and InputBox to display and collect information.
5. Attach a VBA procedure to a command button's Click event.
1. Recap: What Is MS Access?
MS Access is a Relational Database Management System (RDBMS) included in Microsoft Office. It lets you store,
organise, retrieve, and report on structured data — student records, member registers, or stock records. Unlike
Excel, Access enforces data types, relationships, and rules, which keeps large amounts of data reliable and
consistent.
Key idea: Access becomes far more powerful once VBA is added — it can then think and react, not just store data.
The Six Main Access Objects
Object Purpose
Tables Store the raw data, organised into rows and
columns
Queries Ask questions of the data (filter, sort, calculate)
Forms Provide a friendly screen for entering and viewing
data
Reports Present data professionally for printing or sharing
Macros Automate simple actions without writing code
Modules Store VBA code — for logic that macros cannot
handle
This module focuses on the last one — Modules — and the code that lives inside Forms.
2. Opening and Touring the Visual Basic Editor (VBE)
• Open the VBE with the keyboard shortcut Alt + F11 from anywhere inside Access.
• It can also be reached through the Database Tools ribbon tab → Visual Basic.
• The VBE opens in its own separate window from the main Access interface.
Page 2
Programming & VBA in MS Access — Form 3 Online Course
Panel What It Shows
Project Explorer A tree of every form, report, and module in the database
Properties Window The settings of whatever object is currently selected
Code Window Where you actually type and edit VBA statements
Immediate Window A scratchpad for testing single lines of code instantly
(Ctrl+G)
Shortcut: Alt + F11 opens the VBE; Alt + F11 again returns you to the main Access window.
Standard Modules vs Form Modules
Standard Module Form (or Report) Module
Lives under "Modules" in Project Explorer Attached to one specific form
Holds general-purpose, reusable code Holds event procedures for that form's controls
Not tied to any one form or report Runs automatically when an event occurs (e.g. Click)
Most beginner VBA code — including everything in this module's hands-on activity — lives in a Form Module.
The Immediate Window
Open it with Ctrl + G while inside the VBE. Type a single line of code and press Enter to run it instantly — no
need to build a whole form first. For example, typing Print 3 + 4 and pressing Enter displays 7. This is extremely
useful for testing a formula or checking a value while learning.
Page 3
Programming & VBA in MS Access — Form 3 Online Course
3. Writing Your First VBA Statements
MsgBox — Displaying Information
Sub ShowWelcome()
MsgBox "Welcome to Maziwa Records System"
End Sub
• Sub ... End Sub marks the start and end of a procedure.
• MsgBox displays a pop-up message box to the user.
• Text shown to the user is always written inside double quotes.
• Press F5 while your cursor is inside the Sub to run it.
Element Example Effect
Buttons MsgBox "Save?", vbYesNo Shows Yes/No buttons instead of
just OK
Icon MsgBox "Error!", vbCritical Shows a warning/error icon
Title bar MsgBox "Done", vbOKOnly, Sets the pop-up window's title
"Status"
InputBox — Collecting Information
Sub GreetMember()
Dim memberName As String
memberName = InputBox("Enter your name:")
MsgBox "Welcome, " & memberName
End Sub
InputBox pops up a box for the user to type into, and the typed value is stored inside a variable. The & symbol
joins (concatenates) text together. Variables and data types are covered fully in Module 3.
4. Good Habits From the Start
Comments
A comment is a note in the code that VBA ignores when running — it is only for humans to read. Start a
comment with an apostrophe, e.g. ' this line calculates the balance. Good comments explain WHY the code does
something, not just what it does.
Option Explicit
Placing Option Explicit at the very top of a module forces every variable to be declared with Dim before use.
Without it, a simple misspelling of a variable name silently creates a brand-new variable — a very common
source of bugs. To make this automatic, go to Tools → Options → Editor tab in the VBE and tick "Require
Variable Declaration".
Page 4
Programming & VBA in MS Access — Form 3 Online Course
Naming Conventions
• Procedure names should be clear verbs: CalculateTotal, ValidateEntry, ShowReceipt.
• Variable names should describe their contents: studentMark, not just x.
• Avoid spaces in names — use CamelCase instead, e.g. totalFees.
5. Saving, Compiling, and Common Errors
Save the database regularly — VBA code is saved together with the Access file. Use Debug → Compile [Project
Name] in the VBE menu to check the whole project for syntax errors before you even try to run the code.
Message Likely Cause Fix
Compile error: Expected End Sub Missing End Sub / End If Add the missing closing
statement
Variable not defined Option Explicit is on; variable not Add a Dim statement for that
declared variable
Object required Referring to a control that Check the control's exact Name
doesn't exist / is misspelled property
Page 5
Programming & VBA in MS Access — Form 3 Online Course
6. Linking Code to a Command Button
6. Add a Command Button control to a form in Design View.
7. Open its Properties → Event tab → find On Click.
8. Choose "[Event Procedure]" and click the Build (...) button — Access creates the code shell for you.
9. Type your VBA code between the automatically generated Private Sub and End Sub lines.
Hands-On Activity: A Button That Shows a Welcome Message
Task: Add a button labelled "Say Hello" to a form that shows a welcome message when clicked.
10. Open the form in Design View.
11. From the ribbon, insert a Command Button and drop it onto the form.
12. Set its Caption property to "Say Hello".
13. Open its On Click event and choose [Event Procedure].
14. Type: MsgBox "Hello and welcome!" inside the procedure.
15. Switch to Form View and click the button to test it.
Module 2 Summary
• Access has six main objects — Tables, Queries, Forms, Reports, Macros, and Modules.
• The VBE (Alt+F11) is where VBA code is written, tested, and compiled.
• MsgBox displays information; InputBox collects information from the user.
• Option Explicit and clear naming conventions prevent many common bugs.
• VBA code is linked to a control through its event properties, e.g. On Click.
Assessment Questions
16. List the six main objects found in an Access database and give one purpose for each.
17. State the keyboard shortcut used to open the Visual Basic Editor.
18. Write a short procedure that uses InputBox to ask for a member's name, then displays a welcome message
using MsgBox.
19. Explain why Option Explicit is considered good programming practice.
20. Describe the steps needed to attach VBA code to a command button's Click event.
Answers Guide
1. Tables (store data), Queries (ask questions of data), Forms (data entry screens), Reports (printable output),
Macros (simple automation), Modules (VBA code).
2. Alt + F11.
Page 6
Programming & VBA in MS Access — Form 3 Online Course
Sub Greet()
Dim memberName As String
memberName = InputBox("Enter member's name:")
MsgBox "Welcome, " & memberName
End Sub
4. Option Explicit forces every variable to be declared before use, preventing bugs caused by misspelled variable
names silently creating new, unintended variables.
5. Add a command button in Design View, open its On Click event property, choose [Event Procedure], and type
the required VBA code between the generated Private Sub and End Sub lines.
Guidance for Online Self-Study
• Practise opening the VBE and the Immediate Window on your own copy of Access before attempting the
hands-on activity.
• Type out every code example yourself rather than only reading it — muscle memory matters for
programming.
• If Access is not available at home, most cyber cafés and school computer labs have it installed; plan your
practice sessions accordingly.
Page 7