CIS 217, Spring 2001

1
Schedule

• Exam Questions
• Macro Assignment Questions
• Assignment update
• Moving on to VBA. . . .

2
Assignment Updates

• Assignments 1 through 5 are history
• If you have not yet turned them in, forget about
  them and move forward with the new assignments
• Assignment 6 is due today but may be turned in
  next week for 9 points
• Assignment 7 comes from the Callahan book
• Assignments given from the Callahan book usually
  require you to complete the entire lesson then
  perform one or two additional tasks
• Read the assignments carefully b/c you will not
  get full credit if you simply complete the
  Callahan lesson
• Occasionally there are mistakes in the book
  code that worked in Access 97 that does not in
  2000 but did not get removed from the book

3
Assignment Updates

• BEWARE The Callahan book is easy to follow
  because it is a step by step book
• Completing the assignments does not mean you have
  learned anything!
• Pay attention to what you are doing and what
  other applications the feature may have
• Think about how you could code apply the code in
  the book to an Access database you are familiar
  with
• The main VBA goal of the class is to increase
  your understanding of VBA such that you can use
  sample code from the book, help or other sources
  in your applications and make changes to the code
  as needed

4
Callahan Lesson 1

• Use the database wizard to create a basic
  contacts database (like the one on the Exam)
• Import data from Excel to the new structure
• Review the objects the wizard creates
• Add a button to the main entry form using the
  command button wizard
• Discussion of application versus a database with
  the use of code and a switchboard

5
Database Wizard

• Access has a database wizard that will create an
  entire sample application for you
• Useful starting point or ideas for common
  databases such as contact, inventory, finances,
  home lists, etc.
• Can use forms and codes created in your database
• Like most Wizards, while it creates a working
  database it does not create objects or code that
  adhere to naming conventions, use the most
  efficient code or take advantage of all the
  features of VBA in Access 2000
• My theory is that many of the wizards are not
  updated with each version of Access, but I
  cant back this up with fact!

6
Database Wizard

• Select FileNew and select the Database tab
• Choose the template to start with an follow the
  prompts
• Note this feature must have been installed for it
  to work, some templates require the disk before
  you can use them
• Lets create the contacts database and review the
  objects

7
Switchboard

• Switchboard form
• Used to navigate through your application usually
  by opening up specific forms or reports when
  buttons are clicked
• The wizard created some items that are used by
  the Switchboard
• table - Switchboard Items
• Contains all of the choices on the switchboard
• form - Switchboard
• Just a regular Access for with command buttons
  and code to make the buttons work
• Can be edited for aesthetics but do not mess with
  command button names or events
• Can add controls to the form such as application
  titles, date stamps, reminder items

8
Switchboard Manager

• A switchboard can be edited or added to an
  existing database using the Switchboard Manager
• ToolsDatabase UtilitiesSwitchboard Manager
• Creating New switchboards does not actually
  create new forms, just adds items to the
  Switchboard Table
• A new switchboard is just a new set of buttons
• The main switchboard opens first, then buttons
  can call sub menus/switchboards which can hold
  related items
• E.g. the main switchboard may have a Reports
  button that opens a switchboard showing various
  reporting options
• How do you get the switchboard to open up
  automatically when the database is opened?
• ToolsStartUp . Select the form named
  Switchboard
• You do not have to use the Switchboard manager,
  you could create your own menu forms, but the
  Switchboard manager is easy to use and quick

9
Other Objects

• Tables/Forms/Reports
• No naming convention
• Uses caption property, be careful to know the
  real field names
• Bases forms off of tables, not queries
• Relationships
• Creates them and selectively enforces referential
  integrity, be sure to check the settings
• Does not create any queries!!

10
Import Data

• Callahan provides an text file called CH1 Client
  Data Text File
• Format of this file is ready to be imported into
  the existing structure
• Field names are in the first row and match
  existing table names
• Usually some editing of the data file is
  necessary
• You can also import data into a temporary table,
  make needed changes then create an append query
  to put the data in the tables
• Select FileGet External DataImport and follow
  the prompts
• Data to be appended to the contacts table

11
Using the Wizard to Create Buttons

• When you use the wizard to create buttons the
  wizard will write the VB code based on the
  operation you select
• Make sure the wizard is turned on and select
  command button
• View the code written by selecting the build
  button () from the events tab of the button
• You may edit the code as needed after the wizard
  is finished
• You may also add a button to your form without
  using the wizard at all
• Beware The wizard does not always write the
  best code!

12
Visual Basic for Applications

• Development language for Access
• Same language for all of the Office products
• Each product has some objects specific to its
  application
• E.g. Access has tables and forms, Excel has
  worksheets and workbooks
• In most cases the syntax is exactly the same as
  the full version of Visual Basic
• We will learn some of the basic VB syntax but
  focus specifically on how VBA can be used with
  Access

13
Why to use VBA

• Create complex logic structures such as Case
  statements and various loops
• Create and use constants and variables
• Use Access defined functions
• Perform transaction processing
• Programmatically create and work with database
  objects
• Create libraries of user-defined functions to use
  in all your applications
• Implement error handling
• Automate routine maintenance on your database

14
Objects and Properties

• Objects are all the things that make up your
  database
• Tables, queries, forms, reports, macros, modules
  and all of there components such as fields, combo
  boxes, list boxes, etc
• A property is an attribute of an object
• Each object has many properties
• Properties differ by type of object
• Forms and combo boxes both have a width property,
  but Forms also have a record source property
• You can usually set the property of an object in
  a property sheet, a macro or Visual basic
• Some properties can not be set using Visual
  basic, some can only be set using Visual basic
• Access help always tells you to what objects a
  property applies and where it can be set

15
Events

• Windows is an event-driven operating system
• The operating system responds to many events that
  are triggered by actions the user takes and by
  the operating system itself
• Users and/or your code initiates events
• The code you write determines what happens (if
  anything) in response to an event
• Events include mouse movements, changes to data,
  a form opening, a record being added, etc

16
Responding to Events

• You respond to events by using Macros or VBA code
• Each Access object responds to different events
• The Event tab in an objects property window
  displays all the events associated with that
  object
• How do you view the properties of an object?
• Forms, reports and the controls on the form or
  report all have there own events

17
Methods

• Methods are actions that can be taken on the
  objects in your database
• Compared with the Actions shown in the macro
  window
• Action OpenForm has a counter VBA method.
• Different objects have different methods
• A method is like a subroutine or function that is
  specific to the object to which the method
  applies
• E.g. a form as a GoToPage method which will more
  to a specific page, but a text box does not have
  this method
• What is the difference between properties and
  methods?
• Access help or the module window tells you all
  the methods available for each object

18
Modules

• VBA code is written in subroutines and functions
  which are stored in modules
• Standard/Global modules are created by clicking
  the New button on the Modules tab
• Code created here is accessible from anywhere in
  the database
• Class modules can be standalone objects, or
  associated with a form or report
• In Access the class modules are almost always
  associate with specific forms or reports

19
Modules

• When ever you add code behind a form or report
  Access creates a Class module associated with
  that form or report automatically
• The code is available only to that particular
  form or report and if the form or report is moved
  the code goes with it
• Important to decide whether the code your are
  writing at any given time is applicable to more
  than one object in your database so you can
  decide where to put it
• Use standard modules if the code will be used by
  lots of objects
• Use class modules if it is truly specific to only
  one object, such as a form or report

20
Modules

• All modules contain a General declarations
  section for declaring variables and constants
  that you want to be visible to all the functions
  and subroutines in that module
• Option Explicit is a statement that can be
  included or excluded in the General Declarations
  section
• Requires explicit declarations of variables
  before they can be used
• Prevents typos and saves memory

21
Event Procedures

• Event procedures are stored in class modules
• Event procedures are automatically created when
  you write event code for an object
• To write an event procedures
• click the event tab in the objects property
  window and choose Event procedure
• Click the ellipsis button or right click and
  select build
• Note that the routine is named the same as the
  object name, then _ and the event itself
• If you change the name of your object you have to
  change the name of your event code in order or
  the code to run as intended
• E.g. if you change the name of your Save Button
  to MySaveButton the OnClick event will no longer
  work
• Using the command button wizard in a form will
  also create an event procedure

22
Event Procedures

• The code you write in the event procedure will
  run, or execute, when the event occurs
• An OnClick event occurs when the object is
  clicked
• To test your code you usually invoke the event
  and see what happens!
• VBA has many debugging features to help with
  debugging your code
• We will learn about the features as we go
• At first the most common bugs are incorrect
  syntax and not invoking or understanding the
  proper events to code

23
Sub Procedure v Functions

• Functions return values and can be called from
  queries
• Think of Access functions we have been using
  already
• Month() returns the month
• You could create a function called FullName()
  that would return the first name and a space and
  the last name passed to it
• Both can receive arguments or parameters
• Both can contain loops and logic structures
• Both can calculate values
• Both can call other procedures or functions
• At first we will only be creating Sub Procedures

24
Note About VB

• Visual Basic helps you determine which arguments
  or methods an object has with an auto help menu
  that shows you options as you type with a feature
  called List Constants
• Typing DoCmd. in a module will show a list of
  all the DoCmd methods available to you
• After you select a method, the relevant arguments
  for that method are shown
• Arrow to the choice you want and hit tab to
  select
• To get info about an existing object/method right
  click after the name of the object and select
  Quick Info, or press Ctrl-I

25
Review Code of the Add New Record Button

• In Lesson 1 an add new record button code was
  created by the wizard
• How do you view the code created by the wizard?
• Right click the button and select Build Event
• This always brings you to the controls default
  event
• The default event is meant to be the most
  commonly programmed event
• For Buttons this is the OnClick event
• View the properties of the button, go to the
  events tab and click the build () button on the
  OnClick event which will indicate if there is
  code already associated with the event by
  displaying Event Procedure
• Select ViewCode and you will be brought to ALL
  the code behind the form

26
Visual Basic Window

• However you choose to view code you will be
  presented with the Visual Basic Window
• Opens as a separate window in Access 2000
• Can stay open while you work on your Access
  objects
• Basic Parts
• Code window to display the code
• Notice the beginning and end markings of each
  procedure
• Sub ProcedureName()
• Code
• End Sub
• This is where we will be working most of the time
• Note that you can have multiple code windows open
  at any one time
• Title bar and comb boxes at the top of the window
  help you remember where you are!!!

27
Visual Basic Window

• Basic Parts
• Code window to display the code
• The combo box on the left displays the list of
  objects for forms and reports
• E.g. for a form all of the controls in the form
  as well as the form itself
• The combo box on the right lists all the event
  procedures associated with a particular object
• Events with code appear in bold
• Selecting an event will automatically produce an
  event procedure for the selected object

28
Visual Basic Window

• Basic Parts
• Project Window to display a list of all the
  modules that can contain visual basic code for
  your application
• You can jump to specific code or create new
  code from this window by double clicking
• Helpful for an overview and to copy code from one
  object to another
• If you get lost, close all the code windows
  then the VB window and return to the object/event
  where you want to go, then go back to the code
  from there

29
Visual Basic Window

• Basic Parts
• Properties window to display the properties of
  the listed object
• Use the combo box to select the object
• The objects listed I the combo box include the
  parent object selected in the project window
• E.g. a form and its controls
• You can change properties here or within the
  design view of the object
• Any of these windows can be viewed or not by
  selecting View and the object

30
Reading Assignment

• Balter, Chapter 7 is an introduction to VBA
• Reviews the VBA windows in more detail
• Introduces some common control structures and
  functions
• We will be covering most of the items in the
  chapter eventually but an early read of them can
  only help your understanding
• Chapter 8 goes into more about objects,
  properties and events

31
Review Code of the Add New Record Button

• Used the DoCmd (Do Command) object
• The methods of the DoCmd object run Microsoft
  Access actions from Visual Basic
• An action performs tasks such as closing windows,
  opening forms, and setting the value of controls.
  
• E.g. you can use the OpenForm method of the DoCmd
  object to open a form, or use the Hourglass
  method to change the mouse pointer to an
  hourglass icon.
• Syntax DoCmd.method arg1, arg2, ...
• DoCmd.OpenForm "Employees", , ,"Title 'Sales
  Representative'
• DoCmd.OpenReport MyReport",acViewPreview

32
Review Lesson 2

• The Add Record button already exists on the form
  in the CH02 database
• Lesson requires you to
• Make the form allow changes only after an Edit
  button is clicked
• Added a Save Record button for manual saves and
  alerts the user that the save is complete
• Since Access saves records when you move off of
  the record, add code to alert the user that their
  changes were saved even if the save record button
  was not clicked

33
Review Lesson 2

• More uses of the DoCmd object for running actions
• Setting form properties via code
• AllowEdits
• Figuring out which events need to be programmed
  given actions a user might take that fall outside
  the buttons you created, such as moving to the
  next record to save a record

34
Review Lesson 2

• New form property AllowEdits
• Determines whether the form will allow a user to
  edit existing data
• Set to yes/true if data displayed in the form can
  be edited or changed, no/false prevents any
  changes to the data
• Set the form in the form properties, can be
  changed back and forth with code
• Related properties
• AllowAdditions can new records be created?
• AllowDeletions can existing records be deleted?
• Lock the form from edits by default, then create
  a command button will change this property when
  the user clicks the button
• Prevents accidental data changes

35
Save Record

• Create a save record button that the user will
  click to save the record
• The button should save the record
• Prompt the user that it was saved
• Turn the AllowEdits property back to False to
  prevent further changes
• The wizard creates a Save record button with the
  following code
• DoCmd.DoMenuItem acFormBar, acRecordsMenu,
  acSaveRecord, , acMenuVer70

36
Save Record

• Generally you want to avoid using the DoMenuItem
  method
• Why? With ever version of Access the menus
  change! Youre application will be very version
  specific if you use a lot of DoMenuItems
• For most things there is a different way to
  perform the same action that is more efficient in
  the long run, even if its more difficult up front

37
Save Record

• In this case using the DoCmd object with the
  RunCommand method and the access constant
  acCmdSaveRecord is better
• Your code would look like this
• DoCmd.RunCommand acCmdSaveRecord
• This code is not AS version specific as the
  RunMenuItem action!
• Try and change the code the wizard creates to
  this as part of your homework assignment

38
MsgBox Function

• After the record is saved, alert the user with a
  message box
• Displays a message in a dialog box, waits for the
  user to click a button, and returns an Integer
  indicating which button the user clicked
• Syntax MsgBox(prompt, buttons , title ,
  helpfile, context)
• The Msg box Function is a great function that is
  used very often to give and receive information
  to/from users