This assignment will exercise your skills with VBA objects, queries and recordsets. You will create a form and program as shown in the figure below using the two tables provided. You are to turn in your assignment as an Access 2002 MDB file on a floppy or Zip disk labeled with your name.
The general purpose of the assignment is to create a tool for maintaining the list of students assigned to the teams for the big semester-end project. With the tool we can add and remove students from teams using an intuitive and friendly interface. To help with the assignment, and to make the emails of your potential team members available for your use later, the official UT class roster file has been included as an Access table. All confidential information has been removed from the roster.
You will begin by creating a form called frmAssignment4. It should have two list boxes and a combo as shown above. Use Maintain Team Assignment as the form's caption. Title the form Team Assignments. Adhere to class standards for the form.
Teams names are found in the table tblTeam. Use this table for the combo. Student information is in tblStudent, however the names must be reformatted to conform with those shown in the figure. Team assignments must become data in the roster table, as described later.
Users can assign and remove students to the teams, and see your form confirm these actions by adding and removing names from the two list boxes appropriately. They can perform these actions by highlighting a name then pressing the appropriate button, or simply double-clicking a name.
Do not give messages to the user. They would interfere with the smooth flow and ease of use of the form. For example, when a user attempts to assign a student to a particular team and the student is already assigned to that team, do not give a notice or error message. Just do not make the assignment a second time. Because the interface is visual, the message is unnecessary.
Import the class roster as an XML file for this assignment. You are expected to turn the XML syntax into a roster table (tblStudent) with an autonumber pk and a field for the student team assignment: fkTeamID. Your program will need to update the fkTeamID field of tblStudent. Other fields should not be changed in any way. Take this literally and do not change the Name field.
As another XML exercise, download and import the table of team names (tblTeam) . The purpose of this step is to acquaint you with a useful method of acquiring data into Access from the Internet.
Start by writing a list of steps or tasks, or drafting pseudo code for the program. This will help you learn more about the job before you begin programming later. There is very little VBA programming necessary to achieve the smooth interface.
Technical information about combo and listbox objects can be found in the course packet and on the course page. Recordset programming (edit and update) is necessary for maintaining the fkTeamID field of the roster table (tblStudent), and that information can be found in the textbook, course page and course packet. The Access Help system has good examples and information on both these topics.
Because functions can be used in queries in place of fields, it is strongly recommended you write a function for reformatting (for showing) the roster names. It will be useful in the left listbox and in the right listbox.
Graders must be able to assign a student by double clicking the student name (left list) or clicking the Assign button. And they must be able to remove students by double clicking student name (right list) or clicking the Remove button.
Technical information is available in the course packet, course page, textbook, and the Help system on recordset programming. You will only need to use the following recordset steps: locate the record for the particular student selected in the interface, edit the fkTeamID to reflect the new assignment, and update the record.
To have the team names available with the roster information you will need to perform a joined query as the RowSource of the left listbox. As you hopefully learned in your previous MIS courses, there is more than one way to join two tables. You will need a right join for the left query. Consult your textbooks from MIS325, or use the Access Help system for join information.
The right listbox also needs a query to select only those students whose team names match the name in the team combo. Do not forget to requery the listbox. It will be necessary after each assignment change.
Do not use stored queries for either listbox. You can enjoy all the convenience of a stored query (datasheet, QBE or query design interface, and SQL window) without the baggage of another element in the database. Place your cursor in the RowSource of the listbox and press Ctl+F2. This results in an SQL string that is easy to maintain and is a property of the listbox object itself, so it is more portable than a stored query.
Write a function to reformat the student names for showing them in the left and right listboxes. The query can address the function as it would a calculation field. You may remember, for example, that a function can be included in a query. Assuming you want to call your function PrettyNames() and the new query field Student, then add the following field to your queries: Student: PrettyNames(Name). Graders will look for function PrettyName() in the standard Module named modPrettyName. It must have comments per course standards.