Report_TrainControlChart
Explanatory Notes

Version: Access 2000 file format.

References: DAO 3.6

Download File >>:  uploads/37/Report_TrainControlChart.zip

    (a) Excel workbook with pre-defined range names.
    (b) Excel workbook without pre-defined range names.

    Sample excel files (WithRangeNames.xls and WithoutRangeNames.xls) are located in the home folder that contains this sample db. First row of each data block carries field names.

    On opening the main form, and clicking the appropriate command button, the user gets the option either to load the sample excel file already available, or browse to and select any other excel file. In style (a), information pertaining to data blocks belonging to different named ranges as fetched from source file, is displayed in the subform at left. 

    In style (b) similar information is displayed, based upon programmatic detection of data blocks in excel workbook, matching the first field name provided by the user. For greater precision, if desired, the user can also optionally enter column count and name of last field.

    Amongst data blocks displayed in the subform, those required to be imported can be identified by clicking check boxes in column headed "Include". For items selected in this manner, the text box at right gets populated with a comma separated list of data blocks earmarked for import. Based upon user choice in the option group, this list is made up either of range names (applicable to style (a) only), or cell addresses or column addresses. 

    The list is editable, allowing the user to carry out any modification if desired. An option group provides a choice for the mode to be adopted for import action (i.e. whether as per pre-defined ranges or as per user specified cell or column addresses). 

    On clicking the appropriate command button, data blocks covered by above list get imported into access. For added convenience, command buttons have been provided for viewing the Imported data as well as the excel file - if desired. 

Supplementary Notes:
    1 - Names collection in excel workbook covers pre-defined ranges. Each element of this collection returns a Name object whose default property is Value. This default property returns range address. For example if the range object named R_DataBlock_1 covers cells C4:D7 on sheet DataBlock_1, Name object's value will return =DataBlock_1!$C$4:$D$7. For getting actual name of the range, we have to use Name property of Name object.

    2 - User defined address string using comma as separator between adjacent blocks (no spaces) serves as one of the arguments for import subroutine. Either of the three sample styles given below can be used (Orders_01 is the name of worksheet):
    (a) Range Names:  "R1,R2, -- etc"        - (Applicable to style (a) only)
    or
    (b) Address By Cells:  "Orders_01!B2:C12,Orders_01!E3:G13, -- etc"
    or
    (c) Address By Columns:  "Orders_01!B:C,Orders_01!E:G, -- etc"

    Note:  
    In (b) & (c) above, for smooth execution of DoCmd.TransferSpreadsheet method, avoid absolute addresses (Don't use $ sign anywhere)

    3 - Important:
    It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to hung excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of  DoCmd.TransferSpreadsheet is best carried out independently, after execution of automation code modules and closing of excel.

References:
    (a) DAO 3.6
    (b) Microsoft Scripting RunTime
    (For excel application, late binding has been used)

Version - Access 2000 File Format

    Original data is displayed in the subform at left. Based upon user selection of SeriesID and maximum allowed spacing between adjacent elements of a cluster, the values qualifying as members of various clusters are displayed in the subform at right. Start value for each cluster is also displayed in a separate column.

    Simultaneously, matching value elements in in the subform at left get highlighted in distinct color.

    Row numbers get displayed in keeping with the prevailing sort order and do not need any primary key to be available for the purpose. 

    If form's sort order is changed during user interaction, row numbers as displayed, get updated automatically so as to suit the new sort order in force.

    1 - Adding / Replacement of MVF value elements in target subform as per source subform.
    2 - Applying Form Filter on multi-value field as per reference multi-value field.
    3 - Sorting by multi-value field (bound column or displayed column as desired)

    Three styles are covered as follows:
    (a) Bulk appending of selected records (having multi-value fields) from one table to another.
    (b) Bulk updating of multi-value field elements (addition or replacement of values) in destination table based upon values held in source table.
    (c) Make table action covering multi-value fields based upon selected records in source table.  

    Setting of forced page breaks in subreport's detail section, though effective, suffers from the drawback that the subreport control on parent report gets forced to full page height. As a result blank space on main report goes waste. Though not recommended, this method has been demonstrated as the last option, just for academic interest.

    Note: 
    While using forced page break in subreport, it has to be ensured that CanGrow property of subreport control on parent report is set to Yes. Otherwise the subreport does not get displayed beyond first page. In fact what happens is that as a result of forced page break, the subreport control with CanGrow as Yes, expands suitably so as to span multiple pages as needed.    

    Following styles are demonstrated in the sample db:
    1 - A1: Single SubReport Style 1:
         The desired effect is achieved by conditional cancellation of detail format of subreport for records not falling in the target block determined by parent report's current record number. It is like obtaining a filtered output matching target block of sequential numbers. The number of pages on parent report is restricted to the minimum required for the subreport.

    2 - A2: Single SubReport Style 2:
         Similar to A1. However, the number of pages on parent report is not restricted. It can exceed the minimum required for the subreport.

    3 - B1: Two SubReports Style 1:
         Similar to A1 above, but with two subreports, each with its own setting for max rows per page.

    4 - B2: Two SubReports Style 2:
         Similar to A2 above, but with two subreports, each with its own setting for max rows per page.

    5 - C: Single SubReport With Forced Page Break:
         Not recommended. Included for academic interest only.

    Navigation through subform records is conducted through a combo box on the parent form. Alternatively, the user can click on the desired record directly on the subform. The current record gets highlighted in light green while the last viewed record is highlighted in light grey.

    The user can also flag one or more records by double clicking any of the columns on desired record. Last column of records flagged in this manner gets highlighted in pink color. These flags remain in force for the current session of access, even if the form is closed and then re-opened. Repeat double click on a flagged record will remove the flag. To remove all flags, command button captioned "Clear All Flags" can be clicked.