How Do I Configure My Access Database Start Up?

This article has moved: How Do I Configure My Access Database Start Up?

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

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)

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

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

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.

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.