CS130/230

Visual Basic for Applications

VBA is an event-driven programming language that can be used with Access.

Programs in visual basic consist of code, which is a collection of statements (commands), which are instructions that will cause actions to take place when the program is executed.

When working with Access, visual basic deals with events. Events are associated with user actions such as Click, DblClick, MouseDown, …

Variables: 255 characters beginning with a letter including letters, numbers, underscore (no spaces).

Assignment Statement: Factor = 1.05

Simple IF

If CustomerType = “EDU” Then

Factor = 1.05

End If

Note: The If statement can also have an Else and ElseIf component.

Comments: these are notes to yourself that will be ignored by visual basic. You indicate a comment by placing an apostrophe just before the comment. Everything to the right of the apostrophe will now be a comment.

Functions : these are a group of statements that calculate and return a value.

Function Factor (CustomerType)

‘ Determine factor base on Customer Type

If CustomerType = “EDU” Then

Factor = 1.05

Else

Factor = 1

End If

End Function

Subroutines: these are a group of statements that do not return a value.

Public Sub ShowPromotion ()

txtPromoAmount.Visible = True

txtPromoFactor.Visible = True

cmdPromoQuery.Visible = True

End Sub

Module: a group of functions and/or subroutines

- Standard Module – procedures available anywhere in the DB

- Class Module – procedures available in a particular form or report

This was just a quick look at the basics in visual basic. The best way to really get a hang of it though is to use it directly.

Problem

We will use VB to modify the Add Record button so that as soon as we click on it, it will place an insertion point in the customer number box.

Question: What currently happens when we click on the Add Record button?

Go back to the form, and in the design view, click on the Add Record button and then select Build Event by doing a right click.

VB opens up the code and goes directly to the subroutine for the text box. After the line DoCmd.GoToRecord , , acNewRec add the following Visual Basic line Customer_Number.setFocus

Close VB and test out the form.

Another Example

Let us move onto another database example.

Basically, every action we perform at the computer is an event that is sent to the Operating System. Events can be typing, mouse clicks, mouse moves, …

Form events in Access include events such as:

- Open Event

- Load Event

- Resize Event

- UnLoad Event

- Close Event

Event Actions

Private Sub Form_Load()

MsgBox “Form Is Loaded"

End Sub

Variable Declarations

In general, variables are declared using Dim

Private Sub NameIt()

Dim Name As String

Dim Value As Integer

Dim Found As Boolean

Dim Total As Single

End Sub

Other Possible Data Types

- Byte

- Long

- Double

- Currency

- Date

- Object

- Variant

Operators

- ‘ Comment ‘ This is a comment

- = Assignment txtName = txtFirstName

- “ String “hello there”

- & Concatenate “hi” & “there”

Problem

Copy the database VBAccess1.mdb in the CS130 Public directory to your desktop and do the following:

For each button, write the code that will calculate and display the Perimeter and Area for the Square.

Solution

Private Sub cmdSqCalculate_Click()
 txtSqPerimeter = txtSqSide * 4
 txtSqArea = txtSqSide * txtSqSide
End Sub

Complete the rectangle and circle code in a similar manner as that of the Square.

Problem

Insert another page into the VBAccess1.mdb database called Compute Wage. Allow the user the ability to input Hours Worked and Hourly Wage. Then call a subroutine that places the amount of money earned into a textbox on the screen. Add a calculate button that when pressed calls your subroutine and displays the proper value in the text area.

Here is a picture of how your program is to interact:

You will need to use an IF Then Statement

Looping

The general form for looping (doing something repeatedly) is:

Do While Condition

Statements

Loop

More Looping

Statements

Loop While Condition

Do Until Condition

Statements

Loop

Yet More Looping

For Counter = Start To End

Statements

For Each Element In Group

Statements

Next Element

Example

Sum = 0

For Counter = 1 To 10

Sum = Sum + Counter

Problem

Add another page called SumIt that allows the user the ability to enter a starting value and an ending value. Calculate and place the sum and average of the numbers, from the starting value to the ending value, in two separate text box areas in the form.