vb for applications. lesson plan fundamentals of vb vb for handling events in access

VB for applications

Lesson Plan

• Fundamentals of VB

• VB for handling events in Access

Project 1 (parts 1-2-3)

• Avg = 143.3 (27 submissions)Projects 1 (1-2-3)

0

20

40

60

80

100

120

140

160

0 10 20 30 40

Series1

Visual Basic in Access

• Previous version (< 2002)– Use macro to perform automation – Use VB

• This version- Very little support for macro (only for backward

compatibility)

- Focus on VB

Visual Basic in Access• VB is a real programming language and is NOT a

macro language• We focus on using VB for handling user-initiated

events (keyboard press, mouse lick..)• Where do we use Visual Basic for Applications

code?– Docmd command– Write query expression– To create user-defined functions– Even handling, error handling …

Modules

• Form modules:– Contain code in response to event-

triggered by a user using controls of a form

• Report modules– Contain code in response t event-trigged by

reports, sections of reports

• Access modules, and Class modules– Allows you to define custom objects

Modules

• One or more procedures (sub-procedures)

Example:Private Sub Add_Click()On Error GoTo Err_Add_Click ‘Start procedure code

DoCmd.GoToRecord , , acNewRec

Exit_Add_Click: ……. ‘End procedure codeEnd Sub

Procedures

• Procedures

Definition:

private/public SUB <procedure name>

‘ code right here

end SUB

Similar to void-returned method in Java/C++ (or void returned functions in C)

Functions

Functions:

private/public FUNCTION <procedure name>([Parameter as Datatype]) As ReturnType

‘ code right here

end Function

Similar to non-void-returned method in Java/C++ (or non-void returned functions in C)

Datatype• Symbol value: type-declaration character• Byte: [0,255]Symbol value: none• Integer: [-231, 231-1]Symbol value: %• Boolean: true, false. Symbol value: none• Long: [-2,147,483,648; 2,147,483,647]Symbol value: &• Double: Symbol value: #• Currency: symbol value @• String: symbol value $• Date: symbol value none

Variables and naming conventions

• Implicit variables:IntegerVar% = 1234

• Explicit variables:Dim IntegerVar As Integer

Variables

• Arrays: are variables that consists of a collection of values

Dim newArray(20) as String ‘create an array with 21 elements indexed from 0 to 20

Dim newArray(1 to 20) as String‘create an array with 20 elements indexed from 1 to 20

Controlling program flow

• Conditional statements:If boolean_expression1 then

statement to be executed if boolean_expression1 is true

Else

statement to be executed if boolean_expression1 is false

Endif

Controlling program flow

Select case statement:Select Case <variable name>case exp1

statement being executed if the value of variable name = exp1case exp2

statement being executed if the value of variable name = exp2

…case else:statement being executed if none of the above is met

End Select

Controlling program flow

Case expression can be:single value or list of valuesrange of valuesexpression with relational operators

Example:select case inputStrcase “A” to “Z”

char = “Upper Case”case “a” to “z”

char =“Lower Case”

Repetitive commandsFor, NextFor counter=startValue to stopValue Step stepValue ‘Code are here

Next

Example:

Sum%=0For i = 1 to 100 step 2

Sum%= Sum%+ inext i

Repetitive commands

On Error GoTO Label

GoTo label: creates a loop but breaks the structure of a program

VBA for event handling in Access Form- An event: mouse click, mouse movement, keyboard press- VBA code is used to process the actions associated with

such an event. - Event procedure from properties for any controls in a form

Adding a button to open a new form

Adding a button to open a new form

Adding a button to open a new form

Adding a button to open a new form

Adding a button to open a new form

Private Sub Login_Click()On Error GoTo Err_OpenNewUserForm_Click

Dim formName As StringDim formCriteria As String

formName = "NewUser"DoCmd.OpenForm formName, , , formCriteria

Exit_Err_OpenNewUserForm_Click: Exit Sub

Err_OpenNewUserForm_Click: MsgBox Err.Description Resume Exit_Err_OpenNewUserForm_Click

End Sub

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another formPrivate Sub DisplayUserInfo_Click()On Error GoTo Err_DisplayUserInfo_Click

Dim stDocName As String Dim stLinkCriteria As String stDocName = "UserInformation"

stLinkCriteria = "[FirstName]=" & "'" & Me![userfirstname] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_DisplayUserInfo_Click: Exit SubErr_DisplayUserInfo_Click: MsgBox Err.Description Resume Exit_DisplayUserInfo_Click End Sub

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Adding a button to display a specific record on another form

Private Sub DisplayUserInfo_Click()On Error GoTo Err_DisplayUserInfo_Click

Dim stDocName As String Dim stLinkCriteria As String stDocName = "UserInformation" stLinkCriteria = "[FirstName] LIKE " & "'" & Me![userfirstname] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_DisplayUserInfo_Click: Exit SubErr_DisplayUserInfo_Click: MsgBox Err.Description Resume Exit_DisplayUserInfo_Click End Sub

Referring to Access Object with VBA• Form and form property:

Example: Forms!UserInformation

Forms!UserInformation.RecordSource

Me.RecordSource

Me.Userpassword

Validating data

Validating dataPrivate Sub ISBN_BeforeUpdate(Cancel As Integer)

If IsNull(Me!ISBN) Then MsgBox "This should not be empty" Exit SubEnd If firstChar$ = Left(Me!ISBN, 1)

Select Case firstChar$

Case 0 To 9 Exit Sub

Case Else MsgBox “ISBN should start with a number" Cancel = TrueEnd Select

End Sub

PracticeWork on project 1 – part 5-6

Review for Exam

• Week 1:

Three separate types of functionality:– Data Management– Application logic– Presentation

Single-tier, client-server, three-tier architecture

How Do Databases Support the World Wide Web

Review for Exam• Week 2

Relational model: table, key(PR,FK), row, column, domain,tuple/record, NULL value

Data integrity, entity integrity, Referential Integrity, enterprise integrity

Views, relationship (three types, how to model them)

Review for Exam

• Week 3NormalizationValidating data (field level, table level)SQL statements

Review for midterm exam

• Week 4:

Query: boolean expressions, different types of queries (select,action, parameter, crosstab)

Different types of joins (equ(inner)joins, left/right joins)

Review for midterm exam

• Week 5 and week 6:

Form and report

Review for midterm exam

1. MatchingA-3B-6C-4D-2E-5F-1

Review for midterm exam

2. Query operations on a database are very important. Which operation is not a query operation?

a. Insert

b. Update

c. Select

d. Model

Review for midterm exam

3. Consider a database with a logical description Employee (employeeNumber, lastName, firstName, age). Select the entry that would most likely be require to be unique

a. employeeNumberb. lastNamec. firstNamed. Agee. None of the above

Review for midterm exam

4. Insert into video (videoId, movieName,typeDVDVHS)

values (102, ‘Citizen Kane’,’VHS’);

Insert into video (videoId, movieName,typeDVDVHS)

values(103, ‘Chicago’,’DVD’);

Review for midterm exam5. Employee table:Empid: Primary key (PK)State: Foreign key (FK)

State table:Stateid: primary key(PK)

Relationship between state and employee: one to manyState: parent tableEmployee: child table

Review for midterm exam

5.2.

SELECT Employee.Empid, Employee.Salary,Employee.Startdate, State.abbr

FROM State INNER JOIN Employee ON State.Stateid=Employee.State

WHERE State.abbr='WI';

Review for midterm exam

5.2.

SELECT Employee.Empid, Employee.Salary, State.abbr

FROM State INNER JOIN Employee ON State.Stateid=Employee.State

WHERE Employee.StartDate < #1/1/2004#;

Review for midterm exam5.4.SELECT STATE, COUNT(EMPID) as TotalEmployee,

SUM(SALARY) as TotalSalaryFROM EMPLOYEEGROUP BY STATESTATE TotalEmployee TotalSalary1 2 750002 2 1100003 1 450004 1 34000

Review for midterm exam

Result

EMPID SALARY ABBR

00002 40000 CT

00005 35000 CT

00001 50000 NY

00003 60000 NY

00004 45000 NV

00006 34000 WI

SELECT EMPLOYEE.EMPID, EMPLOYEE.SALARY, STATE.ABBRFROM EMPLOYEE, STATEWHERE EMPLOYEE.STATE = STATE.STATEID

Review for midterm examResult

EMPID ABBR

00002 CT

00005 CT

00001 NY

00003 NY

00004 NV

00006 WI

MD

SELECT EMPLOYEE.EMPID, STATE.ABBRFROM STATE LEFT JOIN EMPLOYEEON EMPLOYEE.STATE = STATE.STATEID

Review for midterm exam

Result

TotalSalary

179000

SELECT SUM(SALARY) FROM EMPLOYEE WHERE STARTDATE > #1/1/2004#

Review for midterm exam

Result

SALARY STARTDATE

40000 1/8/2004

35000 11/20/2003

SELECT SALARY, STARTDATE FROM EMPLOYEEWHERE STATE = (SELECT STATEID FROM STATE WHERE

ABBR=”CT”)