Administrative Computer Services
Committee Information Tracking System
CTS Detailed Software Design
1. Introduction
This document describes in detail the software design
of the Committee Information Tracking System (CTS).
CTS is a software project of the Office of the Vice Provost for Research
and Graduate Education and Administrative Computer Services intended to
facilitate the management of information related to university committees.
The intention of this document is to provide sufficient
information (together with other CTS documentation) so that an Access programmer
can recreate or maintain the CTS system. The sections below describe CTS's
software design by listing and describing how all CTS features are implemented.
2. Data Model Design and Queries
The first step in the software design of CTS was to develop
a data model based on general user requirements. The data model and its
development process are described in the "CTS Data Model and General
Requirements" document (http://acs.neu.edu/~ramon/CommTrackSys/datamode.htm).
All table relationships shown in the data model document are set to the
cascade delete and update mode and to enforce referential integrity. Figure
1 shows an example of this configuration for the one-to-many relationship
between the Committee and Membership tables.
Figure 1 - Table Relation Settings
This section expands the data model documentation by describing the queries used in CTS. The main reason for using queries is to implement data views (JOINS) containing columns from two or more related base tables. In some cases, a query is used instead of a single table to reduce network traffic (according to the Access 2.0 documentation) since only the column(s) included in the query are transmitted over the network.
2.1 Explicit Queries
Queries in CTS are primarily editable "select" SQL statements used to display and update information on separate panes on CTS screens. Queries are also used as the data source for CTS reports. Most of the queries are explicit Access 2.0 queries, listed in Figure 2, while others are implicit queries (SQL SELECT statements) declared as the data source for screen list controls or to perform especial tasks. Implicit queries used for controls are documented in the "Linking CTS Screens" Section below.
Figure 2 - CTS Explicit Queries
A detailed definition of each of the queries listed in Figure 2 was generated using the build-in documentation tools in Access 2.0 and is given HERE (12 pages) in postcript format. Here are two links to poscript readers: PostScript Viewer for Windows - shareware, and Obtaining Aladdin Ghostscript- free. I might be able to help if you with the installation of either one with your web browser
2.2 Implicit Append Queries
CTS uses one implicit append query that is executed automatically
when the user adds a new meeting record in the Meeting_Main screen. This
query is an INSERT SQL statement executed by the event handling (OnClick
event) code of the + push button. The following is an extract of the code.
3. Naming Conventions
In general, CTS uses multiple word names, with each word capitalized and joined to other words by an underscore, e.g. "Committee_Main". The naming rules used and the components they apply to are listed below. In this document the word "screen" means the same as "screen form" and it corresponds to the "form" component in Access 2.0.
3.1 Table and Column Names
Table and column names were given based on the type of
information they contain and familiarity to the end users. The table and
column names were specified in the "CTS Data Model and General Requirements"
document (http://acs.neu.edu/~ramon/CommTrackSys/datamode.htm). In
the case of the "Person" table, the name reflects the expected
name for this table in an future enterprise-wide data model.
Column names that may be used in more that one table are
prefixed with the name of their table, e.g., Committee_Name and Task_Name.
Many of the column names in the "Person" table correspond to
column names in the current HRM information system. In all tables, the
column description attribute provides a short description and purpose of
the column and, if applicable, and for the Person table the corresponding
HRM source column name. For all tables, columns used as primary, secondary,
or foreign keys have abbreviated capitalized names and are declared at
the top of the column name list.
3.2 Screen Names
Screen names start with one or more of CTS's functional
area words (Committee, Person, Membership, Meeting, Meeting Attendance,
Task, Meeting Detail, and Task Assignment) followed by a qualifier indicating
its role in the user interface:
Here is a list of CTS screen names:
3.3 Query Names
Query names are made up of the table names that take part
in the query definition. These names contain multiple capitalized words
joined by underscores. The first word in the query name indicates the "parent"
or controlling table (or query) followed by the names of other tables or
queries in the definition. The following qualifying words are used in query
names to indicate the purpose of the query:
3.4 Other Component Names
Other major CTS components are named using "action" words that describe the purpose of the component, and wherever appropriate, one or more words describing the type of component. The following is a sample list of other CTS component names:
4. General Screen Layout and Standard Features
Most CTS screens present information using a 3-pane style.
The first pane, on the top left of the screen, gives a list of item names,
e.g. committee names. The second pane, on the top right of the screen,
gives detail about the item selected (highlighted) on the first pane, e.g.
detail information about a committee. The third pane, on the bottom of
the screen, gives a list of records related to the item selected (highlighted)
on the first pane, e.g. a list a committee members.
In 3-pane screens, the first pane is an "unbound
list box control" that uses a query as its data source and contains
at least two columns, one of which is hidden from view and is used to control
(bound) the other panes on the screen. The contents in each pane are automatically
updated based on selections made in related panels in the screen. This
feature is implemented by using the parent/child link control attributes
and is documented in the "Linking Screen Controls" section below.
There is one 2-pane screen, Person_Main, in which the
top pane contains controls (text boxes) directly bound to the Person table
columns.
CTS gives a brief description, at the bottom of the screen,
of the currently selected field when the cursor is on the second or third
panes. This feature is standard in Access 2.0 and is implemented by filling
in the description attribute of each table column in the table design mode.
CTS as an Access 2.0/Windows 3.1 application automatically
implements standard commands such as:
5. Linking Screen Controls
Figure 3 - ListBox Data Source Definition
All 3-pane screens contain controls synchronized to a
list-box in the first pane. The data source for the list-box is either
a predefined query (e.g. Committee_Name_List) or an explicit query containing
two or more columns from a base table (e.g. Committee). Figure 3 shows
an example from the Committee_Main screen. CTS Screens with one or two
panes are standard Access 2.0 screen forms created using the forms wizard.
The name of the list-box control, e.g. "Selected_Committee_ID", contains at all times the selected value of a hidden column in the control (e.g. COMM_ID in Figure 3). The section "Custom Screen Behavior Implementation" below describes how to hide this column. This hidden value is used as the master "linking value" for other child controls on the screen (see Figure 4).
Figure 4 - ListBox Control Linking Value
The purpose of the "linking value" is to have Access 2.0 automatically synchronize the contents of all child controls on the screen. This is implemented by setting two child data control properties. The first is the "Link Master Fields" data property, which should be set to the name of the control containing the source "linking value" (e.g., Selected_Committee_ID). Figure 5 gives an example of this for the "Committee_Detail_Subform" child control using the control name shown in Figure 4. The second setting is the "Link Child Fields" data property, which should be set to the name of the column (in the control data source, e.g., its source query) that matches the name of the hidden column corresponding to the source "linking value", e.g. COMM_ID in Figures 3 and 5.
Figure 5 - Link Master Attribute Setting
Detailed definitions of all screens and controls in CTS generated using the Access 2.0 built-in documentation tools is provided HERE in postcript format (122 pages).
6. Linking CTS Screens
CTS uses three mechanisms to link related screens. The
first and simplest one is to simply call the desired screen from the "OnClick"
event handling code of a push button. This mechanism is used in one instance
in CTS to call the "Reports" selection screen. The following
is the code used:
The second mechanism is used to call a screen, from the
"OnClick" event handling code of a push button, with a criteria
parameter specifying a subset of data to display. This mechanism is used
in three instances in CTS. The first instance is to open the Person_Main
screen from the Committee_Main screen, the second and third instances are
used to open the Committee_Membership_Details screen from the Committee_Main
Screen and the Person_Main screens. The following is the code used to call
the Person_Main screen:
And for the Committee_Membership_Details screen the code is:
6.1 Implicit Queries
The third mechanism is to call the screen (e.g., Task_Main) without parameters and have the called screen refer to the calling screen (e.g., Committee_Main) for a selection criteria value, denoted here as an implicit query. This mechanism is used to call the "Meeting_Main" and "Task_Main" screens by pushing the "Meetings View" and "Tasks View" buttons on the "Committee_Main" screen. This mechanism is used because the called screens are controlled by a list-box in the first pane and this list-box needs to be initialized prior to opening the called screen. As an example, here is the code used in the "Tasks View " OnClick push button event used to call the "Task_Main" screen.
Figure 6 - Row Source Setting for Screen Linking
In the called screen, Task_Main in the example above,
the row source data property (see Figure 6) of the list-box in the first
pane must use a criteria (WHERE clause) that refers back to the calling
screen. The following implicit query is used in the "Row Source"
data property in Figure 6:
Where [Task_Committee_List] is a pre-defined query as described in section 2 above.
7. Adding New Records
CTS allows the user to add new records to base tables
using a couple of techniques. The first and simplest one is to use the
default Access 2.0 "add record" wizard for pushbutton controls.
The user can fill in the record with new data which is saved automatically
by Access when the user exits the screen or navigates to a different record,
including a new one. This technique is used in the "Person_Main"
screen to allow the user to add new people to the Person table. The following
is the code automatically created by the control wizard for the "OnClick"
of the [ + ] pushbutton:
The second technique is to use the "RecordSetClone"
object of the screen's base table. Access 2.0 automatically provides this
object to which the new record is added. The following is the code use
to allow the user to add a new task in the Task_Main screen:
Using this technique, columns defined as "not empty"
in the base table definition, must be initialized with appropriate values
as shown in the code above for "Task_Name" and "COMM_ID".
The statement "SendKeys "{DELETE}", True" is used to
delete the "New task name " string used to initialize the "Task_Name"
column to force the user to enter a real task name.
8. Custom Screen Behavior Implementation
CTS uses several code snipets to control the behavior
of the user interface. This section describes where, the purpose and how
these snipets are used in CTS.
8.1 Auto-selecting the first item in a list box.
This behavior is implemented in the list-boxes used in
the first pane of 3-pane screens. The purpose of this behavior is to force
the other panes of the screen to display some meaningful information upon
entering the screen, e.g., present a non-empty screen to the user! This
behavior is implemented by forcing the list-box, e.g., Selected_Committee_ID,
to display its first item (pointed to by [ItemData](0)), which then forces
the other panes on the screen to display related information. The code
to do this is: =[Selected_Committee_ID].[ItemData](0), and is shown as
the setting for the "Default value" in the data properties of
the list-box in Figure 6 above.
8.2 Hiding columns in a list-box.
This behavior is implemented in the list-boxes used in the first pane of 3-pane screens. The purpose of this behavior is to hide from view the table key values and other information stored in columns in the list-box data source. The values in these hidden columns are used to link the list-box to other screen controls as described in the "Linking CTS Screens" section above. This behavior is implemented by specifying a zero width for the column(s) to be hidden. For example, Figure 7 shows this for the "Columns Widths" setting of the "Layout Properties" for the Selected_Committee_ID list-box. This setting has the columns widths in the order the columns appear in the source query (see Figure 3) separated by semi colons, e.g., "0 in;2.816 in" in Figure 7.
Figure 7 - Column Width Settings to Hide Columns
8.3 Modal vs. non-modal screens.
All CTS screens are modal, meaning that other CTS screens may be visible but not active at the same time. The user may only interact with one screen at a time and must close the currently active screen to gain interactive access to a previous screen. The reason for this design decision is to keep CTS's development simple. Non-modal screens that may perform database transactions, as CTS screens do, require significantly more programmatic control than modal screens. Screens are defined as modal by setting the "Modal" "Other Properties" setting to yes as shown in Figure 8.
Figure 8 - Screen Modal Setting
8.4 Updating list-box controls.
In 3-pane screens, the contents of the first pane (a list-box) are automatically updated when the user changes data in the second pane, such as a committee name. This is done by forcing the list-box to refresh its data upon leaving the control in the second pane. The following code snipet is used to handle the "OnExit" "Event Property" of the second pane (see Figure 9), e.g.,. the "Meeting_Detail_Subform" control:
Figure 9 - Event Used to Refresh List-Box Contents
9. CTS Distribution and Setup
The distribution and installation disks for CTS are created
using the SetupWizard in the Access 2.0 Distribution Toolkit (ADT). Besides
the CTS.MDB data and program file the only other file needed is the CTS.INI
file. The following are the contents of the CTS.INI file: