Return to IFS 110 Notes!

Tutorial 6
Customizing the User Interface  

Methods and Properties

p   Property - A named attribute of an object that defines a certain characteristic, such as size, color, or a behavioral aspect, such as whether the object is visible or whether it is automatically centered. (adjective)

p   Method - A procedure that acts on an object. (verb)

n   Examples: Undo method, SetFocus method

n   Methods are somewhat similar to macro actions

Collections and Classes

p     Class - The definition for an object. It includes the object’s name, its properties and methods, and any events associated with it.

n    a class is all things that define a particular object (the chair, for example).

p     Instance - A new object with all of the characteristics defined by a class.

n    A text box for example.  The definition of what a text box is includes its properties, methods, and events.  When a textbox is created on the form, this is an instance of the text box object.

p     Collection - An object that contains a set of related objects (objects of the same class).

n    A collection is simply a set of objects that are of the same class.  In Access, there is a collection of all objects on a form, a collection of all forms in the database, a collection of all queries in the database, etc. 

Data Event Properties

p   AfterDelConfirm – Form event occurs after confirming record deletions and records are actually deleted, or after deletions are cancelled

p   AfterInsert– Form event occurs after new record is added

p   AfterUpdate – Form and Control event occurs after a control or record is updated, occurs when record or control loses focus  

Data Event Properties

p    BeforeDelConfirm – Form event occurs after one or more records are deleted, but before Access displays dialog box asking to confirm or cancel deletion, event occurs after Delete event

p    BeforeInsert– Form event occurs when first character typed in new record, but before record added to database

p    BeforeUpdate – Form and Control event occurs before a control or record is updated, occurs when record or control loses focus or when the Save Records is clicked on Records menu bar or toolbar

Data Event Properties

p   Change – Control event occurs when content of text box or text portion of combo box changes, occurs when character typed in control or change the Text property using macro or VBA

p   Current – Form event occurs when focus moves to a record (current record)

p   Delete – Form event occurs when a  record is deleted, but before deletion is confirmed and actually performed

Data Event Properties

p   Dirty – Form event occurs when contents of form or text portion of combo box change, also occurs when move from one page to another using tab control

p   NotInList – Control event occurs when value entered into combo box isn’t in the combo box list

p   Updated – Control event occurs when an OLE object’s data has been modified

Canceling Default Behavior Following an Event

p   One of the events for which Access runs the event procedure before the default behavior takes place is the BeforeUpdate event.

n   Others include Apply Filter, Dbl Click, Delete, and No Data events

n   Use the BeforeUpdate event in frmTimeCards form to test an entry in TimeCardDate field.

p  If date entered not a Saturday, entry canceled.

p  Use DatePart function to test day of week part of date field

§    Returns an integer for each day of the week

DatePart Function arguments

DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

p    Interval – required string represents interval of time returned

p    Date – required is date being tested

n    Brackets [] indicate optional arguments

p    firstdayofweek – optional used to specify first day of week, if don’t want Sunday used

p    firstweekofyear – optional used to specify first week of year, if don’t want week of January 1 used

n    Access sets Sunday as day 1 so Saturday is day 7

Validating Data Using an Event Procedure

Private Sub  TimeCardDate_BeforeUpdate(Cancel As Integer)

    If DatePart("w", TimeCardDate) <> 7 Then

         Cancel = True

         MsgBox "You must enter a date that corresponds to a Saturday."

    End If   

End Sub

p    Note: If the DatePart condition is true, the Cancel and MsgBox statements will be executed.  If the DatePart condition is false, nothing will happen. 

The DoCmd Object

p    DoCmd Object - An object that carries out macro actions in VBA procedures.

n    Most macro actions have corresponding DoCmd methods, and the arguments correspond to the action arguments found in the macro window.

Syntax DoCmd.OpenForm object

DoCmd.OpenForm (FormName, View, FilterName, WhereCondition, DateMode, WindowMode, OpenArgs)

p    FormName only required argument, remaining arguments optional

n    If optional argument included, must include a comma for each argument that precedes optional argument

p   DoCmd.OpenForm “FormName” , , , WhereCondition

OpenForm method arguments

p    FormName – string expression valid form name

p    View – intrinsic constant (word that has a particular meaning in VBA)

n    acDesign – (Design view)

n    acFormDS – (Form Datasheet view)

n    acFormPivotChart – (Form Pivot Chart view)

n    acFormPivotTable – (Form Pivot Table view)

n    acNormal – (Form view [default])

n    acPreview – (Print Preview)

p    FilterName – string expression valid query name

p    WhereCondition – string expression valid SQL where clause without the word “where”  

OpenForm method arguments

p    DataMode – intrinsic constant

n    acFormAdd – (accepts only new records)

n    acFormEdit – (allows entering, editing, deleting)

n    acFormPropertySettings – (based on form’s property settings [default])

n    acFormReadOnly– (Form is read-only)

p    WindowMode – intrinsic constant

n    acDialog – (Modal and Pop Up properties set to Yes)

n    acHidden – (Form open but hidden from view)

n    acIcon – (minimized as small title bar at bottom)

n    acWindowNormal or acNormal– (opens in mode set by its properties [default])

p    OpenArgs – used to set OpenArgs of form

n    Specify particular record to move to

If Statements

p   If…Then - The If…Then construct lets you specify a condition to be evaluated, and then specify the action(s) to be taken if the condition is true. No action is taken if the condition is false.

p   If… Then… Else - The If…Then…Else construct lets you specify a condition to be evaluated, and then specify the action(s) to take if the condition is true, and the action(s) to take if the condition is false..

Select Case and Nested If

p     Select Case statement is just like the If… Then…Else statement except that it allows selecting from several different options.

n    Select Case statement could be written as nested If statements but the code would be more difficult to read. 

Select Case grpForms          
Case 1

                        DoCmd.OpenForm “frmDegrees”

     Case 2

                        DoCmd.OpenForm “frmDepartments”

     Case 3

                        DoCmd.OpenForm “frmEmployees

End Select  

Select Case and Nested If

If grpForms = 1 Then
      DoCmd.OpenForm “frmDegrees”
Else
      If grpForms = 2 then
                DoCmd.OpenForm “frmDepartments”
      Else

          If grpForms = 3 then

                   DoCmd.OpenForm “frmEmployees

          EndIf

    EndIf

EndIf

Adding an Event Procedure to the FrmDataSwitchboard Form

p    converted macro on page AC 247 (Figure 6-7)

p    Notice that the code converted to a series of If… Then statements enclosed within a With statement.

n    the code could also have been written as a Select Case statement yielding the same results. 

p    With statement lets you perform series of statements on specified object without restating the name of the object

p    The With statement in this example uses the CodeContextObject which means that all the statements that lie between the With statement and the End With statement can access this object. 

CodeContextObject property

p    This property’s purpose is to determine the object in which a macro or VBA code is executing.

n    In this instance, CodeContextObject property represents the frmDataSwitchboard form

n    In the converted macro, the CodeContextObject property is used because the mfrmDataSwitchboard_grpForms_AfterUpdate() procedure is a function

p   A function in a standard module can be called from properties, expressions, or even other procedures

§    As a function in standard module, mfrmDataSwitchboard_grpForms_AfterUpdate() procedure can be run from any form in the database, and the CodeContextObject property will then represent the form that called the function

With Statement Example

lblFirstName.BackColor = vbRed

lblFirstName.Caption = “First Name”

lblFirstName.BorderStyle = Solid

p     Object name (lblFirstName) specified 3 times  

With lblFirstName

    .BackColor = vbRed

    .Caption = “First Name”

    .BorderStyle = Solid

End With  

p     Object name (lblFirstName) specified once  

Adding an Event Procedure to the FrmDataSwitchboard Form (Code If)

If ( CodeContextObject.grpForms = 1) then

   Statement here…..

End if

If ( CodeContextObject.grpForms = 2) then

   Statement here…..

End if

And so on….

Adding an Event Procedure to the FrmDataSwitchboard Form (Code Select Case)

Select Case CodeContextObject.grpForms

              Case 1

                     Statements here

              Case 2

                     Statements here

              And so on……

       End Select

Introduction to Menus and Toolbars

p    techniques work in all Office applications.

p    Can move the menus and toolbars

p    One of the great features of windows-based applications is that they allow working in the manner that is most comfortable (short-menus)

n    some people prefer to have their standard toolbar down the right or left of the screen.

n    The following is an example (on next slide) where the user, for whatever reason, prefers to have the menu on the right side of the screen and the standard toolbar on the left of the screen. 

p   Some users believe this gives them quicker access to the menu and toolbar.  Again, this is simply personal preference.

Creating and customizing Menus and Toolbars

p    Restrict access to certain features (security reasons) so users cannot

n    Inadvertently delete data

n    View sensitive data

n    Modify design of an object

n    Modify macros or VBA code

p    Note: A simple way to do this is to customize existing menus and toolbars, or create new ones

n    Create custom toolbar containing only buttons users need to work in database

p    Access stores customized toolbars in file named (System.mdw) – contains preference and security information  

Startup Properties

p   Startup properties - control user access to areas of database

n   Properties set in Startup dialog box

n   Properties apply only to database in which they are set

n   Changing Startup properties for database does not override property settings for a custom toolbar, menu bar, or shortcut menu created for a specific form or report

Startup Properties

p     Application Title – program name displays in database title bar

p     Application Icon – bitmap or icon file displays on left side of title bar

p     Menu Bar – default menu bar

p     Allow Full Menu – use of all Access menu commands

n    If not selected, predefined subset of full built-in menus shown

p   Subset of menus doesn’t include menus and commands enabling users to change design of database objects

n    Clearing option also disables toolbar buttons corresponding to disabled menu items

p     Allow Default Shortcut Menu – specify to allow display of built-in shortcut menus

n    clearing Allow Full Menus does not disable shortcut menus

p   This option must be deselected to keep users from changing database design

Startup Properties

p    Display Form/Page – form or data access page to display when database opens

p    Display Database Window – deselecting check box means database window not visible

n    Can see it by pressing F11 function key

p    Display Status Bar – deselect to hide status bar

p    Shortcut Menu Bar – used to specify custom shortcut menu

n    Should clear Allow Default Shortcut Menus

p    Allow Built-in Toolbars – deselect to disable built-in toolbars  

Startup Properties

p    Allow Toolbar/Menu Changes– deselect to lock toolbars

n    Disables feature that allows you to right-click toolbar

n    Disables Toolbars command on View menu, and the Close button on toolbars

p    Use Special Access Keys – click Advanced button so option is visible

n    Deselect option to prevent users from pressing F11 key to show Database window, pressing Ctrl + F11 to toggle to Database window, pressing Ctrl + Break to enter break mode in VB module, or pressing Ctrl + G to display Immediate window

Disabling the Bypass Key

n   Access database programmers refer to Shift key as the bypass key

p  Disabling bypass key is common method for securing database (savvy users familiar with security measure)

p  Can disable bypass key with VBA code, more difficult for users to undo

§    Trick is VBA code must include method to enable Shift key so authorized users can get into design and programming areas of database

§    One method, to trigger code that enables and disables bypass key is to use transparent command buttons

§    Another method is to use a series of keystrokes

§    AutoKeys macro provides this functionality (global key assignment)

Disabling the Bypass Key (steps)

1. Copy code from help files that include a procedure to disable the bypass key (Shift key that allows you to open a database and skip Startup properties).

1.  Convert this procedure to a function.

2.  Add argument to function that will tell function how to set the “AllowBypassKey” property (True or False).

diagram to help understand what this function does. 

Note: This diagram indicates that a value is passed to the function (either True or False).  The SetByPassProperty function then calls the ChangeProeprty function which actually changes the AllowByPassKey property to True or False based on what is passed to the function.

AutoKeys Macro Group

p     AutoKeys Macro Group - A macro group created to assign custom commands to key combinations.

n    When key combination pressed Access searches for an AutoKeys macro, runs macro assigned to those keys

n    set a key sequence to either set bypass key on or off.

n    will use Ctrl+any function key combination.

p   Ctrl-F4 will set the bypass key so that it works.

p   Ctrl-F5 will disable the bypass key. 

p     Note: set up startup properties to control how the database opens, what the user sees, and what they have access to in the Database environment. 

n    Often you do not want user to be able to bypass the startup properties (i.e., you don’t want them outside the environment of your database).   Disabling the bypass key keeps the user from being able to “jump out” of the database and into the Access environment.

Splitting the Database

p     back end - will contain the tables (network drive)

p     front end - will contain all the remaining objects  (local drives)

p     Note: Back end and front end are linked to each other. Users will see only front end which is the “window” to the data which resides in the back end.

Advantages of Splitting Database

1.  Keeping front end on user’s computer minimizes network traffic and improves performance

2. The user of the front end can work on the front end making changes to its objects, but only that particular user will see those changes without affecting the database of other users

3. Database developer can work on the back end without interfering with the user of the front end.

p     Note: In a server environment, the front end would be copied to user computers and the back end would remain on the network server.  Anytime the back end or front end is moved the tables will need to be linked once again to insure that access is possible.

IFS 110 Computer Assignment 6

Return to IFS 110 Notes!