What Is Normalization: Part III

This article has moved: What Is Normalization: Part III

What Is Normalization, Part II

This article has moved: What Is Normalization, Part II

What Is Normalization, Part I

This article has moved: What Is Normalization, Part I

New Sample: Form_DynamicSizingOfSubform

Author: A.D. Tejpal

This sample db demonstrates run time manipulation of subform control height. It covers both views for the subform, i.e. continuous form as well as datasheet. The views can be toggled from one to the other by clicking a command button.

Depending upon the layout for a specific form, it is preferable to specify the maximum number of rows upto which the subform height should expand. This figure can be entered in text box named TxtMaxLines. Default value for max lines is set at 6. While stepping through the records on main form, if the records in linked subform happen to exceed MaxLines, further increase in the height of subform is kept in abeyance. At this stage, vertical scroll bar is inserted. As and when the subform records are again within limits, the vertical scroll bar is removed and the height of subform control adjusts suitably.

In case of datasheet view, care has been taken to dynamically adjust the column widths so that no awkward blank space is visible at right in lieu of scroll bar when it is not needed.

Two alternative ways are available for navigating through records on the main form. The user can either use the normal navigation buttons or the unbound combo box for jumping to any desired record. The combo box is suitably synchronized so that even while using normal navigation buttons, the value displayed in combo box matches the current record.

Version - Access 2000 file format

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

New Sample: Form_DynamicSizingOfSubform

DH Query By Form

DH Query By Form

Author: Duane Hookom

The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys.

The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (datasource) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow he user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields.

***NEW FEATURE***
An all Access mail merge feature was added Mar 6, 2004. This allows users to create simple mailmerge output without using a word processing program such as MS Word.

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

DH Query By Form

What is a Primary Key?

You can find this article here: What is a Primary Key?

New Sample: Report_HideGrpHdrsIfNoDetail

Report_HideGrpHdrsIfNoDetail

Author: A. D. Tejpal

This sample db demonstrates run time hiding of group headers and footers in access reports, if there are no longer any printable records for the group as a result of conditional cancellation of detail section's format event.

This functionality is achieved by using a set of two classes. The main class (C_CondHeaders_Main) determines the maximum existing number of group levels and report sections, apart from building up a list of unique group keys. This class also builds up a collection of objects conforming to sub-class (C_CondHeaders_Sub).

Each instance of the subclass represents a group header or footer section. Instead of trying to pass report section object as an argument, simply the section index is supplied, thereby circumventing the hurdle otherwise faced. Corresponding report section object (WithEvents) is generated within the sub-class.

List of unique group keys generated in ShowDetal method of main class gets pushed to the sub-class via the latter's ShowGroups property. Action for hiding the group header / footer as applicable, is carried out in the sub-class.

With the combination of two classes as outlined above, the arrangement becomes completely generic, capable of handling any number of grouping levels. At the same time, the code becomes drastically shorter and compact.

IMPORTANT:

1. The report must have a calculated text box having [Pages] as part of its expression.
2. Each group header section should have a text box bound to that level's ControlSource field
3. If there are any sorting/grouping levels above the last header which do NOT have headers, then for each such level, the report must include a textbox (may be hidden) located in Detail section, bound to the field serving as group's ControlSource.

Version - Access 2000 file format

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

Access 101: What is an Index?

You can find this article here: What is an Index?