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”)