Wednesday, November 14, 2012

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

Friday, September 28, 2012

Recreate Access Tables in SQL Server

by Roger Carlson

Introduction

Microsoft Access is a terrific prototyping tool for SQL Server. But upsizing your database structure to a SQL Server database can be a problem. It would be nice if you could simply export the table structure from Access to SQL Server like you can between Access databases, but you can't. Fortunately, it's fairly easy to read the table structure of an Access table and from that build a SQL Create Table script to recreate the table in SQL Server.

Problems with the Access Upsize Wizard

Of course, Access comes with the Upsizing Wizard, which attempts to recreate your Access table in SQL Server. But the Upsizing Wizard has its problems.

First of all, the Upsize Wizard doesn't give you much flexibility. It chooses the SQL Server data types for your table. You don't have a choice. For instance, the wizard will create your text fields as nvarchar, but suppose you want them created as varchar? What if you want your Yes/No fields created as tinyint rather than bit?

And then there are SQL Server specific properties, like Padding and Clustered Indexes, over which you have no control. The Upsizing Wizard will apply a default padding value, which you cannot change. It will also always create your primary key as a clustered index, whether you want it or not.

Another problem with the Upsize Wizard is you must be connected to a SQL Server database for it to work. This is fine if you have a connection, but suppose you don't have rights to create tables? What if the SQL Server DBA asks you for a script to create them? What if you need to send the table definitions to a remote location to be created there?

Lastly, the Upsize Wizard will only work with SQL Server. The following process could be easily modified to create your tables in Oracle or another SQL-based database.

SQL Server's Enterprise Manager has a function that will save the structure of a table as a Create Table statement in a text file. Your DBA can then to run this script in the Query Analyzer to create the tables for you. It would be really useful if Access had a similar facility, but it does not. Fortunately, you can write one of your own.

How it works

The following code module accepts the name of a table in your Access database. It will then read the field names and data types and build a SQL Create Table statement, converting the Access data types to SQL Server equivalent data types. It then writes this SQL statement to a text file named after the table and having a .SQL extension.

Next, it reads all the Indexes in the table, determines the fields in the index and whether it is Unique or a Primary Key index, and finally builds Create Index statements to recreate these indexes.

Public Sub RecreateTableInSQLServer(TableName As String)
On Error GoTo Err_RecreateTableInSQLServer

The first thing is to create some object and scalar variables.

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, idx As DAO.Index
Dim strSQL As String
Dim indexfields As String
Dim indexunique As String
Dim path As String

Next, you need to initialize the object variables, opening a connection to the current database and to the particular table you want to recreate.

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(TableName)

You also want to find the path to the folder where the database currently resides, so the program will know where to store the text file.

path = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

If you want some other directory, you could hard code a folder path here.

path = "C:\My Documents\Access"

In Access, any field can be defined as part of a primary key as long as there aren't any null values in the field. In SQL Server, however, you must define the field as Not Null ahead of time. Therefore, you have to know which fields are in the primary key before you start defining the fields. To do that, you have to loop through the Indexes collection of the TableDef object. When you find the primary key index (as determined by the Primary property of the Index object), assign it to a string variable (indexfields).

For Each idx In tdf.Indexes
    If idx.Primary = True Then
    indexfields = idx.Fields
    End If
Next

The fields that make up the index are stored in the Fields property of the Index object, but they are stored in an odd format. For a multiple field index, they're stored in a string that looks something like this: +TextField;+LongIntField. Fortunately, you can read this string for the fieldnames with the InStr() function. You'll do this later in the routine.

Now that all the variables are created and initialized, and you know what fields are in the primary key, you can begin to build the SQL statement. Every Create Table statement always starts with the word CREATE TABLE and the name of the table. So you'll write that to a string variable (strSQL).

strSQL = "CREATE TABLE [" & TableName & "] ("

The brackets surrounding both the table name above and the field names below are necessary for table and field names that have spaces or special characters in them.

Next, the code needs to read through all the fields in the table, read the data types, and in some cases, determine some field attributes. It will add the field to the field list of the Create Table statement and depending on the data type, add the corresponding SQL Server data type.

For Each fld In tdf.Fields
  Select Case fld.Type
    Case 4 'Long Integer or Autonumber field
      If fld.Attributes = 17 Then
        strSQL = strSQL & "[" & fld.Name & "] INT IDENTITY"
      Else
        strSQL = strSQL & "[" & fld.Name & "] INT"
      End If
    Case 10 'Text field
       strSQL = strSQL & "[" & fld.Name & "] VARCHAR(" & fld.Size & ")"
    Case 12 'Memo field
      
strSQL = strSQL & "[" & fld.Name & "] NTEXT"
    Case 2 'Byte field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 3 'Integer field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 6 'Single-precision field
       strSQL = strSQL & "[" & fld.Name & "] REAL"
    Case 7 'Double-precision field
       strSQL = strSQL & "[" & fld.Name & "] FLOAT"
    Case 15 'ReplicationID field
       strSQL = strSQL & "[" & fld.Name & "] UNIQUEIDENTIFIER"
    Case 8 'Date/Time field
       strSQL = strSQL & "[" & fld.Name & "] DATETIME"
    Case 5 'Currency field
       strSQL = strSQL & "[" & fld.Name & "] MONEY"
    Case 1 'Yes/No field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 11 'OleObject field
       strSQL = strSQL & "[" & fld.Name & "] IMAGE"
  End Select

This is where you'll use the index string of the primary key. If the field is part of the primary key, add NOT NULL to the field data type, otherwise just append a comma.

  If InStr(indexfields, fld.Name) > 0 Then
    strSQL = strSQL & " NOT NULL, "
  Else
    strSQL = strSQL & ", "
  End If

And go to the next field.

Next fld

There will be an extra comma at the end the field list that needs to be removed. You also have to close off the Create Table statement with a closing paren and semicolon. (Technically, the semi-colon is unnecessary).

strSQL = Left(strSQL, Len(strSQL) - 2) & ");"

Now, you need to create a text file and write the string variable to that file. The easiest way to do this is through low-level IO. The following Open statement will create a text file with the name of the table preceded by the word "Create" and followed with a .sql extension in the folder where the database resides. So for table "Customers", the text file would be "CreateCustomers.sql". Then it will use the Print command to write the SQL statement to the file followed by a blank line.

Open path & "Create" & TableName & ".sql" For Output As #1
Print #1, strSQL
Print #1, ""

Next you need to recreate the indexes themselves, so you need to read the indexes again.

For Each idx In tdf.Indexes

As I said earlier, the index property returns a string like this: +TextField;+LongIntField. To make it useful for a SQL statement, you need to remove the pluses and convert the semicolons to commas. This you can do with the Replace function.

  indexfields = idx.Fields
  indexfields = Replace(indexfields, "+", "")
  indexfields = Replace(indexfields, ";", "], [")

In the first Replace function, you will replace all the pluses with the empty string, effectively deleting them. In the second Replace function, you are replacing each semicolon with "], [". The brackets are necessary for field names that have spaces in them. The first and last fields in the field list will not have brackets on the outer edge, but we'll add them when we put the whole SQL statement together.

You also need to determine whether the index is unique. If it is, the Unique property of the Index object will be true.

  If idx.Unique = True Then
    indexunique = " UNIQUE "
  Else
    indexunique = ""
  End If

You are almost ready to put the SQL Statement together. There is just one more property you need to consider whether the index is a primary key. In Access, you can create the Primary Key index in the Create Index statement. However, in SQL Server, you have to create it in the Create Table or Alter Table statement. I've chosen to do it in the Alter Table statement.

So if the index is a primary key index, use the Alter Table statement to create the primary key. Otherwise, build a Create Index statement.

  If idx.Primary = True Then
    strSQL = "ALTER TABLE [" & TableName & _
    "] ADD CONSTRAINT [PK_" & TableName & "_" & idx.Name & _
    "] PRIMARY KEY ([" & indexfields & "]);"
  Else
    strSQL = "CREATE " & indexunique & _
    " INDEX [IX_" & TableName & "_" & idx.Name & _
    "] On [" & TableName & "] ([" & indexfields & "]);"
  End If

Since your text file is still open, you can simply write the SQL string to it. Then loop for the next index.

  Print #1, strSQL
  Print #1, ""
Next

When you're done reading the indexes, close the text file and destroy the object variables.

Close #1
Set idx = Nothing
Set tdf = Nothing
Set dbs = Nothing

Add error trapping, and you're done.

Exit_RecreateTableInSQLServer:
  
Exit Sub
Err_RecreateTableInSQLServer:
  
MsgBox Err.Description
   Resume Exit_RecreateTableInSQLServer
End Sub

Implementing the Routine

Implementing this routine is relatively easy. Create a form with a listbox and a button. Fill the listbox (we'll call it lstTableList) with all the tables in your database by putting the following in the RowSource property of your listbox:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*"
  And (MSysObjects.Name) Not Like "~*")
  AND ((MSysObjects.Type)=1))
  ORDER BY MSysObjects.Name;

Then call the routine from the button like this:

Call RecreateTableInSQLServer(Me.lstTableList)

Figure1

Figure 1: Form used to implement the RecreateTableInSQLServer routine

The resulting text file might look like this:

CREATE TABLE [tblAllDataTypes] ([AutoNumberField] INT IDENTITY NOT NULL,
[TextField] VARCHAR(50) NOT NULL, [MemoField] NTEXT, [LongIntField] INT, [ByteField] SMALLINT, [IntegerField] SMALLINT, [SingleField] REAL, [DoubleField] FLOAT, [ReplicationField] UNIQUEIDENTIFIER, [DateTimeField] DATETIME, [CurrencyField] MONEY, [YesNoField] SMALLINT, [OLEObjectField] IMAGE, [HyperLinkField] NTEXT);

CREATE INDEX [IX_tblAllDataTypes_Index1] On [tblAllDataTypes] ([TextField], [LongIntField]);

CREATE UNIQUE INDEX [IX_tblAllDataTypes_LongIntField] On [tblAllDataTypes] ([LongIntField]);

ALTER TABLE [tblAllDataTypes] ADD CONSTRAINT [PK_tblAllDataTypes_PrimaryKey] PRIMARY KEY ([AutoNumberField], [TextField]);

Considerations for Access Versions

This routine requires a reference set to the DAO (Data Access Objects) object model. This is not a problem in Access 97, 2003 or 2007, but in Access 2000 and 2002 (XP), you'll have to set this reference.

To do that, go to the VB editor (Alt+F11), and from the menu bar select Tools > References. In the References dialog box, scroll down the list until you find Microsoft DAO 3.6 Object Library. Check the box next to it and click OK to close the box.

Access 97 has an additional problem. The Replace function used above does not exist in Access 97, so you'll need to create one. Fortunately, it's relatively simple.

Function ReplaceCharacter(Target As String, SearchChar, ReplaceChar) As String

Dim i As Integer
Dim tempstring As String
  For i = 1 To Len(Target)
    If Mid(Target, i, 1) = SearchChar Then
      tempstring = tempstring & ReplaceChar
    Else
      tempstring = tempstring & Mid(Target, i, 1)
    End If
  Next i
ReplaceCharacter = tempstring
End Function

To use this function, simply replace these lines:

indexfields = Replace(indexfields, "+", "")
indexfields = Replace(indexfields, ";", "], [")

with these:

indexfields = ReplaceCharacter (indexfields, "+", "")
indexfields = ReplaceCharacter (indexfields, ";", "], [")

Conclusion

There are a variety of circumstances where you might want to recreate an Access table in other database environments. This routine demonstrates a fairly simple method to save the table structure complete with indexes into a SQL script file that can be run in the target environment to recreate the table.

Sample Database

You can find a sample which implements this here: http://www.rogersaccesslibrary.com/forum/topic222.html

Friday, September 7, 2012

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

Thursday, August 30, 2012

Parse First, Middle, and Last Name in T-SQL

I'm not a SQL Server T-SQL expert.  But recently, I needed a method to separate a name field into First, Middle, and Last Name.  Googling around, I found many methods, but none of worked for my specific format of First Middle Last (optional middle):

Roger J Carlson
Susan Carlson

So, after much fiddling, I came up with the following and I thought I'd share.

select
case len(rtrim(ltrim(v_name)))-len(replace(rtrim(ltrim(v_name)),' ',''))
     when 1 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 1)
     when 2 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 2)
     when 3 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 3)
end as firstname,

case len(rtrim(ltrim(v_name)))-len(replace(rtrim(ltrim(v_name)),' ',''))
     when 2 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 1)
     when 3 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 2)
end as middlename,

case len(rtrim(ltrim(v_name)))-len(replace(rtrim(ltrim(v_name)),' ',''))
     when 1 then parsename(replace(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), ',', ''), 2)
     when 2 then parsename(replace(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), ',', ''), 3)
     when 3 then parsename(replace(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), ',', ''), 4)
end as lastname

from mytable

I make no claim to have invented this method, just in adapting it to my specific format.

Friday, July 27, 2012

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):

clip_image001

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".

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

clip_image003---------------------------------------------------------------------------------------------------------------------

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

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

clip_image004

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

This statement (optional):

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

clip_image005

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

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):

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

clip_image007

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

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").

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

clip_image009 
(Click to enlarge)

clip_image011
(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.

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

clip_image013
(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.

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

clip_image015

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

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.

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

clip_image017

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

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

clip_image019 (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.

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

clip_image020

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

Thursday, July 26, 2012

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

Wednesday, July 25, 2012

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

Tuesday, July 24, 2012

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

Monday, July 23, 2012

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:

clip_image001

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:

clip_image002

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:

clip_image003

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:

clip_image004

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

Wednesday, June 27, 2012

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

Wednesday, June 6, 2012

COUNT DISTINCT in Access: Part 5

Comparison of the Methods

SQL Server has a nice built-in function called COUNT DISTINCT, which is missing in Access SQL.

Over the last four posts, I've discussed different ways to simulate it in Access :

  1. Subqueries the FROM Clause
  2. Subqueries in the Field List
  3. User-Defined Function
  4. Crosstab Query (reader submitted method)

Each of these methods have advantages and disadvantages, and as promised, I'll address them here.

Subquery in FROM Clause

The main advantage of creating a subquery in the FROM clause is ease of use, that is, it's the easiest to figure out. It's possible to approach it step-wise by first removing the duplicates from the list to be counted.  See Problem 1 in Subqueries the FROM Clause.

The main disadvantage is lack of flexibility.  As I showed in Problems 2 and 3 in Subqueries the FROM Clause, you can't easily created other levels of aggregation nor simply add a second aggregate to the same level.  The reason is you've pre-limited the values available, so you need to create extra levels of subqueries to compensate.

Over all, this method is useful for simple distinct counts, but not for more complex ones.

Subquery in Field List

The main advantage of create a subquery in the Field List is it's flexibility.  Once you've created the initial query, adding a second aggregate can be added just like in any other aggregate query, that is, just add another field to the field list with an aggregate function.

The main disadvantage is that it's a little harder to figure out in the first place.  The subquery must be a correlated subquery, which is conceptually more difficult.  A correlated subquery is evaluated for each row in the main query, so it must be tied to the main query, and that's a more difficult concept.

This method is useful for more complex queries.  It also more closely simulates the T-SQL Count Distinct, which also works at the Field List level.  This means that if you need to upsize this query to T-SQL, it's as simple as replacing the subquery with the COUNT DISTINCT.

User-Defined Function

One advantage of with user-defined function is that once created, you don't have to figure out how to do a subquery for each query.  You can simply call the function and send in the appropriate values.  It also appears to perform fairly well, but I'll address performance below.

The main disadvantage is that it's fairly easy to produce an incorrect result. The Where and Group By arguments must match the main Where and Group By clauses of the main query or the value will be be incorrect.  However, In an extremely complex query, this method may be useful to reduce the level of complexity in the main query.

Crosstab Query Method

If you're familiar with crosstab queries, this method is nice and clean.  And it performs very well (see below).

But as I've noted before, the main problem is that you can't add additional aggregates.  For instance, you can't show the sum of one field and the count distinct of another in the same query.  This makes it more limited than the other methods.

Performance

First of all, it's silly to talk about performance without discussing indexes.  On non-indexed fields, each of the methods will perform much worse.  In this case, I indexed all of the fields involved in the aggregation: OrderID (primary key), Customer, and Order_Date.

When each of the methods were run against the sample data listed, they all ran nearly instantaneously.  That table consists of 4 customers and 3 days, totaling 22 records.

To test the performance, I created a table consisting of 19 customers over the course of 30 days totaling 113,000 rows.  Then I wrote a subroutine that opens each query, recording the time when it opens and when the query completes.  The code looks like this:

Sub test()
Dim starttime As Date
Dim endtime As Date

'test From
starttime = Now
DoCmd.OpenQuery "TestFROMLarge"
endtime = Now
Debug.Print "TestFROMLarge: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "TestFROMLarge"

'test UDF
starttime = Now
DoCmd.OpenQuery "TestUDF_Large"
endtime = Now
Debug.Print "TestUDF_Large: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "TestUDF_Large"

'test Xtab
starttime = Now
DoCmd.OpenQuery "XTab_Prob2_Large"
endtime = Now
Debug.Print "XTab_Prob2_Large: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "XTab_Prob2_Large"

'test FieldList
starttime = Now
DoCmd.OpenQuery "TestFieldListLarge"
endtime = Now
Debug.Print "TestFieldListLarge: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "TestFieldListLarge"

End Sub

Running this code against the 100K table produced this:

TestFROMLarge: 1
TestUDF_Large: 1
XTab_Prob2_Large: 1
TestFieldListLarge: 8

That gives me some information, but not enough.  So I created an even larger file.  Still using the same 19 customers, but with data spanning a whole year.  This new table had nearly 1 million records.

Running my test code against the 1M table produced this:

TestFROMLarge: 10
TestUDF_Large: 4
XTab_Prob2_Large: 6
TestFieldListLarge: 74

This file gives me the granularity to see differences.  The first three still execute within similar time frames.  The Field List method takes nearly 8 times longer.

Surprisingly, the User Defined Function performs the best of all of them.  My expectation would have been that it was the slowest.  Also surprisingly, the Field List method was the slowest.  I would have thought that a correlated subquery would execute faster.

If you'd like to test this for yourself, I've bundled this whole series with the database into a sample available my my website: http://www.rogersaccesslibrary.com/forum/countdistinctmdb-intermediate_topic595.html

Because of size considerations, it does not have the 1M table, so you'd have to create that yourself.

Monday, June 4, 2012

Count Distinct In Access: Part 4

Crosstab Method (Reader Submitted)

Note: In the comments section of Part 3: User-Defined Function, Patrick mentioned another method that uses a Crosstab Query.  Even though I didn't develop it, I'm including it in this series for completeness.  So, thank you, Patrick. 

SQL Server has a nice built-in function called COUNT DISTINCT, which is missing in Access SQL.

What does COUNT DISTINCT do?  Well, there are times when you want to count distinct values in a query, that is, a count of values without duplicates.  For instance, given the following table, how many distinct customers have orders?

ORDERS

OrderID

OrderDate

Customer

Amount

1

1/17/2008

Ajax Inc.

$310.00

2

1/17/2008

Ajax Inc.

$510.50

3

1/17/2008

Ajax Inc.

$311.00

4

1/17/2008

Baker Corp.

$5,144.00

5

1/17/2008

Baker Corp.

$61.00

6

1/17/2008

Baker Corp.

$110.50

7

1/17/2008

Baker Corp.

$11.00

8

1/17/2008

Crystal & Co.

$111.85

9

1/17/2008

Crystal & Co.

$511.00

10

1/18/2008

Baker Corp.

$711.95

11

1/18/2008

Baker Corp.

$810.00

12

1/18/2008

Baker Corp.

$310.59

13

1/18/2008

Crystal & Co.

$311.00

14

1/18/2008

Crystal & Co.

$811.50

15

1/18/2008

Ajax Inc.

$512.00

16

1/18/2008

D&D LLC

$211.00

17

1/18/2008

D&D LLC

$3,311.50

18

1/19/2008

Ajax Inc.

$410.00

19

1/19/2008

Ajax Inc.

$610.50

20

1/19/2008

Baker Corp.

$4,411.00

21

1/19/2008

Baker Corp.

$511.50

22

1/19/2008

Baker Corp.

$611.50

In SQL Server, I can do this:

SELECT COUNT(DISTINCT Customer) AS CountOfCustomer FROM Orders

Which will give me the following:

CountOfCustomer

4

In Access, if I use the Distinct predicate with the count:

SELECT DISTINCT Count(Customer) AS CountOfCustomer FROM Orders;

I get:

CountOfCustomer

22

Since Access SQL does not have the Count Distinct function, what can I do? 

There are actually four different methods for simulating the Count Distinct:

  1. Subqueries the FROM Clause
  2. Subqueries in the Field List
  3. User-Defined Function
  4. Crosstab Query (this post)

Each of these methods have advantages and disadvantages, and I'll address each in turn. 

 Crosstab Query Method

A crosstab query presents aggregated data in an easy-to-understand grid. 

To create a simple Count Distinct as above, use the following SQL:

TRANSFORM Count(*) AS Cell
SELECT Count(cell) AS CountOfCustomer
FROM Orders
GROUP BY "Anything"
PIVOT Customer In (null);

To get the following result:

CountOfCustomer

<>

4

 

More Complex Queries

The Crosstab can also be modified to produce a customer count grouped by the OrderDate:

TRANSFORM Count(*) AS Cell
SELECT OrderDate, Count(cell) AS CountOfCustomer
FROM Orders
GROUP BY OrderDate
PIVOT Customer In (null);

OrderDate

CountOfCustomer

<>

1/17/2008

3

 

1/18/2008

4

 

1/19/2008

2

 

Problem: Additional Aggregation

However, in the other methods I've discussed, I was able to add additional aggregates to the query like this:

OrderDate

CountOfCustomer

CountOfOrderID

SumOfAmount

1/17/2008

3

9

$7,080.85

1/18/2008

4

8

$6,989.54

1/19/2008

2

5

$6,554.50

I can't figure out how to do that with the Crosstab query, so I'd say this method, although fast, is somewhat limited in terms of flexibility. 

Patrick insists this method has MUCH better performance than the UDF method.  Next time, I'll look at each of the methods and discuss their pros and cons, so we'll see.