Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Form_MtoMViaCheckBoxArray
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Form_MtoMViaCheckBoxArray

 Post Reply Post Reply
Author
Message Reverse Sort Order
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Form_MtoMViaCheckBoxArray
    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

 
 
--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.