Programming in Microsoft Access using VBA

1
Programming in Microsoft Access using VBA

• Using VBA to add functionality

2
Introduction

• Writing Visual Basic for Applications (VBA) code
  helps to automate your database and makes your
  database more powerful. The following notes
  assume that you have a basic knowledge (gained
  from the three open learning Access books) of
  creating simple databases with related tables,
  queries and forms.

3
Outcomes

• At the end of this session you will understand
• The role of objects in the Access GUI
• Event-driven programming
• Introductory programming concepts
• How to use VBA to add functionality to a form

4
Objects

• Physical control objects, like textboxes and
  command buttons are physical entities that have
  properties and events associated with them. In
  VBA programming you have to ensure that the focus
  of the programming sequence is on the object you
  wish to manipulate. The SetFocus function does
  this and is assigned to the object (in this case
  a textbox) in the following way
• TextBox1.SetFocus

5
Event-driven programming

• Unlike procedural programming languages where the
  code is read and executed from top to bottom,
  apart from loops and other constructs, Access VBA
  is an event-driven language.
• This means that the events are executed when the
  user interacts with the Graphical User Interface
  (GUI)
• For example, a message appears when the user
  clicks on a button

6
Introduction to Programming

• Programming allows you to
• Issue commands to make the system do something
• Control how and when these commands are issued
• In general, each line in a program consists of a
  single command or control

7
Programming conceptsVariables

• A variable holds a value in memory for temporary
  storage for the time that the block of code is
  being executed.
• For example you could assign a variable with the
  name Surname, data type String
• This stores the value of a surname that can be
  input into a text box for example.
• The physical object that is used to manipulate
  the variable in this case is the textbox.

8
Objects

• Physical control objects, like textboxes and
  command buttons are physical entities that have
  properties and events associated with them. In
  VBA programming you have to ensure that the focus
  of the programming sequence is on the object you
  wish to manipulate. The SetFocus function does
  this and is assigned to the object (in this case
  a textbox) in the following way
• TextBox1.SetFocus

9
Declaring variables

• Any variables created in VBA programming must be
  declared as follows
• Dim Surname as String
• Dim Num as Single
• Dim BirthDate as Date

Variable
Data type
10
VBA statements and procedures

• When you combine constants, variables, objects,
  operator symbols, properties and methods in a
  single operation, for a set purpose, you produce
  a statement. When you VBA programme in VBA, you
  put together the statements in a particular
  order, following specific syntax rules, into a
  procedure.

11
Function procedure

• A function procedure is a particular form of
  procedure that performs a task that may return a
  value. This returned value is computed in the
  procedure and assigned to the function name as
  one of the procedures statements. There are
  several useful in-built functions such as MsgBox
  function, InputBox function

12
Variables to store data

• When you want to write a procedure or function
  you will need to declare any variables to hold
  temporary data.
• For example if you want to write a procedure to
  multiply 2 numbers that will be input by the user
  then you will first need to put two input boxes
  on a form, a command button to trigger the event
  and a text box for display.

13
Control structures

• VBA executes a procedures statements in sequence
  beginning with the first statement.
• It reads the code left to right and then top to
  bottom-this is called sequential flow.
• Control constructs influence the execution of a
  program so that if you want one set of statements
  to be executed rather than another i.e you want
  to change the order then you can use constructs
  to do this.
• You can use decision structures to test
  conditions and then perform a particular
  statement or set of statements depending on the
  outcome. Or you can use loop structures to
  execute a set of structures repetitively.

14
Making decisions

• If ..then
• The words in italics are the condition , words in
  bold are the construct commands, words in normal
  text are the statement
• If you are feeling too hot then
• Take some of your clothes off
• End if
• (The above algorithm is a form of pseudocode-like
  a recipe to do something in plain English and not
  in a programming language

15
Programming an activity
16
Simple interaction with the user

• Open a new form and put a command button on the
  form and name it cmdAnswer, a label with the
  caption The best football team!, and a textbox
  called txtTeam with the label Which is the best
  team?.
• In design view open the code dialog box and type
  the following in the section for the object
  cmdAnswer and event

17
Writing the code
Note the object box
And this is the event box
So when the button is pressed (object button
triggers click event) the code is executed and a
message box appears.
18
Summary

• Access is an even-driven programme
• When you interact with the interface objects,
  events happen
• You can add programming constructs using VBA
• The tutorial is Exercise 10.
• The tutorial covers sessions 15 and 16.