New Sample: DataDICTIONARY_DisplayControl_Crystal

By Crystal Long

Zip file contains 2 objects: 1 form and 1 module:

• f_DataDICTIONARY_DisplayControl
• mod_crystal_DataDICTIONARY_DisplayControl

You can find this sample here:
http://www.rogersaccesslibrary.com/forum/data-dictionary-display-control_topic610.html
Other Samples By Crystal:
http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html

How to Use this tool:

 

Import the DataDICTIONARY_DisplayControl form and module into a working database, then compile and save, then Open the form: f_DataDICTIONARY_DisplayControl

 

Overview

1. View Data Dictionary for selected table 
2. Go to Table Design view of selected table
3. Open table Datasheet View of selected table
4. Rename selected table
5. See if there are text or memo fields where Unicode Compression is not set
6. See an estimate of record width (sum of the data type sizes, taking compression into account)
7. Change Display Control of selected fields:
   1. Combo and Listbox to Textbox
   2. Integer to Checkbox

Screen Shots

When you first open the f_DataDICTIONARY_DisplayControl form, you will not see much until you choose a table to look at.

Choose Table

 

 

Rename Tables 

 

 

Delete Lookups

Integer to Checkbox

New Sample: Document Query SQL, Form and Report RecordSources

by Crystal Long

Document Query SQL, Form and Report RecordSources, and create query to show main objects

Here is code to DOCUMENT the SQL stored for each QUERY. You can also document the source for FORMS and REPORTS. You can also create a query that lists the main object names and types in your database.

click in first procedure, Run_Word_CreateDocumention_SQL, and press F5 to Run!

A Word document showing the SQL for all your queries will be created.
To use this module to document RecordSource for forms or reports, run one of the following procedures:

Run_Word_CreateDocumention_Forms
Run_Word_CreateDocumention_Reports

To create a query from the MSysObjects table with a list of all the main object names and types in your database, run Run_Create_qObjex_byCrystal_Query
BAS module

to import a module into your database, UNZIP it to pull out the BAS file
then open the database you want to document, press Alt-F11 to go to a Visual Basic window. From the menu, choose: File, Import
Navigate to:
bas_crystal_Document_QrySQL_FrmRptRecordSource_2Wrd_Create_qObjx.bas

NEEDS reference to
Microsoft DAO Object Library or Microsoft Office ##.0 Access Database Engine Object Library
(from the menu: Tools, References...)

Debug, Compile and then Save

then run each of the four procedures at the top that are named Runblahblah

You can find the sample here: http://www.rogersaccesslibrary.com/forum/Document-query-sql-Form-and-report-recordsources_topic606.html

For more samples by Crystal look here: http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html

Microsoft MVP
remote programming and training
Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access
http://www.YouTube.com/LearnAccessByCrystal

New Sample: GetDistance function for Latitudes and Longitudes

By Crystal Long

The shortest distance between 2 points is a straight line.  Here is a function to do that.  It does not take curvature or routes into account.
GetDistance Function for VBA

Function GetDistance(pLat1 As Double, pLng1 As Double _
   , pLat2 As Double, pLng2 As Double _
   , Optional pWhich As Integer = 1 _
   ) As Double
'12-13-08, 12-22
   ' calculates distance between 2 points of Latitude and Longitude
   ' in Statute Miles, Kilometers, or Nautical Miles
   ' crystal strive4peac2012 at yahoo.com
   ' http://www.rogersaccesslibrary.com/forum/topic604_post622.html#622
   
   'PARAMETERS
   ' pLat1 is Latitude of the first point in decimal degrees
   ' pLng1 is Longitude of the first point in decimal degrees
   ' pLat2 is Latitude of the second point in decimal degrees
   ' pLng2 is Longitude of the second point in decimal degrees
   
   On Error Resume Next
   Dim EarthRadius As Double
   
   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7
   Case 3:
      EarthRadius = 3437.74677
   Case Else
      EarthRadius = 3963
   End Select
   
   ' Radius of Earth:
   ' 1  3963.0 (statute miles)
   ' 2  6378.7 (kilometers)
   ' 3  3437.74677 (nautical miles)
   ' to convert degrees to radians, divide by 180/pi, which is 57.2958
   GetDistance = 0
   
   Dim X As Double
    
    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958))
      
   GetDistance = EarthRadius * Atn(Sqr(1 - X ^ 2) / X)
End Function

You can find this sample here: http://rogersaccesslibrary.com/forum/getdistance-function-for-latitudes-and-longitudes_topic604.html

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

Optimizing Continuous Form Size: Part 6 (conclusion)

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form (this post)

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

VI. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

The purpose of this section is to provide information so that a person familiar with Access can incorporate this functionality into another Continuous Form.

The code used for Continuous Form sizing and positioning is contained within two modules, four small forms, and code within each Continuous Form. A sizable portion of the code copied from the sample form should be used without modification. A small portion of the code can be tailored. In this part of the document, each section of code is displayed and clearly marked.

To incorporate the sizing and positioning functionality into another Continuous Form, perform these steps:

A) Make sure you have these references to code libraries (or similar references for your version of Access):

B) Import the two sample database modules (make no changes):

- FormMetrics is a class module that provides various form measurements.

- modGlobalsAndMaxRows is a standard module containing global variables and the function "FnMaxRowsOnForm," which accepts the name of a form and its border style and returns the maximum number of rows that will fit in the form.

C) Import the four small sample database forms used for calculating border size (make no changes):

• - frmBorderStyle0None
• - frmBorderStyle1Thin
• - frmBorderStyle2Sizable
• - frmBorderStyle3Dialog

D) Import form "frmSampleListing3" from the sample database for convenience in copying its sizing and positioning code. This form can be deleted when finished.

E) Open "frmSampleListing3" in Design mode and click on the Code button to expose the code behind the form. Copy all code from the beginning down through the "End Sub" statement of the "Private Sub Form_Current()" event. Copying the remainder of the code is optional ("Sub Form_Close" and "Sub cmdCloseForm_Click").

Three Subs are required:

Private Sub Form_Open

Sub SetVerticalFormSize

Private Sub Form_Current

Insert the copied code into the code module of your Continuous Form. If you are starting with a new Continuous Form, it might be easier to copy all of the code from "frmSampleListing3" into the new form, then modify and delete code as needed.

F) Customize the copied code if desired, following these customization notes.

Notes about customization

Placement of the sizing and positioning code (the code in the Open event of the sample form) can be in either the Open or Load event. As far as I can determine, it's pretty much your choice. Upon opening a form, the Open event fires first, but since these are the first two events to fire, it seems not to make any difference where the code lies. If you insert the copied code into the Load event, change "Form_Open" to "Form_Load".

The other two Subs ("Sub SetVerticalFormSize" and "Private Sub Form_Current") need to be inserted as separate subs, apart from the Open event code.

Following are several snippets of VBA code taken from the code in the sample form. Each is explained as to its purpose and whether it is customizable.

These lines of code at the top of the Open event should remain without change. Of course, the references to "Open" could be "Load".

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

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

These "Dim" statements are used in determining the number of records to be displayed and in setting the form size, and are customizable.

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

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

This statement (optional):

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

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

in the Open event of each form limits Access to one form at a time visible on the screen. If the above statement is removed, also remove all the OnClose event code; it unhides (makes visible) the previous form (OnClose event code shown here):

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

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

The next section of code, beginning with the comment "Begin Form Metrics Code" and ending with the comment "End Form Metrics Code", is the heart of the Continuous Form sizing logic and should be used without alteration, except possibly the percentage of unused vertical space allocated above the form. It can be changed if desired ("Const sglPctSpAboveForm").

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

 
(Click to enlarge)

(Click to enlarge)

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

The next section of code obtains the number of data rows to be displayed and calls a subroutine to set the vertical size of the form based on that number. This code is customizable.

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

(Click to enlarge)

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

Since the form has been resized above, the next set of code acquires the current form measurements and positions the form vertically within the Access window. This code should be used without alteration.

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

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

The remainder of the code in the Open event of the sample form is comprised of common statements to close the recordset, return memory to the system and exit. The "Stop" and "Resume 0" statements provide flexibility in debugging. If an error occurs, an error message will be generated for the user, then the code will stop. The F8 key can be used to step the code to the "Resume 0" statement, which will return to the specific statement which caused the error—sometimes very helpful.

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

The following "SetVerticalFormSize" subroutine is the code described on page 6.

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

(Click to enlarge)

There is an anomaly in the code for which no explanation has been found. Even though there is no useful code in the OnCurrent event, the existence of the event skeleton seems to be required for error-free operation.

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

The remainder of the code in each of the sample forms is comprised of normal event code for closing the form.

After you finish inserting and customizing the code, click on the "Save" button, then click on the "Debug" button in the top menu bar and click on the "Compile" option in the top line of the dropdown menu. Any errors will be called to you attention. Correct any errors and repeat the Save and Compile sequence until no more errors are noted. Then close the code window and open your Continuous Form, observing whether it accomplishes what you expected.

Best wishes for Continuous Form vertical optimization.

You can find this sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

If you have any comments or corrections concerning this document or the sample database, please direct them to the author. The name and email address are near the top of the first page.

Optimizing Continuous Form Size: Part 5

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration (this post)
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

V. Sample Access Database Demonstration

The sample Microsoft Access database associated with this document is named "Optimizing Continuous Form Size.mdb". It contains sample forms that can be opened/perused for demonstration of the concepts described in the previous sections of this document.

The sample database contains:

• 2 Tables:
  • - tblBooks
  • - tblBooksFew
• 8 Forms:
  • - frmBorderStyle0None
  • - frmBorderStyle1Thin
  • - frmBorderStyle2Sizable
  • - frmBorderStyle3Dialog
  • - frmSampleListing0
  • - frmSampleListing1
  • - frmSampleListing2
  • - frmSampleListing3
• 2 Modules.
  • - FormMetrics
  • - modGlobalsAndMaxRows

A "tour" of the Listing Forms will demonstrate the concepts and advantages of optimizing the vertical size of Continuous Forms.

Open database "Optimizing Continuous Form Size".

Open form "frmSampleListing0" in Design mode. While this form is a Continuous Form, it is a classic version, not having any vertical sizing and positioning logic. Notice the tiny vertical size with no detail section rows visible.

Close the form.

Open form "frmSampleListing0" in production mode (with the Open button or by double clicking on the name of the form). It opens to a small size but Access forces about 3 rows of data--even with no detail section rows showing initially in Design mode. You can see the rest of the data by using the vertical slide bar on the right of the form.

Close the form using the "Exit" button on the form.

Open "frmSampleListing0" again in Design mode and drag the lower border of the form down to about the 3-inch mark as shown by the scale on the left side of the form.

Click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form.

Open form "frmSampleListing0" in production mode. Notice that the form is approximately the size set in Design mode.

Close the form using the "Exit" button.

Open form "frmSampleListing0" again in Design mode and move the top of the form to the top of the Access window. Then drag the lower border of the form down as far as possible.

Click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form.

Open form "frmSampleListing0" in production mode. Notice that the form itself nearly fills the Access window top to bottom, but there may be a blank space in the form below the rows of data (on larger screens and screens with higher resolution), demonstrating that on its own, Access does not fit the size of a Continuous Form to the number of rows of data to be displayed.

Close the form using the "Exit" button.

It's strictly optional, but if you wish, you can open the form in Design mode and reset its size to what it was originally--very tiny--so others can follow the same tour, then Click File | Save | File | Close to save the form to its original size.

The next time the form is opened in Design mode, it will appear in the Access window in the same location it was when it was saved and closed.

The remainder of the tour of the sample database will use the other sample Listing Forms (1, 2, and 3), all of which include the same sizing and positioning logic.

frmSampleListing1

Open form "frmSampleListing1" in Design mode. Notice it is very long vertically.

Close the form.

Open form "frmSampleListing1" in production mode (with the Open button or by double clicking on the name of the form). It opens with 30 records displayed. In spite of its large vertical size in Design mode, the size of the form in production mode just fits the number of rows of data—a demonstration of optimal vertical sizing by VBA code. You can verify this by dragging the bottom border of the form down a bit to see that there is no data hidden. And the last row is not slightly pinched; the bottom border of the record selector box on the left is visible.

Close the form using the "Exit" button on the form.

frmSampleListing2

Open form "frmSampleListing2" in Design mode. Notice it is very small vertically, similar to frmSampleListing0.

Close the form.

Open form "frmSampleListing2" in production mode (with the Open button or by double clicking on the name of the form).

As opposed to frmSampleListing0, which opened with 3 records, this form is dynamically sized to fit all 10 records, all the data available. As with the previous form, you can verify this by dragging the bottom border of the form down a bit to see that there is no data hidden. Again the bottom border of the record selector box on the left is visible.

Close the form using the "Exit" button.

frmSampleListing3

Open form "frmSampleListing3" in Design mode. It is small but not tiny. The 1-inch mark should be visible in the scale on the lower left of the form.

Close the form.

Open form "frmSampleListing3" in production mode (with the Open button or by double clicking on the name of the form). It nearly fills the Access window top to bottom, such that there is not enough spare space to include an additional row of data—demonstrating the use of vertical size optimizing code. You can verify there are more records than can be displayed on the screen by noting that there is a vertical slide bar on the right of the form and the Navigation Button field at the bottom of the form shows "of 116", indicating there are 116 records available for this form.

Close the form using the "Exit" button on the form.

This ends the tour through the sample database, demonstrating Continuous Form vertical size optimization.

Next time: Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

Optimizing Continuous Form Size: Part 4

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning (this post)
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

IV. Vertical Positioning

After determining the number of rows of data to be displayed (maximum or otherwise) and specifying the vertical size of the Continuous Form, consideration can be given to the vertical positioning of the form within the Access window. Horizontal positioning is not a consideration since it does not affect vertical sizing or positioning.

The specifics of satisfactory vertical positioning are pretty much individual preferences. Although the metrics from the form make it possible to vertically center the form within the Access window, vertical centering (particularly with small forms) does not seem to lend itself to comfortable viewing by the user. Positioning in the upper part of the Access window is generally preferable to positioning it lower.

The vertical positioning logic used in the sample forms favors the upper part of the window (30% of unused Access window space above the form), but can easily be changed. This amount was determined after testing, but is not based on any scientific value. Perhaps the suggestion here will spur your thinking toward something better.

The simple vertical positioning logic used in the sample forms is spelled out here in pseudo code:

Const sglPctSpAboveForm as Single = 0.30

'Since the form has been resized, get the form's current 'measurements in Twips, including the overall FormHeight.

Call mfi.GetSize ...

' Position form vertically in the Access window.

DoCmd.MoveSize , (WindowHeight - FormHeight) * sglPctSpAboveForm

The MoveSize calculation results in the integer number of twips for positioning the top of the form down from the top of the Access window.

A suggestion on moving the form:

There is probably something the author is not familiar with, but it seems that Windows 7 (don't know about XP) complains about moving a form unless it already contains records. You might want to try something different, but it appears to be necessary to use a "Requery" statement somewhere prior to using the MoveSize statement.

Next time: Sample Access Database Demonstration

Optimizing Continuous Form Size: Part 3

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations (this post)
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

III. Vertical Sizing by Calculations

To dynamically adjust the vertical size of a Continuous Form to the number of records to be displayed (without empty space below the records), a programmed method can be used. In addition to adjusting the vertical size of the form, vertical placement within the Access window can be set by program code also.

This section explains how Access VBA (Visual Basic for Applications) code can be used to automatically calculate the optimum vertical size for a Continuous Form. The sample Access database accompanying this document has forms and code for demonstration. Topic V of this document (Sample Access Database Demonstration) describes how to tour the demonstration.

To complete this task, we need to know the vertical size of the Access window (sometimes called the "Form Client") and the vertical size of each of the elements of a Continuous Form that contributes to vertical size.

The vertical size of a Continuous Form is comprised of these individual elements:

• Border (both top and bottom border combined as one value)
• Caption Bar (sometimes called "Title Bar")
• Form Header
• Form Detail
• Form Footer
• Navigation Buttons

If we have the metrics (measurements) for the Access window and each of the elements above in twips (1440 twips/inch), we can calculate the maximum integer number of rows of a Continuous Form that will fit within the Access window on the particular screen being used.

Comparison of the maximum number of rows that can fit in the Access window with the number of rows of data to be displayed, can provide the basis for specifying the actual size and position of the form.

To get the vertical size of some of the form elements, form metrics code is incorporated from the Access 2000 Developer's Handbook, Volume I by Getz, Litwin, and Gilbert (Sybex), Copyright 1999, which was published Dec. 17, 1999, on a web site called "Office VBA", and with subsequent articles by Getz stating that the Developer's Handbook code needed to be supplemented with code he then provided. The code has been modified to fit the current application.

We start with a description of how each of the measurements can be obtained.

Access Window Height, Caption (Title) Bar Height

Code from Getz, et al, provides these.

Form Header, Form Detail, Form Footer

These values are readily available from Continuous Form properties:

• FormHeader.Height
• Detail.Height
• FormFooter.Height.

Navigation Buttons

The NavigationButtons property of a form can be tested (True/False) for the existence of Navigation Buttons. If they are present, their height can be determined by saving the value of the form's InsideHeight property, setting the NavigationButtons property to False and again saving the value of the InsideHeight property. The difference is the height of the Navigation Buttons.

On some forms this difference may occasionally be reported from Access as a value near 15 twips, when it should be around 250 twips. This incongruity–cause unknown–is handled reasonably well by an approximation. If the difference is reported as less than 216 twips, the value is set arbitrarily to 216, equivalent to 0.15 inches, which is close to being correct.

Border

The last measurement to be considered is Border. It takes more effort to obtain this value since there is no property to provide it. We can use the difference between the measured overall form height for a form with no borders and no Caption Bar (BorderStyle = 0) and the measured overall form height for the same size form having the border style of the Continuous Form, less the height of the Caption (Title) Bar.

A method which can be used to determine the height of the borders involves using four very small forms (2 inches by 2 inches in the sample database) that are identical except for identification, border style, and the name of the global variable to which each form height is saved. Upon opening, each of these small forms obtains its overall form height, saves that into its individual global variable, then closes itself.

In the Open event of Listing Forms 1, 2, and 3, in the sample database, the small form with BorderStyle0 (None) is opened to capture the overall form size for border style 0. The small form having the same border style as the Continuous Form is then opened to capture its overall form size. The calculated difference minus the height of the Caption (Title) Bar is the size of the two (top and bottom) borders taken together.

For border styles 1 and 3 (Thin and Dialog), the composite border size is a variation of the following equation for the Sizable border, style 2 (all values in twips):

CompositeBorderHeight = FormHeightBorderStyle2 - FormHeightBorderStyle0 - CaptionBarHeight.

Having determined the values for the height of the Access window and each of the six elements of the form, we can now determine the total vertical space available for rows of data in the form's Detail section. Dividing that total space available by the height of the Detail section gives the maximum number of rows of data that can be displayed on the form in the Access window. Here is the equation (all values in twips):

MaxRowsOnForm = (AccessWindowHeight - CaptionBarHeight - FormHeaderHeight - FormFooterHeight - NavButtonsHeight - BorderHeight) \ FormDetailHeight

Notice the backslash ("\") preceding FormDetailHeight. That forces an integer quotient.

Maximum Number of Rows possible versus Actual Number of Rows of data

After calculating the maximum number of rows that will fit in the Access window, there must be consideration of the actual number of rows of data to be displayed.

The actual number of rows of data available can be obtained from opening a recordset using the same SQL statement as the RecordSource property of the Continuous Form and capturing the RecordCount property, as demonstrated in the code for sample Listing Forms 1, 2, and 3.

The maximum number of rows that can be displayed is compared with the actual number of rows of data available and the following logic is applied:

If all rows of data will fit in the Access window (actual less than or equal to the maximum), the vertical size of the form is specified to accommodate the actual number of rows.

If the number of rows of data available is greater than the maximum that will fit in the Access window, the number of rows for the form is specified as the maximum. There may be a small space left below (and possibly above) the form, but there should not be enough extra space to display an additional row of data. In form "frmSampleListing3" in the sample database, the maximum number of rows displayed can be seen in the Navigation Buttons area by clicking the record selector on the last record visible when the form is open.

In Listing Forms 1, 2, and 3, in the sample database, the number of rows of data to be displayed is calculated and placed into a variable named " intNRows". Then a subroutine is called to set the vertical form size (maximum or otherwise). Here is the call statement:

SetVerticalFormSize Me, intNRows

The use of "Me" as a parameter provides to the subroutine the name of the Continuous Form to be sized.

The "SetVerticalFormSize" subroutine sets the vertical size of the Continuous Form by calculating the form's InsideHeight property using this equation contributed by Lambert Heenan:

InsideHeight = FormHeader.Height + (Detail.Height * intNRows) + FormFooter.Height

(where "intNRows" is the number of rows to be displayed--either the maximum number permitted by the height of the Access window or the number allowing display of all data without blank space in the lower portion of the form.)

Next time: Vertical Positioning

Optimizing Continuous Form Size: Part 2

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment (this post)
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

II. Vertical Sizing by Hand Adjustment

In case the reader is not familiar with hand-adjusting the vertical size of a Continuous Form, here is a procedure for it. This method will cause the form to open in production mode to a fixed vertical size, the same size every time regardless of the number of rows of data.

In Design view, drag the bottom of the form down to the size you would like to see it when it opens in production mode. Then click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form. When the form opens in production mode, it will open to your preset size. With records displayed, you can see how close to your desired size the form is set. If not satisfactory, the form can be opened again in Design view and the procedure repeated.

The inconvenience of this method is that unless you are prescient, the size of the Detail section may not quite display a full integer number of records or you may have oversized it and have empty space. Hand-adjustment is just a way to get close to a desired vertical size.

Next time: Vertical Sizing by Calculations

Optimizing Continuous Form Size: Part 1

by Earl Brightup earlreb@charter.net
This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.
Topics:

1. Introduction - Continuous Forms (this post)
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html
I. Introduction - Continuous Forms
A "Continuous Form" is one of the form types that can be generated by Microsoft Access. It can display a variable number of rows of data with a fixed number of columns. This type of form is often used for displaying a list of items with related information such as Item Number, Item Description, Item Price, etc. Data fields are displayed in a format similar to a spreadsheet table.
This document describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution. For continuous forms that do not fill the Access window top to bottom, a method of vertically positioning them is suggested.
Dynamically adjusting the size of continuous forms can be valuable, especially in Access applications that will be distributed to a number of locations having different screen sizes and different screen resolutions.
The sample Access database to which this document refers ("Optimizing Continuous Form Size.mdb") was developed using Access 2003 in Access 2000 file format, and has been tested successfully under Access 2000, 2003, and 2007. A bit of the code has run under Access 2010.
The following screen shot is a simple Continuous Form displaying a couple of fields from a table containing a list of books:

The number of rows of data displayed depends on the internal space defined for the form. Without adjustment, Access will display nearly a full integer number of rows, but does not quite do justice to the last row of data unless specifically set by the user. Left to chance or estimation, the last row may be just slightly "pinched" at the bottom. The bottom border of the last record selector box is not visible (not a serious problem). This screen shot shows an example:

The vertical size of the form can be hand-adjusted in Design view (usually with repeated adjustment and testing) to better fit an estimated number of rows of data--and that size will remain fixed when the form is opened in production mode.
If the "AutoCenter" property is set to "Yes", Access will attempt to center the form both vertically and horizontally in production mode, but the vertical size of the form itself remains fixed at the size set by the user in Design mode.
If the user attempts to maximize the form size for the Access window, hand-adjustment may leave the form too long, resulting in the last visible row of data partially showing, like this:

If the number of rows of data to be displayed does not fill up the specified size of the form, there will be a blank area between the last row of data and the bottom of the form. The next screen shot is an example of this case:

Without some method of dynamically adjusting the vertical size of the Continuous Form for the number of rows of data to be displayed, we must depend on hand-adjustment to a fixed size with trial and error and perhaps the occasional blank area.
Next time: Vertical Sizing by Hand Adjustment

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: Data Definition Language: SQL vs DAO

This sample (with full documentation) illustrates how to do a variety of DDL (Data Definition Language) operations using both SQL and DAO.

DDL operations are those that modify the database structure, ie. tables, fields, indexes, and relationships.

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

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: NormalizingRepeatingColumnsVBA.mdb

By Roger Carlson

This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements.

Full Article Included

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