New Sample: Form_MtoMViaCheckBoxArray

by AD Tejpal

This sample db demonstrates use of check box array for populating a junction table serving many to many relationship between clients and training courses. Data is stored in normalized manner in tables T_Clients, T_Courses and T_ClientCourses. No temporary table is needed.


Two styles of user interface are demonstrated:

1 - Normal Style (Using Virtual Records Matrix):
-------------------------------------------------

1.1 - Client particulars are displayed in the parent form while all available course options are displayed in a subform, as a matrix of virtual new records.

1.2 - As and when the user clicks a check box into selected state against desired course in the subform, the row in question gets converted into a freshly entered actual record in the junction table.

1.3 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted. This is accompanied by a virtual new record getting displayed in lieu of the recently deleted actual record (At all times, the subform matrix continues to display all available course options).

1.4 - Total number of courses opted for current client as well as the grand total for all clients are also displayed in the parent form

2 - Spreadsheet Style - Datasheet View (Using Crosstab Query):
-----------------------------------------------------------------

2.1 - Adequate number of check boxes along with child labels are inserted in the form at design stage. This is a one time exercise, using the subroutine P_AddControls().

2.2 - Each check box column of this datasheet form (used as a subform) represents an individual course. A crosstab query, based upon Cartesian join between tables T_Clients and T_Courses, serves as the record source.

2.3 - As and when the user clicks a check box into selected state in desired course column, a record with appropriate values for ClientID and CourseID gets added to the junction table.

2.4 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted.

2.5 - First three columns display client name, list of courses opted and total number of courses opted respectively. These three columns are frozen so as to always remain in view while the user moves across check box columns representing individual courses.

2.6 - Third column depicts total number of courses opted for each client. Bottom cell of this column shows the overall total courses opted for all clients. Overall total is also depicted within parenthesis in header caption for this column, so as to always remain in view despite vertical scrolling.

2.7 - For each course, total number of clients who have opted for it, is depicted within parenthesis in the caption for respective column header, so as to always remain in view despite vertical scrolling.

You can find this sample here:
http://www.rogersaccesslibrary.com/forum/Form-mtomviacheckboxarray_topic553.html

.