access 2007 tutorial 11 using and writing visual basic applications code l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Access 2007 Tutorial 11 Using and Writing Visual Basic Applications Code PowerPoint Presentation
Download Presentation
Access 2007 Tutorial 11 Using and Writing Visual Basic Applications Code

Loading in 2 Seconds...

play fullscreen
1 / 38

Access 2007 Tutorial 11 Using and Writing Visual Basic Applications Code - PowerPoint PPT Presentation


  • 358 Views
  • Uploaded on

Access 2007 Tutorial 11 Using and Writing Visual Basic Applications Code . (Access Developer). Introduction to Visual Basic for Applications. Visual Basic for Applications (VBA) is the programming language provided with Access and other Office programs

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Access 2007 Tutorial 11 Using and Writing Visual Basic Applications Code' - shae


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
access 2007 tutorial 11 using and writing visual basic applications code
Access 2007Tutorial 11Using and Writing Visual Basic Applications Code

(Access Developer)

introduction to visual basic for applications
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
events
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
procedures
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
modules
Modules
  • Module: an object where groups of related procedures are stored
modules6
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
create a procedure
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
create a procedure8
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

create a procedure9
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
create a procedure10
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

create a procedure11
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

creating a new standard module
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

creating a function
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
testing a procedure in the immediate window
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
creating an event procedure
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

compiling modules
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
adding a second procedure
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
adding a second procedure18
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
zip validation procedure code
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)
zip validation procedure code20
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
zip validation procedure code21
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
zip validation procedure code22
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
zip validation procedure code23
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
zip validation procedure code24
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
zip validation procedure code25
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
zip validation procedure code26
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
zip validation procedure code27
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
changing the case of a field value
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
changing the case of a field value30
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

hiding a control and changing a control s color
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
creating the procedures for frmqueries
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
coding the load event for frmqueries
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
coding the procedures for frmqueries
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
coding the procedures for frmqueries36
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
coding the procedures for frmqueries37
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)
coding the procedures for frmqueries38
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)