New Sample: Form_SplitFormSimulatedMultiple

By AD Tejpal

    This sample db demonstrates simulated multiple split forms on tab control pages. The split form has two adjacent subforms, one below the other, separated by a divider bar. Top subform serves as single form while the bottom one represents datasheet portion. There is two way synchronization between single form and datasheet. Comparative vertical space available for the two subforms can be adjusted at run time by dragging the divider bar up or down. Single form portion functions as the prime mover.

    Some of the salient features are listed below:
    1 - Ease of adaptation:

        The developer wishing to use this sample db for his own needs has to simply assign his single form as source object for the top subform in the main form included here. Everything else will get taken care of automatically. Based upon generic template, datasheet portion of split form gets generated programmatically in the bottom subform, displaying columns matching bound controls in the single form above. It is like a plug & play feature.

    2 - Consolidation of code in a wrapper class:

        Necessary code for integrated coordination between main form as well as its two subforms is consolidated in a wrapper class, instantiated in open event of main form. This class has pointers to both the subforms as well as the main form.

    3 - No added burden for data loading:

        Datasheet subform uses the recordset already loaded for single form, thus avoiding any additional burden.

    4 - Divider bar:

        (a) Divider bar can be dragged for dynamic expansion / shrinkage of datasheet and single form heights at run time.

        (b) On opening the split form, divider bar assumes the position last held in previous session.

    5 - Re-sizing of nominated controls on single form:

        For added convenience, certain controls on single form, e.g. text box bound to memo field or even an image control, can be slated for vertical re-sizing so as to best utilize the available space resulting from divider bar movement. Tag property of such controls should include the word ReSize. In the sample db, such a behavior is demonstrated for control named  Synopsis, bound to memo field.

    6 - Hiding / Un-hiding of datasheet columns:

        On opening, datasheet columns matching memo fields are in hidden state. The user can hide any other column by double clicking the same. Similarly, any column can be un-hidden by double clicking the matching bound control on single form.

    7 - Auto adjustment of datasheet column widths:

        (a) At any given stage, the width of displayed columns gets adjusted automatically, so as to ensure optimum utilization of available width of datasheet window, duly taking into account the latest status of hidden / un-hidden columns.

        (b) For a given single form, on opening for the very first time, datasheet columns assume equal width, suiting available space. Thereafter, if the user has manually adjusted the column widths, any further automated adjustment of column widths in response to hiding / un-hiding of columns is carried out in such a manner as to retain relative proportion of column widths.

        (c) On click of a command button, the user has the option to reset all column widths equally distributed, as if it were the first opening of form.

    8 - Highlights:

        (a) When a datasheet column is in hidden state, corresponding control in single form gets highlighted in light grey. As and when the column is un-hidden, matching control on single form reverts to its normal color.

        (b) Current row in datasheet gets highlighted in light green.

        (c) As the user tabs from one control to the other on single form, matching cell on current row of datasheet gets highlighted in distinct color (say light maroon).
        Note: Flicker effect on datasheet, due to conditional formatting, seems to be more pronounced in Access 2010 as compared to Access 2003.

    9 - Search Action - Positioning of destination row in datasheet window:

        Based upon search action performed via suitable controls (like combo box etc) on the single form, the destination row on datasheet gets positioned at middle of display window. This takes into account dynamic height of datasheet window, resulting from movement of divider bar. In Access 2010, use of search box on navigation bar, too, results in similar positioning of destination row at middle of datasheet window. 

Wrapper Class Usage:
    Integrated wrapper class C_SplitForm is used in VBA module of main form F_Main as follows:
    (1) Following statement is in F_Main's declarations section:
         Private mfm As C_SplitForm

    (2) Following code is in F_Main's open event:
        Set mfm = New C_SplitForm
        mfm.P_Init Me, Me.SF_A, Me.SF_B, Me.LbDiv, "T_ZZZRef"
Note:
    (a) Subform control at top, i.e. SF_A holds the single form while SF_B holds the datasheet form. Single form is the prime mover.
    (b) Reference table named T_ZZZRef holds divider bar position and columns widths from last session.
    (c) Label LbDiv serves as the divider bar. It can be dragged up & down as desired, for adjusting relative heights of single form & datasheet portions at run time.
    (d) Setting of form object to Nothing is carried out in close event of mfmMain in wrapper class C_SplitForm, so as to ensure proper termination of the class.
    (Care should be exercised Not To set mfm to Nothing in real form's module, for example in its close event, as that would interfere with smooth termination of wrapper class. - Also see remarks in close event of mfmMain in this class).
    (e) Class C_SplitFormControls is a child class of wrapper class C_SplitForm.
    (f) Generic datasheet form used in bottom subform control (SF_B) has no code in its VBA module. It has 200 unbound text boxes along with corresponding attached labels so as to cater up to 200 columns.

Version: Access 2000 file format.

You can find the sample here: http://rogersaccesslibrary.com/forum/Form-splitFormsimulatedmultiple_topic605.html

New Sample: Form_DragDropResizeArrange

By AD Tejpal

    This sample db demonstrates various methods for dragging / re-sizing / arranging of controls by user action on access form at run time. In each style, before closing the form, user has the option to save latest status of each control regarding its position and size. On next opening of form, controls will get depicted as per such saved status.

    For a control to become eligible for drag and/or resize action, its tag property should include the words Drag and/or ReSize. At any stage, the user can undo the last action by clicking the UnDo command button. As and when desired, all such controls can be made to revert back to their original design settings by clicking the command button at bottom left. This command button also serves to toggle between design settings and last saved user settings.

    Following alternative styles are demonstrated:

    1 - Style A - It covers the following features:

        1.1 - Drag / re-size by direct use of control's mouse events via class C_ControlsDDR. This class has an interesting feature: WithEvents functionality for different types of controls has been incorporated into a single class.

        1.2 - Grouped controls manipulation. As the user drags the mouse (with left button in pressed state) around a set of controls, a red rectangle gets drawn accordingly and all controls falling within this rectangle are treated as a group. This group of controls can then be subjected to any of the following actions as desired:
            (a) Drag the group of controls.
            (b) Align the grouped controls Left / Right / Top / Bottom.
            (c) ReSize the group as per Widest / Narrowest / Tallest / Shortest control.
            (d) Make horizontal or vertical spacing amongst grouped controls equal.

        1.3 - All controls enclosed by red rectangle drawn by the user (para 1.2 above) become subject to grouped action, even if not carrying the words Drag or ReSize in their tag values. For controls required to be kept immune to grouped action, the tag value should carry the word Exclude.

    2 - Style B:

        2.1 - It demonstrates induced manipulation of controls via drag/re-size handles - without depending upon mouse events of these controls. Three types of controls are illustrated, i.e. web browser, subform and image. Out of these, web browser and subform control do not have any mouse event of their own.

        2.2 - As the user clicks upon the target control, drag handle appears at top left while resize handle appears at bottom right. Dragging on top left handle (with left button pressed) moves the control while similar action on resize handle leads to resizing.

    3 - Style C:

        3.1 - It demonstrates a completely non-intrusive approach towards induced manipulation of controls without depending upon any of their event. While three types of controls are illustrated, i.e. web browser, subform and image, this approach is universally applicable to all types of controls (including lines which do not have any event at all).

        3.2 - As the user clicks anywhere on blank space of detail section near top left of target control, it is accompanied by  automatic detection of that control. In confirmation, a red marker appears at top left of the control. Dragging the mouse (with left button pressed) on form detail section leads to induced movement of control identified by the marker.

        3.3 - Similarly, when the user clicks anywhere on blank space of detail section near bottom right of target control, it is accompanied by  automatic detection of that control. In confirmation, a red marker appears at bottom right of the control. Dragging the mouse (with left button pressed) on form detail section leads to induced resizing of control identified by the marker.

    4 - Style D:

        4.1 - It demonstrates a completely non-intrusive approach towards induced manipulation of lines (lines do not have any event at all).

        4.2 - As the user clicks anywhere on blank space of detail section near top left of target line, it is accompanied by  automatic detection of that line. In confirmation, a red marker appears at top left of the line. Dragging the mouse (with left button pressed) on form detail section leads to induced movement of line identified by the marker.

        4.3 - Similarly, when the user clicks anywhere on blank space of detail section near bottom right of target line, it is accompanied by  automatic detection of that line. In confirmation, a red marker appears at bottom right of the line. Dragging the mouse (with left button pressed) on form detail section leads to induced resizing/rotation of line identified by the marker.

        4.4 - Rotating action on a line can be carried out in either direction (clock-wise or anti clock-wise). There is no limit to the angle of rotation. So long as left mouse button is kept pressed, the user can continue rotating the line in complete circles in either direction.

    Note: It is observed that combo box and list box do not behave in a completely satisfactory manner while being dragged or re-sized through direct use of mouse events of these controls. There is no such problem with induced drag/re-size approach, as demonstrated in styles B to D. Interestingly, styles C & D  are completely non-intrusive and do not depend upon any event of target control.

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

New Sample: Form_PivotTableSimulated

Form_PivotTableSimulated

by AD Tejpal

    This sample db demonstrates a simulated pivot table, displayed on access subform in datasheet view. It is based purely upon native access queries and VBA, getting rid of traditional dependence upon OWC i.e. Office Web Components (slated for deprecation).

    2 - Incorporation of features typical of excel pivot table:
    --------------------------------------------------------
    An attempt has been made to incorporate basic features considered typical of an excel pivot table as listed below:
    2.1 - Being a datasheet, column headings remain in perpetual view, affording convenient vertical scrolling.
    2.2 - Suitable number of grouping columns at left are frozen so as to facilitate convenient horizontal scrolling.
    2.3 - Uncluttered display (suppressing duplicate contents in  grouping columns).
    2.4 - Full choice of filter fields (equivalent to page fields in excel)
    2.5 - Super-imposition of a layer of cross tab columns in parental style, if a field is earmarked as column field.
    2.6 - Toggling of data orientation between column and row styles as per user choice.
    2.7 - Manipulation of ordinal position of columns - as desired by user - by clicking up or down command buttons in field list.
    2.8 - Run time insertion of unlimited number of calculated field columns - as desired by user. Calculated field names and the underlying expressions can be edited as and when desired. The results of any such action get displayed promptly in the pivot table.
    2.9 - Run time setting / editing of formatting for different data fields - as desired by user. The results of any such action get displayed promptly in the pivot table.
  2.10 - Run time hiding / un-hiding of grouping and data fields as desired by user.
  2.11 - Fields List and Filter Check-List can be hidden un-hidden via command button. The filter list gets displayed only if IsFilter check box against one or more fields is in selected state.
  2.12 - If desired by the user, filter field columns get displayed in the pivot table as well.

    3 - Some Interesting Extra Enhancements:
    ------------------------------------------
    While building the simulated pivot table certain interesting enhancements have been incorporated as follows, so as to make it still more versatile and user friendly:

    3.1 - Generic Data Source:
    ---------------------------
    The arrangement for depicting the pivot table is completely generic. The developer is not required to drag any fields on to the form. All that needs to be done is identification of data source (which can be the name of a table or saved query). This is done conveniently via look-up list on a combo box. On selection of new data source, corresponding pivot table gets displayed via unbound controls on the subform. Adequate number of such controls have been provided so as to suit up to 300 columns.

    3.2 - Step by step auto-expand and auto-collapse:
    ---------------------------------------------------
    Step by step auto-expand or auto-collapse of pivot table can be carried out by clicking pertinent command button.

    3.3 - Flexible Run Time Grouping By Date Type Fields:
    --------------------------------------------------------
    Source data originally entered as simple dates can be depicted in various grouping styles, e.g. ByYear, ByQuarter, ByMonth etc. The user can play with date grouping style, selectable via combo box at bottom right. The results of any such action get displayed promptly in the pivot table.

    3.4 - Color Highlights For Pivot Table Rows Depicting Sub-totals:
    ------------------------------------------------------------------
    For added convenience, sub-total rows in pivot table are highlighted in color. Wherever more than one such rows happen to be adjacent, alternate color highlights are provided - for improved legibility.

    3.5 - Color Highlights In Fields And Filter List:
    -----------------------------------------------
    In fields list, distinct color highlights are provided for fields identified for hiding, filtering or to serve as column field. In filter check list, excluded items are highlighted.

    3.6 - Reminder Content In Outer Columns:
    ----------------------------------------------------
    During vertical scrolling in traditional pivot tables, the topic displayed in outer-most column at left can go out of view, depending upon the range of contents in grouped columns at right. For better convenience, pivot table in this sample db provides for repeat display of outer column content, whenever their is change of content in last but one grouped column.

Version: Access 2000 file format.

You can find the sample here:  http://www.rogersaccesslibrary.com/forum/topic582.html

New Sample: Report_TrainControlChart

by AD Tejpal

This sample db demonstrates time distance charting of trains on a double line route. Time axis, covering 24 hours is depicted horizontally, while various railway stations are displayed along the vertical axis. Vertical lines across chart area identify half hour time intervals. For convenient legibility, time lines at two hour intervals have darker shade.

Positioning of stations is in proportion to their respective distance from terminal station as compared to overall length of route. Major stations have adequate loop lines, while the minor ones have no facility for overtaking.

Each pair of trains is assigned a distinct color (in table T_Trains). T1/T2 (Red) is a slow train, stopping at all stations. T3/T4 (Blue) is faster and stops only at major stations en-route. T5/T6 (Green) is a super fast pair, stopping at only at some of the major stations en-route. Halts of slower trains are devised in such a manner as to permit un-impeded overtake by faster ones. Two local trains (L1 and L2) performing multiple U trips are also demonstrated. Start and end points of each train have been made prominent by placement of a marker (a small circle - looks like a big dot) in a color matching that of the train in question.

Train control chart can be viewed in three alternative styles as follows, depending upon user's choice via option group:

(a) Whole route at a glance.

(b) Zoomed view of first half of route.

(c) Zoomed view of second half of route.

Note:

(a) If more stations get added (in table T_Stations), proportionate inter-station spacing in the chart will get adjusted automatically so as to suit the available chart height.

(b) Chart height is set as 8100 twips via report level constant mTotChartHt. This is found to work fine in Access 2003 on Win XP (Paper size: A4, Default printer: HP Laserjet M1005). If, depending upon local set up, it is found that the report tends to spill over beyond one page, the value of this constant can be adjusted downwards suitably. For complete chart to be depicted, it is important that report (R_TrainControlChart) remains confined to a single page.

Version: Access 2000 file format.   References: DAO 3.6

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic581_post599.html

.

New Sample: Query_A2K10_MultiValueFields

By AD Tejpal

    This sample db demonstrates query based approach to bulk appending / updating / make table actions involving multi-value  fields in Access 2010, without resorting to use of recordset or recordset2 objects, thus overcoming a known limitation associated with such fields.

    In this arrangement, an extra field named TempID is provided in the destination table. The append query is executed in two stages. In first stage, primary key values from source table are appended to TempID field in destination table. In 2nd stage, value elements of MVF field get appended, using an inner join between the source and destination tables (PK of source table equals TempID of destination table).

    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.  

Version: Access 2010 accdb file.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic575_post593.html#593

New Sample: Form_ContinuousSimulated

By A. D. Tejpal

This sample db demonstrates a simulated continuous form having unbound controls, facilitating row-wise display of unlimited colors.


It displays 12 records at a time. Full coverage of data is provided via suitable navigation buttons. Apart from being able to edit the records, the user can add new records or delete the current record by clicking appropriate command buttons. In addition, two alternative modes for search have been provided: (a) By record number or (b) By ID number. All these actions are feasible directly on the form. Current record remains identified by special highlight in first column.

First two columns (locked) display the record number and TrainID. Next two columns, holding TrainCode and ColorValue, are editable. Last column serves as ColorStrip, each row displaying the color represented by ColorValue in the previous column.

There is two way synchronization between ColorValue and ColorStrip. Any value entered in the former gets reflected as corresponding color in the latter. On the other hand, double click on ColorStrip invokes the color dialog box, where the user has unlimited choice and the color finally selected gets displayed in the color strip. Simultaneously, appropriate value gets assigned in ColorValue column.

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/topic574_post592.html

Report_SubRepSetMaxRowsPerPg

By AD Tejpal

This sample db demonstrates custom setting of maximum number of rows per page for one or more subreports.


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.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/report-subrepsetmaxrowsperpg_topic573.html

New Sample: Form_LastViewedAndCurRecToggle

By A.D. Tejpal

    This sample db demonstrates toggling between last viewed record and the current record on subform in datasheet view, via a command button on parent form.

    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.

Version: Access 2000 file format

You can find the sample here:
www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=572&PID=590#590

New Sample: ListFoldersAndFiles

By AD Tejpal

    This sample db demonstrates listing of folders and files contained within the selected top directory, as per desired file specifications. Based upon user's choice, subfolders can be included or ignored. If desired, more than one types of files can be included in a single file spec in the form of a comma separated string - e.g. :  "*.htm,*.pdf,*.txt" etc.

    Process Mode For Listing Folders and files:

    Two alternative methods for listing of folders and files, under a given top folder, are covered as follows:
    (a) Non-Recursive mode - using Dir() function. Apart from being faster than (b), it has the advantage that there is no extra strain on memory resources (otherwise associated with recursive approach), thus avoiding the risk of potential hang up in case of very large and deep directory tree.
    (b) Recursive mode - using FileSystemObject

    Display Of Listed Folders And Files:

    Each run for listing of folders and files is logged in table T_ProcessLog. On the viewing form, for the selected ProcessID, path and other details of topmost folder are displayed at top of the form. Similar details for the current subfolder are displayed just below the information for top-most folder.

    Subfolders and their files are displayed in adjacent subforms. For the current file, its details (like file type, size, attributes, DtCreated / DtLastModified / DtLastAccessed) are also displayed, apart from a hyperlink to the file itself. The hyperlink label becomes active only for permitted file types. The user can edit the contents of table T_AllowHyperLink for setting such  permissions. Three alternative styles of display are provided as follows:

    Style A - View Folders and Files In Hierarchical Chain:

    The user can drill down the directory tree by expanding any of the subfolders which then assumes the role of current main folder, resulting in display of subfolders and files held by the erstwhile subfolder. This can be done indefinitely, till the last subfolder at deepest nesting level is reached. Similarly, by pressing a command button, the user can move up the directory tree. The process can be repeated till the current main folder becomes identical to the top folder (i.e. the original top most folder for which the listing was generated).

    Style B - View All Folders At A Glance - And Their Files:

    For convenient viewing, the folders are sorted as per nesting level and path. The top-most folder (nesting level: zero) is highlighted in distinct color. For other folders, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.

    Style C - View All Files At A Glance:

    For convenient viewing, the files are sorted as per nesting level and path. Files in top-most folder (nesting level: zero) are  highlighted in distinct color. For other files, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.

    General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca.

    Note:
    (a) While using file system object, all types of folders and files get covered (including System, Volume, Hidden, ReadOnly etc). On the other hand, while using Dir() function, such types don't get covered unless relevant arguments are explicitly supplied.

    (b) Using Dir command via DOS command prompt, listing of folders and files can be saved to a text file, which can then be imported into access table. Such a listing is quite fast and one might be tempted to try this route. However, there is a pitfall associated with this approach. If any special characters are present in the folder or file name (say in internet files), the same might not come through faithfully. For example ® is found to come across as r - resulting in corrupted path. 

Version: Access 2000 file format.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic568_post584.html

More samples by AD Tejpal: http://www.rogersaccesslibrary.com/forum/tejpal-ad_forum45.html

New Sample: Form_SynchronizedSubforms

By A.D. Tejpal

This sample db demonstrates synchronized scrolling of two subforms (both in datasheet view).

Two modes are covered:
    (a) One way synchronization: Top subform always functions as the master while the other one serves as the slave.
    (b) Two way synchronization: Whichever subform happens to be the active one, functions as the master while the other one serves as the slave.

Note: For ready identification, the subform currently serving as the slave, has a darker back color as compared to the master.

For each of the above modes, three alternative styles of scroll synchronization are demonstrated:
    (a) Synchronize horizontal scroll only.
    (b) Synchronize vertical scroll only.
    (c) Synchronize both horizontal and vertical scroll.

Sample data depicts student's scores in phase 1 (top subform) and phase 2 (bottom subform). For each student, wherever the scores in these two subform happen to differ, the same get highlighted as follows:
    (a) Top subform: Light grey.
    (b) Bottom subform: If value is greater than that in other subform, it gets highlighted in light green. On the other hand, if value is less than that in other subform, it gets highlighted in light pink.

Note: The above highlights get suitably updated promptly on editing of data in either of the two subforms.

Version:  Access 2000 File Format

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

New Sample: Query_ComputeAcrossFields_CodeLess

By A.D. Tejpal

    This sample db demonstrates computations across fields via pure SQL. In the first step, normalization of data is accomplished, using Cartesian join between the source table and an ancillary single field table holding the field names. Thereafter, a totals query provides the desired results.

    Two styles are covered:
    (a) Compute across all fields.
    (b) Compute across top 3 fields (i.e. fields holding top 3 values).

Version:  Access 2000 File Format

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/query-computeacrossfields-codeless_topic565.html

New Sample: Form_DatasheetHighLightStyles

   This sample db demonstrates an interesting approach to conditional highlighting of datasheet forms. It is remarkably generic, using a set of functions in general module and does not depend upon any primary key field. In fact, no field name or form name is used and hardly any code is needed in the form module (except for style (b) below).

    Following styles for highlighting the records are covered:
    (a) Highlight current record, First record, new record.
    (b) Flag desired row or rows by dbl clicking (Dbl click again to remove the flag).
         Once a flag is set for a record, and unless it is removed subsequently by user action (another dbl click on flagged record), it remains in force for current database session, even if the form is closed and then re-opened.
    (c) Highlight odd rows.
    (d) Highlight even rows.
    (e) Highlight every third row.
    (f) Highlight top - mid - last row.
    (g) Highlight top 2 - mid 2 - last 2 rows.

    Note: The solution is equally applicable to continuous forms.

Version: Access 2000 file format.

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

.

New Sample: Form_ControlDefaultValues

Form_ControlDefaultValues


By A.D. Tejpal

This sample db demonstrates programmatic setting of default values for form controls, covering different data types (Date, Text and Number). Two methods are illustrated as follows:

1 - Concatenation of intended default value into a string assigned as control's default value property.

2 - Generation of intended default value through embedded reference within the string assigned as control's default value property.

For each of the above styles, two alternative modes are demonstrated as follows:

(a) Set defaults as per current record (On editing or double clicking pertinent controls).

(b) Set defaults as per last record (On editing or double clicking pertinent controls - only if the cursor is on last record).

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

.

New Sample: Query_FillMissingValues

by AD Tejpal

This sample db demonstrates filling up of missing values in a sequential series. Three types of source data are covered:
(a) Number series.
(b) Alpha-numeric series.
(c) Date series.

Subform on left shows the start and end values for each missing range of sequential values per PersonID. Subform at right depicts all the missing sequential values duly filled in. Each record representing start of a new sequence is highlighted in color.

Note:
A single field table (T_Ref) populated with sequential numbers from 0 onwards is used as the driver for eliciting the missing values. The number of records in this table should be such as to cover the maximum likely range of missing values.

Version: Access 2000 file format.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic558_post573.html

.

NewSample: Query_GrpSequentialsAndMissing

by AD Tejpal

This sample db demonstrates identification of data blocks as well as missing portions in a sequential series. Three types of source data are covered:


(a) Number series.

(b) Alpha-numeric series.

(c) Date series.

Subform on left shows all records, duly highlighting the start of each new block of sequential series. Subform at right depicts group-wise gist (for each PersonID) of sequential blocks as well as missing portions, duly indicating the start and end values for each set. Missing blocks are highlighted in light grey.

Version: Access 2000 file format.
 
You can find the sample here: http://www.rogersaccesslibrary.com/forum/query-grpsequentialsandmissing_topic556.html
 
.

New Sample: TableNormalizationByPureSQL

by A.D. Tejpal

This sample db demonstrates pure query based solution for normalization of data held by a non-normalized table (T_Source) and posting the converted contents to destination table (T_Normalized).


Steps:

1 - Create the empty destination table T_Normalized with a structure identical to that of table T_Source, but without the non-normalized fields (e.g. Red, Green, Blue, Yellow in this sample).

2 - Add two new fields to the newly created table T_Normalized. One meant for holding the names of non-normalized fields and the other for holding corresponding values. In the current sample, these two fields are named Color and Stock respectively.

3 - Create an auxiliary table named T_SourceFieldsConverted having a single field meant for holding names of non-normalized fields. In the current sample, this field is named Color. Populate this table with the names of non-normalized fields (i.e. Red, Green, Blue, Yellow in this sample).

4 - Execution of append query Q_AppNormalized will populate destination table T_Normalized with normalized data, duly converted from source table T_Source. This query is based upon Cartesian join between tables T_SourceFieldsConverted and T_Source.

Version: Access 2000 file format.
 
You can find the sample here: http://www.rogersaccesslibrary.com/forum/tablenormalizationbypuresql_topic554.html
 
.

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

.

Featured Sample: Archives Manager

Archives Manager

by A. D. Tejpal

This sample db demonstrates management of personal knowledge base (e.g. notes / articles / extracts etc). Copies of important messages from various discussion groups can also be stored.

Self generating cascaded lookup lists for category / subject / topic facilitate data entry as well as search & retrieval through preview or print.

The db is in Access 2000 file format, developed on Access-XP installation.

You can download this sample for free here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=140.

.

Featured Sample: Charts_AccessAndExcel.mdb

Charts_AccessAndExcel.mdb

by A.D. Tejpal

This sample db demonstrates management of chart objects on forms and reports. Display of Excel chart on Access form based upon Access data, is also covered.

Examples covered pertain to the readings of Blood Pressure and Pulse for patients. Each individual spell of stay in the hospital is identified by unique InPatient_ID.

Important tips for handling the chart object are also included.

Version - Access 2K/XP/2K3 (Access 2000 File Format)
References:
(a) Microsoft Excel Object library (version 9.0 or later)
(b) Microsoft Scripting RunTime
(c) Microsoft Graph - (Appropriate Version)
(d) DAO 3.6

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=135

.

Featured Sample: AppointmentsAlert

AppointmentsAlert

Author: A.D. Tejpal

This sample db demonstrates an appointments planner featuring audiovisual alarm for events becoming due.

The Planner opens with appointment grid pre-selected for today's date and the cursor moves to current time slot.

If any other date (not older than yesterday) is selected in the Date Picker control, fresh appointment grid for that date is presented (if not already existing).

When put into standby mode, the utility goes into minimized state and keeps scanning the status of scheduled appointments at specified time intervals.

As & when an event becomes due, an audio alarm is sounded accompanied by a pop-up form displaying the current status of various appointments.

The time in advance of an event for sounding the alarm, as well as the frequency of scanning is amenable to customization by the user.

Audio alarm is governed by the *.wav file located in the same folder as that containing the sample db. If it is desired to use a different sound, simply replace this file by the one desired.

The zip file contains two versions (Access 2000 file format), developed as follows -
(a) Access 2002 installed on Windows XP
(b) Access 2000 installed on Windows 98

Caution -
(a) An activex control named 'Microsoft Date And Time Picker Control' has been used in this database. This control is required to be registered in the access installation on user's computer, before attempting to open & use the sample db.
(b) For registration - file mscomct2.ocx should be available in System32 folder of windows operating system. Open any access database other than this db (even a blank one will do), click Tools -> Activex Controls -> Register. Navigate to the location of above .ocx file and click OK.

Versions - XP & 2K (both in Access 2000 file format)
References -
(a) XP Version -
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft Office 10.0 Object Library DAO 3.6
(a) 2K Version -
Visual Basic For Applications
Microsoft Access 9.0 Object Library
Microsoft Office 9.0 Object Library DAO 3.6

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=141