SESSION 16 VBA example

1
SESSION 16VBA example
2
Exercise 10.1 You are going to use VBA to create
a simple calculator to multiply 2 numbers and
display the answer.
3
Outcomes

• To further understand examples of VBA code in
  Microsoft Access

4
Setting up the objects

• Open a new database and call it vba.mdb
• Create a new form in design view
• Add a label with a caption Easy Calculator
• Add 2 textboxes, one called txtFirst and the
  second txtSecond, with captions of First number,
  Second number respectively.

5
Add a button

• Add a command button called cmdCalculate
• Add a text box called txtDisplay
• Open the form in code view
• Select cmdCalculate and onclick

6

• Assign 3 variables, firstNo ,secondNo and Answer
  as single
• Set the focus to the txtFirst
• Assign firstNo to the txtFirst.text
• firstNoval(txtFirst.text)
• (val changes text input to a number)

7

• Repeat for txtSecond
• Write code for the answer
• Assign Answer to txtDisplay.text
• Test your code by opening the object view the
  form view

8
The number calculator form view
9

• To enter VBA code go to View Code. The 2 drop
  down boxes at the top contain the objects (LHS)
  and their associated events (RHS)

10
The code view
object
event
11
Code for number exercise

• Option Explicit
• Private Sub cmdCalculate_click()
• Dim FirstNo As Single
• Dim SecondNo As Single
• Dim Answer As Single
• txtFirst.SetFocus
• FirstNo Val(txtFirst.Text)

This forces the program to check for
inconsistencies
This is the code for a procedure (set of
functional statements) to perform a calculation
when a button is pressed.
In Access VBA you need to set the focus on the
object
12

• 'decision construct to check that a positive no
  is entered
• If FirstNo lt 0 Then
• txtFirst.Text ""
• MsgBox "You must enter a positive no"
• txtFirst.SetFocus
• End If
• FirstNo Val(txtFirst.Text)
• End Sub

13

• Code for the above Exercise this must be typed
  into the design view of the NumberCalculator form
  (view code).
• --------------------------------------------------
  ----------------------------
• Option Compare Database
• Private Sub cmdCalculate_click()
• Dim FirstNo As Single
• Dim SecondNo As Single
• Dim Answer As Single
• txtFirst.SetFocus
• This appears by default when you open the code
  screen it forms part of the General
  Declarations area.
• FirstNo Val(txtFirst.Text)
• 'decision construct to check that a positive no
  is entered
• If FirstNo lt 0 Then
• txtFirst.Text ""
• MsgBox "You must enter a positive no"
• txtFirst.SetFocus
• End If

14
Code continued

• FirstNo Val(txtFirst.Text)
• txtSecond.SetFocus
• SecondNo Val(txtSecond.Text)
• If SecondNo lt 0 Then
• txtSecond.Text ""
• MsgBox "You must enter a positive no"
• txtSecond.SetFocus
• End If
• Answer FirstNo SecondNo
• txtOutput.SetFocus
• txtOutput Answer
• End Sub