Access 2007 Tutorial 11 Using and Writing Visual Basic Applications Code

1. Access 2007Tutorial 11Using and Writing Visual Basic Applications Code (Access Developer)

2. Introduction to Visual Basic for Applications • Visual Basic for Applications (VBA) is the programming language provided with Access and other Office programs • VBA has a common syntax and a set of common features for all Microsoft Office programs • Event-driven language • Object-oriented language

3. Events • Event: a state, condition, or action that Access recognizes • Each event has an associated event property that specifies how an object responds when an event occurs

4. Procedures • Function procedure,or function: performs operations, returns a value, accepts input values, and can be used in expressions (recall that an expression is a calculation resulting in a single value) • Sub procedure, or subroutine: performs operations and accepts input values, but does not return a value and cannot be used in expressions

5. Modules • Module: an object where groups of related procedures are stored

6. Modules • Declarations section • Each module starts with the Declarations section, which contains statements that apply to ALL procedures in the module • Standard module • A database object that is stored in memory with other database objects (queries, forms, etc.) • Public procedure • A procedure that more than one object can use • Class module • Associated with a particular form or report • Local procedure / Private procedure • Each event procedure in a class module is a local procedure or private procedure which means only the form or report for which the module was created can use the event procedure

7. Create a Procedure • We have an unbound TextBox (txtTeam) control to the right of the Zip control • Based upon the value of the City field on the form • A value and color will be assigned to the unbound textbox (txtTeam) • Or the txtTeam textbox will be made invisible • The form’s OnCurrent event will be used to trigger the task of updating the txtTeam control

8. Create a Procedure Open the properties for the form Find the On Current Event (The Current event occurs when a form opens and when the focus moves to another record) Click the ellipsis… after the [Event Procedure] to open the VBA Code window Enter the code necessary to achieve the desired outcome

9. Create a Procedure • [txtTeam].Visible = True • Assignment Statement: assigns the value of an expression (True) to a field, control or property ([txtTeam] control’s Visible property) • [txtTeam] = “Christopher” (“Christopher assigned to [txtTeam]) • [txtTeam] = “Cynthia” (“Cynthia assigned to [txtTeam]) • [txtTeam].Visible = False (False assigned to [txtTeam].Visible) • [txtTeam].Forecolor = vbRed (vbRed assigned to [txtTeam] Forecolor) • Assignment statements will always take the value on the right side of the = sign and store (assign) it to the field, control or property identified on the left or the = sign

10. Create a Procedure Select Case [City] Case control structure: a conditional control structure that evaluates an expression ([City] field value in this example) and then performs one of several alternative actions based on comparing the resulting value to each Case statement included within the structure Case “Grand Rapids” Case statement furnishes the value to compare with the Select Case statement. If “Grand Rapids” matches the [City] field value then the code below the Case statement is executed to the next Case statement or the End Select statement

11. Create a Procedure Case “Holland” If “Holland” matches the [City] field value then the code below the Case statement is executed to the next Case statement or the End Select statement Case Else Case Else statement provides an alternative when All Case statements fail. The code below the Case Else statement is executed to the End Select statement when this occurs End Select End Select statement announces the end of the Select Case code block

12. Creating a New Standard Module • Click the Create tab on the Ribbon • In the Other group on the Create tab, click the arrow on the New Object button and then click Module Module1 view in VBA code window after creation

13. Creating a Function Function procedure,or function: performs operations, returns a value, accepts input values, and can be used in expressions Each function begins with a Function statement and ends with End Function • CapAll is the name of the function • (FValue) is the placeholder for the input value in the function definition • It will be used to accept the value passed from the state field • (FValue) is called a parameter • The value passed to the function and used in place of the parameter when the function is executed is called an argument • Ucase(value) is a function that will convert the contents of value into all capitals

14. Testing a Procedure in the Immediate Window • In the Code window, click View on the menu bar, and then click Immediate Window to open the Immediate window • Type a question mark (?), the procedure name, and the procedure’s arguments in parentheses. If the argument contains a string of characters, enclose the value in quotation marks • Press the Enter key and verify the displayed answer

15. Creating an Event Procedure Create an procedure for the [State] field’s AfterUpdate event on the form Enter the code to test if the [State] field is not empty and call the CapAll function to convert the [State] field’s value to all capitals

16. Compiling Modules • The process of translating modules from VBA to a form your computer understands is called compilation; you say that you compile the module when you translate it • When you run a procedure for the first time, Access compiles it for you automatically and opens a dialog box only when it finds syntax errors in the procedure • Click Debug, then Compile <database name> to compile the VBA code in the entire database on demand

17. Adding a Second Procedure • Use the BeforeUpdate event to validate the contents of a control and respond to the entry • The Form’s BeforeUpdate event fires before changed data in a control or a record is updated • When the State and Zip fields are not empty, store the first two characters of the Zip field into a variable and perform a validation test when the State is “MI” • If the validation test fails, cancel the update and inform the user

18. Adding a Second Procedure • Create the Form’s BeforeUpdate event via the property sheet and enter the code necessary to perform the validation of the Zip field

19. Zip Validation Procedure Code • Private Sub Form_BeforeUpdate(Cancel As Integer) • (Cancel As Integer) • Defines a Parameter “Cancel” as an Integer datatype which can be used to cancel the event procedure (Form’s update)

20. Zip Validation Procedure Code • Dim ZipFirstTwo As Integer • Dim variablename As datatype • Dim is a keyword allowing a variable to be defined • Variables are areas in memory where data may be stored, retrieved, and or updated • Variables have a lifetime, in this case the length of the sub procedure

21. Zip Validation Procedure Code • Dim ZipFirstTwo As Integer • As datatype • Datatype is a keyword that identifies what type of data may be stored in the variable • As Integer in this case is capable of storing a whole number between -32,767 and 32,768

22. Zip Validation Procedure Code • If Not IsNull([State]) And Not IsNull([Zip]) Then • If condition • condition can be evaluated as True/False • Then • Keyword denoting the end of the condition and that the code following will be performed when the condition is True • End If • Keyword denoting the end of the If statement block

23. Zip Validation Procedure Code • If Not IsNull([State]) And Not IsNull([Zip]) Then • Not • Keyword that reverses the condition as in “Not True” or “Not False” • isNull(testvalue) • Keyword that will inspect the testvalue and return True when the contents are empty • If the State field is not empty And the Zip field is not empty Then • Keyword denoting the end of the If statement block

24. Zip Validation Procedure Code • ZipFirstTwo = Val(Left([Zip], 2)) • This is an example of an assignment statement where the right side of the = sign is calculated and the result placed in the property or variable in the left side of the = sign • Val( testvalue) • Keyword that will inspect the testvalue and convert the value into a number if possible • Left(stringvalue,length) • Keyword that will extract from the left of the stringvalue the length number of characters • Assign to ZipFirstTwo the first two characters from the Zip field

25. Zip Validation Procedure Code • Select Case State • Select Case test expression • Denotes the beginning of Case selection structure statement block • Test expression here if the value that will be compared to individual Case statements for a match • End Select • Keyword that denotes that the Case selection structure statement block has ended

26. Zip Validation Procedure Code • Case Value • Case “MI” • Case statement includes the value that will be used to compare to the Select Case test expression to determine if a match has been made • If a match is found, the statements following the Case will be executed • If a match is not found, then the next Case statement will be tested until a match if found or End Select is encountered • If the State field value is “MI” then execute the code following the Case

27. Zip Validation Procedure Code • If ZipFirstTwo < 48 Or ZipFirstTwo > 49 Then • Test the value in ZipFirstTwo to see if it is < 48 OR >49 • If either condition is found to be True the code following the Then will be executed • DoCmd.CancelEvent • Keyword that will cancel the Update of the form • MsgBox “MI zip codes must start 48 or 49” • Keyword that will result in opening a message box as specified • Me.Undo • Keyword that will result in clearing the changes made to the form o • Zip.SetFocus • Cursor to Zip field

28. Changing the Case of a Field Value • Data entry can be made easier in selected fields by utilizing the StrConv function • The StrConv function converts the letters in a string to all uppercase or lowercase letters, or converts the first letter of every word in the string to uppercase and all others to lowercase • Fieldname = StrConv(Fieldname, vbProperCase) • Convert the Fieldname using the vbProperCase argument and assign the result into the original FieldName • vbProperCase is a Visual Basic constant (keyword that performs a task) • Capitalizes the first letter of the every word in FieldName • AfterUpdate event • This event will fire when the focus leaves the field • Private Sub Company_AfterUpdate() • The code in this procedure will be executed when the focus leaves the Company field on the form

29. Changing the Case of a Field Value

30. Changing the Case of a Field Value The “college of marin” entry into the Company field changes to “College Of Marin” when the focus leaves the Company field

31. Hiding a Control and Changing a Control’s Color • When the Start Date field value is prior to 1/1/2011 • The lblPurgeMsg is displayed • The Start Date field forecolor property is set to Red

32. Hiding Controls and Changing a Control’s Color

33. Creating the Procedures for frmQueries • The frmQueries form has two listboxes: lstQueries and lstReports • List boxes contain queries and reports available in the application • The frmQueries form has 4 command buttons (from left to right) • cmdPreviewQuery, cmdDisplayQuery, cmdCloseVBA, cmdPreviewReport

34. Coding the Load Event for frmQueries • The Load event occurs when Access opens a form • List boxes need to open with a selected item • Control.SetFocus • Statement transfers the cursor to the control • SendKeys “{command}” • Statement will peform the command included in the statement

35. Coding the Procedures for frmQueries • Double clicking an item in a listbox or selecting an item in a listbox and then clicking the Preview command button will open the query/report in print preview • Add the functions to perform the Preview and Display actions • Open the VBA code window • Click Insert Procedure • Enter the Function Name and Click Function

36. Coding the Procedures for frmQueries • Add the code statements for each function • DoCmdOpenQuery, DoCmd.OpenReport • lstQueries, lstReports represent the items selected • acPreview represents Print Preview • acViewNormal represents datasheet view

37. Coding the Procedures for frmQueries • Specify the functions for the event properties • lstQueries DoubleClick =basPreviewQuery() (opens selected query in Preview) • cmdPreviewQuery Click =basPreviewQuery() (opens selected query in Preview) • cmdDisplayQuery Click =basDisplayQuery() (opens selected query in Datasheet)

38. Coding the Procedures for frmQueries • Specify the functions for the event properties • lstReports DoubleClick =basPreviewReport() (opens selected Report in Preview) • cmdPreviewQuery Click =basPreviewReport() (opens selected Report in Preview)