VB for applications

1. VB for applications

2. Lesson Plan • Fundamentals of VB • VB for handling events in Access

3. Project 1 (parts 1-2-3) • Avg = 143.3 (27 submissions)

4. 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

5. 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 …

6. 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

7. 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 code End Sub

8. 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)

9. 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)

10. 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

11. Variables and naming conventions • Implicit variables: IntegerVar% = 1234 • Explicit variables: Dim IntegerVar As Integer

12. 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

13. 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

14. Controlling program flow Select case statement: Select Case <variable name> case exp1 statement being executed if the value of variable name = exp1 case 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

15. Controlling program flow Case expression can be: single value or list of values range of values expression with relational operators Example: select case inputStr case “A” to “Z” char = “Upper Case” case “a” to “z” char =“Lower Case”

16. Repetitive commands For, Next For counter=startValue to stopValue Step stepValue ‘Code are here Next Example: Sum%=0 For i = 1 to 100 step 2 Sum%= Sum%+ i next i

17. Repetitive commands On Error GoTO Label GoTo label: creates a loop but breaks the structure of a program

18. 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

19. Adding a button to open a new form

20. Adding a button to open a new form

21. Adding a button to open a new form

22. Adding a button to open a new form

23. Adding a button to open a new form Private Sub Login_Click() On Error GoTo Err_OpenNewUserForm_Click Dim formName As String Dim 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

24. Adding a button to display a specific record on another form

25. Adding a button to display a specific record on another form

26. Adding a button to display a specific record on another form

27. Adding a button to display a specific record on another form

28. Adding a button to display a specific record on another form

29. Adding a button to display a specific record on another form

30. Adding a button to display a specific record on another form

31. Adding a button to display a specific record on another form

32. 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]=" & "'"& Me![userfirstname] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_DisplayUserInfo_Click: Exit Sub Err_DisplayUserInfo_Click: MsgBox Err.Description Resume Exit_DisplayUserInfo_Click End Sub

33. Adding a button to display a specific record on another form

34. Adding a button to display a specific record on another form

35. Adding a button to display a specific record on another form

36. 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, , , stLinkCriteria Exit_DisplayUserInfo_Click: Exit Sub Err_DisplayUserInfo_Click: MsgBox Err.Description Resume Exit_DisplayUserInfo_Click End Sub

37. Referring to Access Object with VBA • Form and form property: Example: Forms!UserInformation Forms!UserInformation.RecordSource Me.RecordSource Me.Userpassword

38. Validating data

39. Validating data Private Sub ISBN_BeforeUpdate(Cancel As Integer) If IsNull(Me!ISBN) Then MsgBox "This should not be empty" Exit Sub End 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 = True End Select End Sub

40. Practice Work on project 1 – part 5-6

41. 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

42. 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 modelthem)

43. Review for Exam • Week 3 Normalization Validating data (field level, table level) SQL statements

44. 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)

45. Review for midterm exam • Week 5 and week 6: Form and report

46. Review for midterm exam • Matching A-3 B-6 C-4 D-2 E-5 F-1

47. 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

48. 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. employeeNumber b. lastName c. firstName d. Age e. None of the above

49. 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’);

50. Review for midterm exam 5. Employee table: Empid: Primary key (PK) State: Foreign key (FK) State table: Stateid: primary key(PK) Relationship between state and employee: one to many State: parent table Employee: child table