CIS 217, Spring 2001

1
Passing Parameters Through Forms

• You can create an unbound form to pass parameter
  values to a query
• First create and save the form
• Put the controls on the form where the parameters
  will be entered
• Text boxes for dates or specific values, drop
  down boxes to allow the user to select a
  particular value
• Name the controls something meaningful
• Next create a query with the desired tables and
  fields

2
Passing Parameters Through Forms

• In the applicable field criteria row reference
  the controls on your form using the following
  syntax
• Forms!MyForm!MyControl
• Your form must be open in form view
• Enter the values for the parameters
• Open the query with the form still open and the
  parameter values you entered in the form will be
  applied to the query
• See the form frmQryOrders and the query
  qryFrmOrders in the Restaurant Answer file for an
  example
• Note the form has a button to open the query
  which makes thing easier than doing it manually

3
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

4
Responding to Events

• Your Access database responds to events by using
  Macros or VBA code
• E.g. You can write a macro to open a report on
  the OnClick event of a button
• Each Access object has different events
  associated with it
• E.g. a command button as an OnClick event
• 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?
• Right click and select properties

5
Responding to Events

• Forms, reports and the controls on the form or
  report all have there own events
• A form has an OnOpen event where code or a macro
  to initialize controls can be put
• To associate a macro to a particular event first
  write the macro then select the macro from the
  drop down box in the event you wish to associate
  it with
• Write and save a macro to open a query
• Select the macro from the OnClick event of a
  command button
• Each time the command button is clicked the query
  will be opened
• See frmQryOrders in the Restaurant Answer file

6
Responding to Events

• As we go through the semester we will be learning
  about events of various objects
• Begin looking at what events each object has by
  studying the event tab of the object
• Most of the semester will be spent writing VB
  code to respond to events
• But first well learn about MACROS!

7
Macros

• Easy to create but hard to manage in large
  database projects
• Macro Actions are comparable to programming
  commands or functions
• Actions instruct Access to perform a task
• In Help many subjects have an Action (for macros)
  and a Method (for code)
• ApplyFilter method for VBA and ApplyFilter action
  for macros
• OpenReport method for the DoCmd object in VBA and
  the OpenReport action for macros

8
Macros

• Macros are easy because they prompt the user for
  all required arguments
• Macros are hard to manage because there is
  limited debugging and error handling capabilities
• There is only one thing that Macros can do that
  Code can notbut tons of things code can do that
  Macros can not!

9
When to Use Macros

• Balter recommends never using macros to control
  the flow of your code.
• But, if you are not a programmer and your need
  for customizing actions is minimal (opening forms
  and reports per the users request) you use macros
• Macros can also be converted to code so it is a
  good way to learn the syntax of how to perform
  certain tasks

10
When to Use Macros

• Think about the future of the application
• Will it become more automated?
• What is the expected level of programming
  experience of future administrators of your
  database?
• Will there be things you need to do in code or
  can everything be in macros?
• Be consistent, do not use a macro to open a
  report from one form and code on another form

11
Creating a Macro

• Click New on the Macro tab
• Select the action you would like to perform from
  the drop down list
• Access displays the list of available and/or
  require arguments
• Think of arguments like the parameters of our
  functions
• Choose the argument values from the drop down
  lists

12
Short Cut

• If you drag and drop a form icon from the forms
  tab to your macro Access will create an OpenForm
  action
• With this technique Access automatically fills in
  three conditions of the OpenForm action
• Name of the form
• View of the form (Form)
• Window mode (Normal)

13
Macro Comments

• ADD COMMENTS to your macro for future reference,
  even if it is obvious
• You might want to include where the macro is
  called from, or a general situation when the
  macro is called
• E.g. Called from frmContact or Called on all
  NotInList events
• Multiple line comments can be added by leaving
  the corresponding action column blank

14
Macro Names

• Macro names are like subroutines
• Allow you to place more than one routine in a
  macro
• Create macro names that perform related functions
  within one macro
• To add a name first display the name column
• Click the macro names button on the macro, or
• Select ViewMacroNames
• The macro name column is a toggle
• You can hide or show it without losing its
  contents

15
Macro Names

• You can control the flow of the macro by calling
  the named subroutines from within the macro
• Create all of your named macro subroutines
• Save the macro
• Insert a row where you want to call a subroutine
• Select the RunMacro action
• Select your named routine to run

16
Macro Conditions

• Macro conditions let you determine when a
  specific macro action will execute
• Display the conditions column
• Click the condition button on the macro design
  toolbar
• Select ViewConditions
• Conditions are valid Access expressions including
  the conditional operator
• Forms!frmCustomers!CustomerNameElizabeth
  Hanke

17
Macro Conditions

• The Action will be executed if the condition is
  true
• You can run multiple actions by placing in the
  condition column on the rows directly following
  the condition
• Access checks all of the conditions in a macro
  unless you indicate the macro should stop
• You can mimic an If..Then..Else statement using
  the StopMacro action

18
Referring to Forms with Macros

• You must specify a complete form reference
• Forms!frmCustomers!CustomerName
• All Access functions can be used in the
  conditions of macros
• Year(Forms!frmCustomers!Birthdate)lt1968
• Left(Forms!frmCustomers!CustomerName,1)A
• You can not have variables in Macros

19
Macro Design View
Macro Condition Button
Macro Name Button
Run Button
Condition w/Multiple True Lines
20
Running a Macro

• Click run in macro design view
• Click run from the macro tab
• Select the macro from the appropriate event
  property drop down list of all macros
• If a macro contains names, only the first rows of
  the macro are run unless the macro.name is
  selected
• mcrStatus will run until the first name
• mcrStatus.MyName will run starting at MyName
  until the next name

21
Special Macro - AutoExec

• If you create a macro and name it AutoExec it
  will be run every time you open your .mdb file
• Can also use the StartUp options in Access 97 and
  2000
• Use this to open a splash or switch board form
• Run code to set constants to be used such as
  company information

22
Special Macro - AutoKeys

• If you create a macro and name it AutoKeys you
  can redefine keystrokes to a command or series of
  commands
• Enter an Access key name (found in help) in the
  name column of your macro
• Select the macro action you want to associate
  with the key stroke combination
• Z is Alt-Z, could set it to open an entry form
• As soon as you save the macro the key strokes are
  set