Homework Assignment 2

About this homework

This assignment will exercise your skills with Access 2002 Objects. It will test both your Access programming skills and your ability to follow a high-level software requirement. You will create a simple form, program a minimal logon security system, and program basic navigation buttons according to a one-page statement of "high-level requirements," as explained below. You are to turn in your assignment as an Access MDB file named Assignment2.mdb on a floppy disk labeled with your name and the assignment number as you did for Assignment 1.

The "high-level requirements" for this homework will leave you some wiggle room on how you accomplish the software design. Do not try to "improve" the application, however, as this will take additional time and also lower your grade. Follow the requirement statement carefully and do not omit anything. For the best grade you must adhere to the class Software Standards.

Required Programming

Your form must be highly user-proof. Prevent users from entering bad data. Graders will try to break your form by omitting data and entering all manner of inappropriate values. You are expected to intercept errors, issue your own error messages when possible, and prevent the data from being written into the table. Check every field for reasonable data. Express error messages clearly using the best MsgBox features for these kinds of messages. It is not permitted to simply tag the fields as "Required" in the table design for having all fields filled in. You will be penalized if your table has the fields required.

Your form must be highly user-friendly. For example, when users click cmdAdd you must move the cursor to the first field. Provide for multiple records to be added without requiring users to click cmdAdd for each new record after the first. When users enter data for sex and party they must be supported to enter only part of the field; for example ref for reform party, m for male, etc.

Your form must be highly error-proof as described in the requirements, and support users easily entering data by having the correct order of tabs. Provide combos for entering sex and party and be sure they have defaults. Do not allow users to enter values beyond those in the Requirements, or to delete a value and leave a combo empty. More information about good practices with combos can be found on the course site.

Program the navigation buttons (include Add, Save, Undo and Delete) and the navigation combo. To the right of the combo place a text box that contains record position information ("1 of 20"). Make the four navigation buttons smart (no error messages), and do all programming for the four buttons in the Form_Current module. Create the table and form after reading the Requirement Statement. Be sure and have a few sample records with credible data for the graders to see.

About Requirement Statements

An early step in developing an application should always be to establish the "high-level requirements." For Assignment 2 this has already been done. They are presented in a concise, written statement that serves as an overall roadmap for the project. In an actual workplace setting, the high-level requirements should always be discussed with intended system users, revised if necessary, and agreed upon before any programming starts.

The attached Requirement Statement for Assignment 2 serves two purposes. First, you will use it as the basis for Assignment Screen shot2. Second, it is a model for the kinds of statements you will be asked to write for future class assignments and your semester project. You will be graded on: (1) how clearly your requirement statement describes the intended application; (2) how carefully the project is planned; and (3) whether your application agrees with the written requirements. You should always get to the point, stay organized, and check your spelling.

Your applications will benefit enormously from the time you invest in expressing the requirements precisely, since you, the customer, programmers, and quality assurance managers will all be reading from the same sheet of music. Guidelines for writing requirements on future assignments are on the Web.

The figure on this page is intended to help communicate the controls needed on the contributions form. If you need more information on combo and list boxes than is available in the textbook, check out the combo and list box information mentioned above.

Requirement Statement for Assignment 2

Project Goal

The goal of this project is to develop a Windows 2000 software program for recording contributions to the reelection campaign of Mayor Maxwell Minimum. By law, registered candidates running for local political office must disclose all contributions to the Municipal Records Office.

Application Description. The stand-alone application will be written in VBA using Access 2002. It will provide an interface for recording financial contributions and a database for keeping track of contributions to Mayor Minimum's reelection campaign. Because volunteer political campaign workers will be the primary users, it will be necessary to provide for security. Two tiers of users will be supported: Volunteers (data entry functions only), and Supervisors (data entry, plus change and delete contribution records).

Component Descriptions

User Interface

An on-screen data entry form (Minimum Contributions) will support add/change and delete functions for the contribution records. Before the form is available to a user, a security code must be entered to authorize the level of usage. The codes are V for volunteer and S for supervisor security levels. All data fields in the database will be visible on the form to either class of users. Because of city disclosure regulations, contributions which have incomplete information cannot be accepted, and therefore must not be entered into the database.

Record navigation control buttons will support browsing through the contributions in the same sequence that they were entered into the database. These controls are First, Previous, Next, Last, Add, Undo, Save and Delete. Another button will close the form.


All fields must be entered for each contribution received. The fields of information are: First Name, Last Name, Political Party Affiliation (Democrat, Republican, Reform, Independent, DK), Sex (M,F), Date of Contribution, and Contribution Amount.

Quality Assurance

A test suite will be devised to verify that data entered into the application are stored and retrieved correctly. Working from a printed test suite, testers will enter contributions into the application and then exit the program. After restarting the program, they will go through a checklist and determine whether the data were preserved accurately across executions of the program. A QA supervisor will verify that each tester is working appropriately by auditing their completed test protocols.


Campaign Treasurer (name)________________ (signature)________ (date)_____
Software Development Mngr (name)__________ (signature)________ (date)_____
Quality Assurance Mngr (name)_____________ (signature)________ (date)_____

Note to MIS333k students: the Quality Assurance section above suggests how you can test your work, but you are not required to test and turn in a report or checklist. The QA section is intended to give you experience reading requirements for QA testing. Similar sections must appear in all of the requirement statements you are later assigned to write.

VBA tips for Assignment 2

None of the tips that follow below are required. You can program the assignment in a number of acceptable ways. Results count, pay attention to requirements. Here are some programming ideas.

Plan the logic you will need. If you want to learn more about how to program the assignment, refer to the Course Packet pages. Assignment 2 has three logical tasks. The first two can be combined into one VBA event procedure (On_Open()).

Task 1: get user security code (S or V),
Task 2: adjust frmContribution properties to the user's security level.
Task 3: check user input for validity and report invalid entries back to the user.

As part of this exercise you will give your own error messages. You job is to check the input and notify users in your own words. It is not enough to simply tag the fields as "Required" in the table design, and you will be penalized if your do.

If you are new to VBA then work on each task separately and combine them into the event procedure On_Open() after you are confident you have written good logic to accomplish the tasks. Do not expect VBA to allow you to test drive the On_Open() proc without actually running the form. That's OK, but it is often easier to develop VBA in a non-Form module (a standard module) as I do in class. Here are some tips on the tasks that we may have time to discuss in class.

Task 1 uses the following intrinsic VBA functions: InputBox and Ucase.
Task 2 resets these form properties: AllowEdits, AllowDeletions, and this button property: cmdDelete.Enabled
Task 3 uses event procedures and then interprets the data entered by users.

Task 1: Hints for the input logic

InputBox("prompt", "title bar") returns the characters users type. Store the result in a variable you call "strSecurity". We need to filter out some garbage the user may type, and allow the user a way to cancel the input when they don't know what they should do. Here it is.

InputBox("Please enter your security code.", "Minimum Security")

Try it out. Remember if you are working in the On_Open() proc you will have to toggle back to Access from VBA and run the Form. In class, I sometimes work in non-Event procs to make it easier to try out primitive logic. I just click the run button while the I bar is in the proc.

Next, surround the InputBox call with a control structure that will not allow users to enter non-security codes. They can cancel by clicking Cancel on the form. The DO…Loop Until() control structure is ideal for this. We want to accept only "S" or "V" or "" ("" means the Cancel button on InputBox was clicked) and the DO will control the logic nicely.

    strSecurity = InputBox(per above)
Loop Until (strSecurity="" or strSecurity="S" or strSecurity="V")

This does most of the task, but I would filter the InputBox() data to force an uppercase letter by substituting this line for the middle line above:

    strSecurity = Ucase(InputBox(per above))   ' see below

With the addition of Ucase(), users could type "s", "V", "S" and so on. Questions about the intrinsics InputBox(), Ucase()? While in VBA place the I bar over one of them and press F1.

Task 2: Hints for the form property logic

At this stage you can assume strSecurity has "S" or "V" and now you will need to adjust the form accordingly. You were instructed in the Requirement Statement to not allow volunteers to edit existing contribution records or delete them, but supervisors could do these.

The relevant form properties here are AllowEdits and AllowDeletions. Your job is to address these properties differently when strSecurity is "S" or "V". The control structure Case Select…End Case is excellent to use here.

You already know form properties can be set to Yes/No at design time. We need to change the two form properties AllowEdits and AllowDeletions at run time. The syntax we will use inside the event proc Sub On_Open() is as follows:

Me.AllowEdits = True
Me.AllowDeletions = False

Another user interface matter is the Delete command button. It should be disabled for volunteers and enabled for supervisors.

CmdDelete.Enabled = False

Planning for Final Logic (Tasks 1 and 2)

You have to integrate the pieces presented above into friendly logic. Here is the pseudo code I might use.

Loop while security input
Ask for security password input
    If (Supervisor or Volunteer or Cancel)  exit loop
    Ask if user wants to try again
    If not try again then abort form open
End loop
Case Supervisor
    Allow data entry, record deletions, edits to stored records
Case Volunteer
    Allow data entry
Case Cancel
    Abort form open

Planning for Validity Checking (Task 3)

You will make extensive use of event procedures available in Access 2002 for this task. Start by programming one small error check for a blank first name field. As you gain more understanding, add the error checks to the other fields.