CIS 217, Spring 2001

1
Form Events

• 29 Total Form Events, each with a distinctive
  purpose
• You can run code in response to any event
• Which event you select is very important
• Sometimes you will need to put the same code in
  more than one event just to make sure it is run
• E.g. In Lesson 2 you had to change the forms
  AllowEdits property to false when a record was
  saved, regardless of how it was saved which
  required putting the same code in more than one
  event
• Sometimes determining which events should hold
  what code is an exercise of trial and error
• We will cover a few of the most common events

2
Other Command Button Wizards

• The command button wizards will write code for
  many common database needs
• Good place to start to learn syntax but always
  keep in mind the wizard does not necessarily use
  the most efficient code for the job!
• Common tasks the wizard can do
• Save record, add record, delete record, duplicate
  record, undo record, print current record
• close form, open form,
• move to the next, previous, last and first
  records
• Open, close reports
• Open other applications
• Run macros or queries

3
Opening and Closing a Form

• When you open a form, the following sequence of
  events occurs for the form
• Open ?Load ? Resize ? Activate ? Current
• If there are no active controls on the form, the
  GotFocus event also occurs for the form after the
  Activate event but before the Current event
• When you close a form, the following sequence of
  events occurs for the form
• Unload ?Deactivate ?Close
• If there are no active controls on the form, the
  LostFocus event also occurs for the form after
  the Unload event but before the Deactivate event

4
Moving Between Open Forms

• When you switch between two open forms, the
  Deactivate event occurs for the first form, and
  the Activate event occurs for the second form
• Deactivate (form1) ? Activate (form2)
• The Deactivate event for a form also occurs when
  you switch from the form to another window in
  Microsoft Access.
• The Deactivate event does NOT occur when you
  switch to a dialog box, to a form whose PopUp
  property is set to Yes, or to a window in another
  application.
• An Open event does not occur if you move to a
  form that is already open, even if you've moved
  to the form by carrying out an OpenForm action.

5
Working with data on a form

• Form and control events occur as you move between
  records in the form and change data. For example,
  when you first open a form, the following
  sequence of events occurs
• Open (form) ? Load (form) ? Resize (form) ?
  Activate (form) ? Current (form) ? Enter
  (control) ? GotFocus (control)
• Similarly, when you close a form, the following
  sequence of events occurs
• Exit (control) ? LostFocus (control) ? Unload
  (form) ? Deactivate (form) ? Close (form)

6
Events Between Records

• When you move the focus to an existing record on
  a form, enter or change data in the record, and
  then move the focus to another record, the
  following sequence of events occurs for the form
• Current (form) ? BeforeUpdate (form) ?
  AfterUpdate (form) Þ Current (form)
• When you leave the record whose data has changed,
  but before you enter the next record, the Exit
  and LostFocus events occur for the control with
  the focus.
• These events occur after the BeforeUpdate and
  AfterUpdate events for the form, as follows
• BeforeUpdate (form) ? AfterUpdate (form) ? Exit
  (control) Þ LostFocus (control) ? Current (form)

7
Events Between Controls

• As you move the focus among the controls on a
  form, events occur for each control. For example,
  the following sequences of events occur when you
  
• Open a form and change data in a control
• Current (form) ? Enter (control) ? GotFocus
  (control) ? BeforeUpdate (control) ? AfterUpdate
  (control)
• Move the focus to another control
• Exit (control1) ? LostFocus (control1) ? Enter
  (control2) ? GotFocus (control2)
• Move the focus to another record
• BeforeUpdate (form) ? AfterUpdate (form) ? Exit
  (control2) ? LostFocus (control2) ? Current
  (form)

8
Working with subforms

• When you open a form containing a subform, the
  subform and its records are loaded before the
  main form.
• Thus, the events for the subform and its controls
  (such as Open, Current, Enter, and GotFocus)
  occur before the events for the form.
• The Activate event does not occur for subforms,
  however, so opening a main form triggers an
  Activate event only for the main form.
• Similarly, when you close a form containing a
  subform, the subform and its records are unloaded
  after the form.

9
Working with subforms

• The Deactivate event does not occur for subforms,
  so closing a main form triggers a Deactivate
  event only for the main form. The events for the
  controls, form, and subform occur in the
  following order
• Events for the subform's controls (such as Exit
  and LostFocus)
• Events for the form's controls (including the
  subform control)
• Events for the form (such as Deactivate and
  Close)
• Events for the subform
• If all else fails when trying to determine when
  an event occurs use the message box function to
  determine when and if certain events occur!!

10
Class Demo

• Using Ch03 Contacts EAH.mdb put a message box in
  the Open event and the Current event of the form
  Contacts
• The message box function has the following syntax
• MsgBox(Prompt, Buttons As VbMsgBoxStyle
  vbOKOnly, Title, HelpFile, Context) As
  VbMsgBoxResult
• This syntax returns a value to which a variable
  can be set
• MsgBox Prompt, Buttons As VbMsgBoxStyle
  vbOKOnly, Title, HelpFile, Context
• This syntax does not return a value and will just
  prompt the user with the message
• Note that Prompt and Title must be valid string
  expressions
• My String Prompt
• The can be used to create string expressions
  such as Me.MyComboBox combo box value where
  MyComboBox is a control name on the current form

11
Form Events - Current

• Very common event to code
• Happens each time focus moves from one record to
  the next record
• Examples
• Change particular shading of controls based on
  values in the current record
• Set the enabled/visible/locked properties for
  irrelevant controls
• Set values of unbound controls to the current
  record
• E.g. A combo box used for filtering would be set
  to be equal to the current record (Lesson 3)
• Make sure not to repeat code in the current event
  and the open event since the current event runs
  for the first record after the open event

12
Form Events Before Update

• Runs before a record is updated, after the user
  initiates a save record by either moving to
  another record or selecting save
• Examples
• Perform complex validations and stop the update
  if the rules are broken
• Test for null values
• Confirm changes with the user using a message box
• Record changes made to another table or save the
  old record in another table
• Has one argument, Cancel that is set to either
  true or false
• Canceltrue will cancel the update and put the
  user back into the form, with changes unsaved

13
Form Events - AfterUpdate

• Runs after changes are made to a record
• Examples
• Requery combo boxes which are dependent on data
  in the record
• Confirm a save to a user with a message box
• Reset the enable/visible/locked properties of the
  form (like you may also do in the OnCurrent
  procedure) based on the new data values

14
Form Events - Delete

• Runs when a user initiates a delete record but
  before the deletion occurs
• Runs once for each selected record if multiple
  records are selected
• Examples
• Limit deletions to meet certain values
• Write specific confirmation message boxes
• Create back up data or track deletions in another
  table

15
Form Events - Open

• Runs when a form is opened but before the first
  record is displayed
• Examples
• Control what happens when the form first opens
• Make sure the form is opened in the right context
• Can cancel the open if certain criteria are not
  met such as confirming there are existing records
  to display
• CancelTrue

16
Form Events - Close

• Runs when a form is closed and removed from the
  screen
• Examples
• Requery combo boxes in open forms which are based
  on data changed with the current form
• Open another form, such as a main menu form

17
On Click Event for Controls

• Any code placed in an event procedure for the on
  click event will be run every time the user
  clicks that object
• Before you write your code, think about what you
  want to do and how you would do it manually
• ie. If I want to allow a form to be edited when
  the button is clicked I change the allow edits
  property
• Look up syntax in help by searching for
  properties and examples of code

18
Me Property

• Me property can refer to the form or report that
  is currently running
• Me!LastName refers to the control named LastName
  on the current form
• Forms!Employees.LastName refers to the control
  named LastName on only the employees form
• These two lines are the same if the form named
  Employees is currently running but if the name of
  the Employees form changes, the second syntax
  will become invalid
• Very convenient because it works faster than
  explicit references and you can make generic code
  that works for all forms in a database
• Also will prompt you with all the valid control
  names on the current form or report when you type
  Me. in the VB editor

19
Form Events Used In Lesson 2

• Current event for the form occurs each time focus
  moves from one record to the next
• Used in this lesson to turn off the edit function
  if the user moves without clicking the save
  button
• AfterUpdate event runs after changes are made and
  SAVED to a record
• Used in this lesson to alert the user that the
  record was saved even if the Save button is not
  clicked
• Note this event does not run until the changes
  are saved and does not run if changes are not
  made or are canceled before a save

20
Review Lesson 3

• Add a combo box to the contacts form to filter
  the records based on a user selection
• Uses the wizard so review the code when complete!
• Combo box is not bound to a control source
• AfterUpdate event coded to move to the selected
  contact record
• OnCurrent event coded to keep the box
  synchronized when moving between contacts without
  using the combo box

21
Review Lesson 3

• Combo box uses the bookmark property of Access in
  combination with the Recordset Close property
• Bookmark property
• There is a combo box wizard that will write this
  code for you but you should be able to understand
  all the parts and make edits as needed
• Pay particular attention to the properties of the
  combo box that you will change as part of the
  lesson (RowSource)

22
Review Callahan Combo Box

• Wizard is very convenient but lets create our
  own combo box event procedure from scratch
• First create the combo box and fill it with data
• Program the on update event of the box to include
  one of the following two codes
• Me.RecordSetClone.FindFirst "ContactID "
  Me!ComboName
• Me.Bookmark Me.RecordsetClone.Bookmark
• OR
• Dim rs As Object
• Set rs Me.Recordset.Clone
• rs.FindFirst "ContactID "
  Str(Me!Combo55)
• Me.Bookmark rs.Bookmark

23
Review Callahan Combo Box

• The book uses the wizard to write code in Lesson
  3 to find a particular record in a RecordSet
  clone then jump to that record. This is the code
  from the wizard
• Dim rs As Object
• Set rs Me.Recordset.Clone
• rs.FindFirst "ContactID "
  Str(Me!Combo55)
• Me.Bookmark rs.Bookmark
• The database from Lesson 4 uses the following
  code only, where Combo55 is the name of the
  filter combo box
• Me.RecordsetClone.FindFirst "ContactID "
  Str(Me!Combo55)
• Me.Bookmark Me.RecordsetClone.Bookmark
• It is better programming form to declare the RS
  variable but it is not required

24
Review Callahan Combo Box

• The RecordSetClone object allows you to perform
  methods on the current record set
• Think of it as a temporary copy of the underlying
  record set of the form
• The Bookmark property uniquely identifies the
  current record in a record set
• You can use the Bookmark property with forms to
  set a bookmark that uniquely identifies a
  particular record in the form's underlying table,
  query, or SQL statement.
• The Bookmark property contains a string
  expression created by Microsoft Access.
• You can set this property by using a macro or
  Visual Basic.

25
Review Callahan Combo Box

• When a bound form is opened in Form view, each
  record is assigned a unique bookmark.
• In Visual Basic, you can save the bookmark for
  the current record by assigning the value of the
  form's Bookmark property to any string variable.
• To return to a saved record after moving to a
  different record, set the form's Bookmark
  property to the value of the saved string
  variable.
• You can use the StrComp function to compare a
  Variant or string variable to a bookmark, or when
  comparing a bookmark against another bookmark.
• StrComp(string1, string2, compare)
• The third argument for the StrComp function must
  be set to a value of zero.

26
Review Callahan Combo Box

• Bookmarks are not saved with the records they
  represent and are only valid while the form is
  open. They are re-created by Microsoft Access
  each time a bound form is opened.
• There is no limit to the number of bookmarks you
  can save if each is saved with a unique string
  variable.
• As you find the first match you are changing the
  bookmark property of the RecordSetClone
• By setting the recordset of the Form using the Me
  property to the Bookmark of the RecordSetClone
  you will cause the form to jump to the correct
  record

27
Why Synchronize the Box?

• You need to synchronize the box to avoid
  confusion for users
• The combo box is not bound to the data and can
  not change the data but the user does not know
  that
• If you are on Griffins record but the combo box
  says Geloff they will become very confused
• Add code to the OnCurrent event to set the value
  of the combo box equal to the value of the
  ContactID

28
Review Lesson 3

• Prompt user when the select to filter by form by
  using and If-Then-Else structure
• Create an option button group to allow the user
  to filter or not filter the record set
• Sets the filter based on actions taken by a user
• Updating the values in the option group
• Also must remember to change the option group
  value box based on other actions taken by the
  user, such as filtering by selection manually
  using the
• Set the option box to null to indicate that
  another filter is working

29
Review Lesson 3

• Use the immediate window to evaluation form
  properties in real time
• Immediate window is great for debugging
• Show the window by doing the following while in
  the VB editor
• ViewImmediate Window
• Ctrl-G
• You can type commands directly into the window
• Try it in the demo file
• Bring up the VB Editor and Immediate Window
• Type Beep and hit enter
• Type ? (for print to screen) Month(date())
• Works for any valid expression/variable that
  Access can evaluate at the time the window is
  open
• I.e. if the form Contacts is open you can
  evaluate controls on that form in the immediate
  window, but if it is closed you can not because
  the controls have no value!

30
Review Lesson 3

• Filter Property
• Access sets two properties when the user filters
  a form using the filter by selection or filter by
  form buttons
• Filter Where portion of the SQL statement that
  is the filter, without the word WHERE
• FilterOn true/false which determines whether the
  filter is currently applied or not
• Code in the AfterUpdate event of the option group
  changes these properties based on the user
  selection
• An event also occurs when a user filters a form
  On Apply Filter event
• You can check to see what type and the value of
  the filter the user has selected

31
Review Lesson 3

• Apply Filter Event
• Occurs when the users applies a filter, removes a
  filter or sorts
• In this lesson you use it to set the radio
  buttons if the user selects one of the filter
  options and do something accordingly
• Has two arguments that can be referenced and
  changed
• The ApplyType indicates the action taken to call
  the event
• acShowAllRecords 0 
• acApplyFilter 1
• acCloseFilterWindow 2
•  acApplyServerFilter 3
•  acCloseServerFilterWindow 4
• Cancel argument can be used to cancel the filter
  if it is invalid by setting it equal to -1

32
If..Then..Else..End If

• In Lesson 3 you use the If..Then to determine
  what type of filter the user has selected and in
  Lesson 4 you will use the If statement in
  combination with the built in IsNull() function
  of VBA to only set the Dear field if it is empty
• Syntax of the If statement is
• If ltltconditiongtgt Then
• Code to execute if true
• Not run if ltltconditiongtgt is false
• Else
• Code to execute if false
• Not run if ltltconditiongtgt is true
• End If

33
If..Then..Else..End If

• Not necessary to have an Else if there is nothing
  to do
• The code after if is only executed if the
  condition is true and the code after the else is
  only executed if the condition is false
• Think carefully about what should go inside the
  If
• If there is the same line of code in both parts
  of the statement then take it out and put it
  after the End If

34
Nested If..Then..Else..End If

• You can next If statements in each other two
  possible ways
• If ltltconditiongtgt Then
• Code to execute if true
• Not run if ltltconditiongtgt is false
• Else
• Code to execute if false
• Not run if ltltconditiongtgt is true
• If ltltconditiongtgt Then
• Code to execute if second condition is true
• Not run if ltltconditiongtgt is false
• Else
• Code to execute if second condition is false
• Not run if ltltconditiongtgt is true
• End If
• End If

35
Nested If..Then..Else..End If

• You can next If statements in each other two
  possible ways
• If ltltconditiongtgt Then
• Code to execute if true
• Not run if ltltconditiongtgt is false
• ElseIF ltltconditiongtgt Then
• Code to execute if second condition is true
• Not run if ltltconditiongtgt is false
• Else
• Code to execute if second condition is false
• Not run if ltltconditiongtgt is true
• End If
• Both are correct but depending on the situation
  one or the other MAY make more sense to use

36
IsNull and Not IsNull

• IsNull (Object) is a very handy built in function
  of VBA which returns the value True (-1) if the
  object is completely empty and False (0) if it
  contains anything at all, including a zero length
  string
• Can be used with queries by passing a field name
  through to the function either as a criteria or
  as part of a calculated expression
• Can be used in code with variables or objects
  available on the active form/report
• Using Not with IsNull returns a true value if
  there is data..
• Not IsNull(Elizabeth) returns True because of a
  double negative
• IsNull(Elizabeth) False
• Not False True
• Not IsNull(Elizabeth) False
• Can use in combination with the If statement to
  see if a particular control/value is null.Lesson
  4