Print Page | Close Window

Form_MtoMViaCheckBoxArray

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=553
Printed Date: 13 Dec 2017 at 9:57am


Topic: Form_MtoMViaCheckBoxArray
Posted By: A.D. Tejpal
Subject: Form_MtoMViaCheckBoxArray
Date Posted: 28 Dec 2010 at 12:31pm

Form_MtoMViaCheckBoxArray
Explanatory Notes

    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.

Version: Access 2000 file format.

References: DAO 3.6

Download >>:  http://www.rogersaccesslibrary.com/forum/uploads/37/Form_MtoMViaCheckBoxArray.zip - uploads/37/Form_MtoMViaCheckBoxArray.zip
 
 


-------------
--

Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com



Print Page | Close Window