Microsoft Office Access 2003 - PowerPoint by tre72542

VIEWS: 37 PAGES: 30

									                                                   XP




 Microsoft Office Access 2003


Tutorial 11 – Using and Writing Visual
     Basic for Applications Code



                  New Perspectives on              1
        Microsoft Office Access 2003 Tutorial 11
                                                                XP
                             Learn about VBA

• When you work in Access, in the background, Visual
  Basic for Applications (VBA) code is being created.
• You can also write your own VBA code that will alter the
  properties of objects, perform calculations, and many other
  custom actions.
• Recall that an event is something that happens while using
  the database.
• You can write a statement that, when an event occurs,
  responds by executing a series of VBA statements.


                            New Perspectives on              2
                  Microsoft Office Access 2003 Tutorial 11
                                                              XP
                     Function procedures,
                  Sub procedures, and modules

• Statements are grouped together into procedures, which
  can be either a function or a Sub procedure.
• Common procedures are usually stored together into a
  module.
• A module can be a standard module (stored in memory
  with other database objects) or it can be a class module
  (stored in association with a particular form or report).
• With class modules, the procedures are available by the
  form or report for which the class module was created.


                             New Perspectives on              3
                   Microsoft Office Access 2003 Tutorial 11
                                            XP
The structure of a VBA module




           New Perspectives on              4
 Microsoft Office Access 2003 Tutorial 11
                                                               XP
            Learning VBA takes time and effort

• Learning to write VBA code efficiently and accurately is
  the hardest part of learning to use Access.
• In this tutorial, you are getting just a small view of VBA
  code.
• You should keep in mind that you will not be considered a
  proficient VBA programmer following this tutorial.
• Rather, if you want to be a database developer, you should
  probably take another course geared towards VBA
  programming.


                            New Perspectives on                5
                  Microsoft Office Access 2003 Tutorial 11
                                             XP
             Review and modify an existing Sub
              procedure in an event procedure

• When a class module already exists, you can view
  the code for the procedures in the VBA editor:
   – Open the property sheets for the object and then locate
     the event for which the procedure is written
   – Click the Build button to open the Visual Basic
     window, which reveals the code stored in the class
     module
• The window in which the code appears is called
  the Code window.

                            New Perspectives on                6
                  Microsoft Office Access 2003 Tutorial 11
                                                           XP
                 Use assignment statements

• Procedures are enclosed between the Sub statement
  and the End Sub statement.
• Within a procedure, you will see statements that
  work together called control structures.
• Assignment statements assign a value to a field or
  property.
• In some cases, an assignment statement might
  assign a new value to an object property.
• You can make changes to VBA code in the Code
  window.

                          New Perspectives on              7
                Microsoft Office Access 2003 Tutorial 11
                                           XP
Event properties for a form




          New Perspectives on              8
Microsoft Office Access 2003 Tutorial 11
                                            XP
The Visual Basic Code window




           New Perspectives on              9
 Microsoft Office Access 2003 Tutorial 11
                                                                                 XP
                         Recognize VBA statements

• In the Code window (shown in the previous slide) you would view,
  edit, and test your VBA statements.
• You will probably not understand the statements that appear in this
  window.
• However, there are a couple of statements that you will recognize:
    – The first statement is the Private Sub statement
    – The last statement is the End Sub statement
• In this sample code, the ForeColor is changed under certain
  circumstances.
    – The code used the RGB function, which will return a specified colored by
      using combinations of Red, Green, and Blue


                                  New Perspectives on                        10
                        Microsoft Office Access 2003 Tutorial 11
                                            XP
    A function that modifies
        an object’s color




          New Perspectives on              11
Microsoft Office Access 2003 Tutorial 11
                                            XP
RGB values for some colors




          New Perspectives on              12
Microsoft Office Access 2003 Tutorial 11
                                                              XP
                  Create Function procedures
                     in a standard module

• To create new procedures click Modules on the objects bar
  and then click New.
• This will open the Code window in the Visual Basic
  window.
• To write a Sub procedure,begin with the Sub statement.
• To write a Function procedure, begin with the Function
  statement.
• The Function will end with the End Function statement.



                            New Perspectives on              13
                  Microsoft Office Access 2003 Tutorial 11
                                                            XP
                Assign a name to a function

• The Function statement is then followed with the
  name of the function.
• If you will be passing values to the Function, the
  Function name is followed by a Parameter list
  (which could be just one item).
• When you pass a value to a Function, it is called
  an argument.
• When naming a Sub procedure or a function, you
  must follow the rules for naming objects.

                          New Perspectives on              14
                Microsoft Office Access 2003 Tutorial 11
                                                XP
Define a function in the Code window




              New Perspectives on              15
    Microsoft Office Access 2003 Tutorial 11
                                                               XP
                  Name and save your module

• Once you have written your Function, you will need to
  save the module that holds the function.
• When you click the Save button, you will be prompted to
  supply a name for the module.
• This is not the same as naming the Function and you do
  not have to follow the name rules mentioned previously.
• Recall that a module will hold a collection of procedures
  so you will want to name the module something that will
  indicate what is inside the module.


                            New Perspectives on               16
                  Microsoft Office Access 2003 Tutorial 11
                                             XP
Use comments in your functions




           New Perspectives on              17
 Microsoft Office Access 2003 Tutorial 11
                                                             XP
                     Create event procedures


• VBA is an event-driven language, meaning that
  when events takes place, procedures are triggered.
• All event procedures are Sub procedures.
• Access will automatically name the event in a
  standard way, which includes:
   – The name of the control
   – An underscore
   – The event name

                           New Perspectives on              18
                 Microsoft Office Access 2003 Tutorial 11
                                                                          XP
                         Add an event procedure

• To add an event procedure to a class module:
   – Open the object to which it will be attached
   – Open its property sheet
   – On the Event tab, select the event that will trigger the function
• This will open the code window for the object you have
  selected.
• Before you run your procedure, it must first be compiled.
• The process of compilation is the process of converting the
  code to a format the computer can understand.


                               New Perspectives on                       19
                     Microsoft Office Access 2003 Tutorial 11
                                                                 XP
                            Use the If statement

• A commonly used control structure is the If statement
• The If statement provides a condition and then if the
  condition is true, a set of statements is executed; if it is
  false, a different set of statements is executed.
• The If statement created in your tutorial incorporates the
  IsNull function into it.
• The IsNull function returns a True value if the argument is
  empty (no text) and it returns a False value if the argument
  is not empty.


                             New Perspectives on              20
                   Microsoft Office Access 2003 Tutorial 11
                                             XP
An example of an If statement




           New Perspectives on              21
 Microsoft Office Access 2003 Tutorial 11
                                             XP
The function execution process




           New Perspectives on              22
 Microsoft Office Access 2003 Tutorial 11
                                              XP
An example of an event procedure




            New Perspectives on              23
  Microsoft Office Access 2003 Tutorial 11
                                            XP
      Declare variables in
   functions and procedures




          New Perspectives on              24
Microsoft Office Access 2003 Tutorial 11
                                            XP
  Primary VBA Data Types




          New Perspectives on              25
Microsoft Office Access 2003 Tutorial 11
                                            XP
           Compile and test Function procedures,
           Sub procedures and event procedures

• When you compile a module, Access will look for errors in
  syntax.
• If any errors are found, an error message will display. You
  will need to fix all syntax errors before the modules can
  complete compilation.
• When no errors exist, Access will translate the procedure.
• You should compile a module any time you make changes
  to make sure there are no syntax errors in its procedures.
• You should also test each procedure to make sure it
  performs as you expect.
                            New Perspectives on              26
                  Microsoft Office Access 2003 Tutorial 11
                                                            XP
          Modify object properties in VBA code

• Sometimes you will want values in your database
  to appear differently in certain circumstances.
• For example, you might want unpaid balances to
  appear in red and paid balances to appear in green.
• You might also want to hide a control under
  certain circumstances but display it in other
  circumstances.
• These kinds of things can be accomplished with
  VBA statements.

                          New Perspectives on              27
                Microsoft Office Access 2003 Tutorial 11
                                                                XP
             Hide text and change display colors

• The ForeColor property of an object represents the color
  of the text in the object.
• You can write statements that will alter this property when
  a certain condition arises
• The Visible property of an object is set to True when you
  want the object to be displayed and False when you want it
  hidden.
• This property can be altered by writing VBA statements to
  change the property under certain circumstances.
• Finally, you can use the DoCmd statement to execute an
  action in a function.
                            New Perspectives on              28
                  Microsoft Office Access 2003 Tutorial 11
                                                XP
Hiding and Changing Display Colors




              New Perspectives on              29
    Microsoft Office Access 2003 Tutorial 11
                                            XP
      An event procedure to
       modify properties




          New Perspectives on              30
Microsoft Office Access 2003 Tutorial 11

								
To top