1 / 57

VB for applications

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). Visual Basic in Access. Previous version (< 2002) Use macro to perform automation Use VB This version

ilyssa
Download Presentation

VB for applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related