What Is Normalization: Part III

This article has moved: What Is Normalization: Part III

What Is Normalization, Part II

This article has moved: What Is Normalization, Part II

What Is Normalization, Part I

This article has moved: What Is Normalization, Part I

New Sample: Form_DynamicSizingOfSubform

Author: A.D. Tejpal

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

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

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

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

Version - Access 2000 file format

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

New Sample: Form_DynamicSizingOfSubform

DH Query By Form

DH Query By Form

Author: Duane Hookom

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

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

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

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

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

DH Query By Form

What is a Primary Key?

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

New Sample: Report_HideGrpHdrsIfNoDetail

Report_HideGrpHdrsIfNoDetail

Author: A. D. Tejpal

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

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

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

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

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

IMPORTANT:

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

Version - Access 2000 file format

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

Access 101: What is an Index?

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

Access 101: Why Do I Get the "Ambiguous Outer Join" Error

You can find this article here: Ambiguous Outer Joins

New Sample: Form_ControlGroups_2K3

Form_ControlGroups_2K3

Author: A.D. Tejpal

This sample db demonstrates handling of form controls in distinct groups in such a way that actions meant for a given group get performed across all controls belonging to that group, without having to process any loop for tackling individual controls. This functionality is achieved via class module named C_ControlGrps. Grouped controls get assigned to a collection, as instances of this class.

Following styles of grouping are shown - covering two distinct groups in each case:
(a) Permanent grouping via control's tag value assigned at design stage.
(b) Dynamic grouping allowing the user to add / remove controls to any of the groups as desired, at run time.

For current illustration, following actions performed on the groups at run time, are demonstrated:
(a) Manipulation of default values group-wise.
(b) Locking / unlocking of controls group-wise.
(c) Restricting tabbing to a particular group as desired.
(d) Hiding / un-hiding of groups as desired.
(e) Manipulation of back colors group-wise, via slider bars for R/G/B color components.
(f) Special effects - This involves a continuous cycle of smooth transition of shades for the back color, interspersed with periodic swapping of back colors between the two groups.

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

Featured Sample: AppointmentsAlert

AppointmentsAlert

Author: A.D. Tejpal

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

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

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

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

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

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

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

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

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

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

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

Access 101: How Do I Find The Path and Filename For My Database?

This article has moved here: How Do I Find The Path and Filename For My Database?

Featured Sample: Excel to Access 2000-XP Converter Utility program

Excel to Access 2000-XP Converter Utility program

Author: Peter Hibbs

This utility program allows you to convert an Excel spreadsheet file which contains 'flat-file' data into one or more tables in an Access database.

The program creates primary and foreign keys in the tables so that 'One to Many' or even 'Many to Many' related tables can be created automatically. You just import the Excel file into the program, create a template which tells the program which columns in the spreadsheet should be copied to which fields in which tables, click a button and the program will extract the data into a series of separate tables which can then be exported to a new database via .csv files.

The program can also be used to create properly 'normalised' tables from data in MS Works or any other database by first exporting the data to an Excel spreadsheet (or .csv file which can then be imported into Excel). Full instructions are included.

Version.
This version runs under Access 2000 and Access XP (2002).

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

Access 101: How Do I Replace System Error Messages With Custom Messages?

Access provides a number of error messages, but many are confusing to the users. I find it sometimes useful to replace the system error messages with custom error messages. The Null primary key is a case in point.

When you try leave a record on a form without a primary key value, the form will display the following error.

But you might want to replace this with your own error message, identifying the actual field.

At it's simplest, you can use the Form_Error event to trap for this and otherrecord-level errors. Something like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3058 Then

MsgBox "You must give Account Number a value."

Response = acDataErrContinue

Else 'msgbox DataErr

Response = acDataErrDisplay

End If

End Sub

3058 is the error number for the null primary key. The "Else" section will let any other error display the normal system error message.

If you want to know the number for another error, just un-comment the msgbox and you'll get the error number. Add any additional errors as an ElseIf.

Other errors you can trap:

Limit to List: 2237

Input Mask: 2279

Required field: 3314

Validation Rule: 2107

Bad Data Value: 2113

Access 101: How Do I Bypass Start Up Options?

This article has moved here: How Do I Bypass Start Up Options?

Access 101: How Do I Run A Process Automatically Once A Day? Part I

There are two ways to do this: 1) from within Access, and 2) externally with Windows Scheduler. I talk about method 2 here: How Do I Run A Process Automatically Once A Day? Part 2 .

Suppose you have a macro (mcrImportFiles) that imports data and you want to schedule it for the middle of the night each night -- say 1 am. How would you do it?

First of all, to do this, you need to leave your Access database open all the time (or at least start it every night) because it must be open to work.

Next, you need to create a form that will stay open all the time. Have this form open automatically at Start Up (see How Do I Configure My Database Start Up? ). It can be hidden if you want. (see How Do I Hide a Form But Leave It Running? ).

Every form has at Timer Property and an OnTimer event. Set the Timer property to 60000 (60 seconds). This will cue the form to run the OnTimer event once each minute.

Now, add some code to the OnTimer event. Something like this:

Private Sub Form_Timer()
If Time() >= #1:00:00 AM# And Time() < #1:01:00 AM# Then DoCmd.RunMacro mcrImportFiles End If End Sub This will run the macro each day at 1 am. To make this process even more robust, convert the macro to VBA code (see How Do I Convert A Macro to VBA Code? ) and insert it directly into the OnTimer event. This way, you can trap for any errors and handle them gracefully instead of simply letting your application hang as it would with a macro.

New Sample: FormParametersForRecordsets

FormParametersForRecordsets

Author: A. D. Tejpal

SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string.

Concatenation of hard values into SQL string suffers from the following drawbacks:

• (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data).
• (b) If text data has embedded quotes, these need to be fixed before concatenation.
• (c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format.
• (d) Care needed in handling Null values.

The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para.

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

New Sample: ModulesAddIn_BulkImportFromFiles

ModulesAddIn_BulkImportFromFiles

Author: A. D. Tejpal

The ModulesAddIn_BulkImportFromFiles for MS Access is a utility that enables bulk import of .bas or .txt files as general modules.

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

New Sample: Query_RowNumbersAndSumLargeData

Query_RowNumbersAndSumLargeData
Author: A.D. Tejpal

Subqueries (or user defined functions based upon recordsets) for calculation and display of row numbers and running sum, are found to be slow in case of large data sets.

Use of increments to global variables offers a faster alternative. This method is preferably used through an action query, as calculated values directly displayed via select query tend to be volatile (the results keep on changing as one navigates up and down the records.

Two alternative methods based upon incrementing global variables are demonstrated as follows. In each case, two styles are covered, namely (a) Straight simple sequence and (b) Group-wise sequence. If it is simple sequence, the user defined functions have provision for specifying the starting number or sum - if desired:
1 - Row number and running sum - via update action.
2 - Row number and running sum - via make table action.

For academic interest, depiction of row numbers through generation of autonumbers has also been demonstrated as follows (this approach is however not as fast as that involving increments to global variables):
3 - Row number - via append action..
4 - Row number - via make table action.

Note:
(a) Use of incrementing global variables (items 1 and 2 above), apart from being the fastest approach, has the added benefit that it is not dependent upon availability of primary key.
(b) Four user defined functions utilized in implementing this method are given in the general module.
(c) Use of these functions in WHERE clause of pertinent queries is meant to initiate the sequence in desired manner.
(d) If filter has been applied to displayed data, generation of row number and running sum gets implemented on the filtered data, as demonstrated in the sample db.

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

Featured Sample: Crosstab Query in Flex Grid

Crosstab Query in Flex Grid

Author: Peter Hibbs

This demo program shows how to display a Crosstab query in a Flex Grid control on a form. The problem with displaying Crosstab queries on a form (say in a ListBox control) is that some queries have a variable number of columns as well as rows. Using a Flex Grid control allows you to set the number of columns at run time as well as giving all the other facilities that you get with Flex Grid controls such as colouring cells, fixing columns or rows, etc, etc.

See Peter's other demo for more information on Flex Grid commands.

This demo works with Access 2000 onwards.

Find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429

Featured Sample - Form_SearchByMultipleListBoxes

Form_SearchByMultipleListBoxes

author: A. D. Tejpal

This sample db demonstrates application of search criteria based upon selections in three multi-select list boxes. For a given list box, the search criteria is deemed True if the field value happens to match any of the selected items. Overall criteria is determined by application of And operator between the outcomes pertaining to different list boxes.

For sake of illustration, three different data types are covered by the three list boxes in this sample, namely date, text and number. In case of text type, handling of embedded quotes if any, within the field values, has also been demonstrated.

Three different styles of displaying the contents of list boxes have been shown as follows:
(a) No cascading - The full list is always under display.
(b) Simple cascading (hierarchical) - LstDate controls the contents of LstType and LstSupp, while LstType controls the contents of LstSupp.
(c) FreeStyle cascade - Whichever list box happens to be updated, causes filtering of contents of other two list boxes accordingly.

For user's convenience, facility for selecting or de-selecting the whole list box has been provided.
Three different styles of applying the search criteria are demonstrated for each style of cascading:
(a) Permanent saved query with embedded criteria clause.
(b) SQL built at run time.
(c) Filter string applied at run time.

Selection results for each list box are stored in corresponding unbound text boxes as comma separated strings. For text type data, each element of such a string is enclosed within outer single quotes, after having replaced each embedded single quote if any, by double single quotes (As the outer enclosing character is single quote, embedded double quotes if any, don't require any special treatment). For date type data, each element of such a string is enclosed within outer hashes.

This arrangement eliminates the need for repeated cycling through the list boxes while testing the field values. The value is simply checked against the contents of corresponding unbound text box, using IN operator. In case of permanently saved query with embedded criteria clause, EVal() function is used for facilitating interpretation of superstring having IN operator.

Version: Access 2000 File Format

Find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=403

New Sample: A.D. Tejpal's ExportToExcelNamedMultiSheets

ExportToExcelNamedMultiSheets

Author: A.D. Tejpal

Description: This sample db demonstrates a drastically simplified method for exporting multiple access tables/queries to custom named worksheets in the given excel workbook. No automation code is needed.

Find out more here: http://rogersaccesslibrary.com/forum/forum_posts.asp?TID=432

What is Microsoft Access?

Microsoft Access^® is the most popular database product on the planet. People argue about what this means exactly, but it is clear that more people use Access (the actual product) than any other database management system.

But many people don't realize that Access is not just one product, but a suite of tools integrated into one product. In other databases, these tools are separate programs, some of which must be purchased from a third-party. Together, these tools make Access one of the most powerful database products in existence.

Now, some may scoff at this. They would say that Access is just a tinker toy compared to a "real" database like Oracle or SQL Server. But that is just ignorance speaking. It is true that the database component of Access (the Jet database engine) is limited in terms of size, speed, and number of users. However, the Jet engine is only one part (and not the most important part, in my opinion) of Access.

So let's look at the component parts of Access:

First of all, as I already mentioned, there is the Jet database engine. In fact, there are two different versions of Jet. There is the traditional version (mdb) which has been around since version 2.0. With Access 2007, there is a new version, the Access Database Engine (accdb). Although it has limitations, it is nonetheless a fully functional relational database management system.

Secondly, there is the Access User Interface (AUI). This is the part of Access that the user sees when they open a table. With the AUI, a user can create tables, modify tables, add data, modify data, and create filters to view data. In SQL Server, this functionality is done in the SQL Server Management Studio (SSMS) or the older Enterprise Manager. In fact, the original Enterprise Manager was patterned after the Access UI.

Third, there is the Query Builder. This is an ad hoc querying tool. Queries can be built in the graphical user interface (gui) or directly in standard SQL. Access does not have "Views" like SQL Server does, but it does have "Select queries" that server the same function. In addition, Access has Data Definition Language (DDL) queries that create and modify table structure, Data Manipulation Language (DML) queries that display or modify data. In SQL Server, you would need a tool like Query Analyser or SSMS to accomplish this.

Another type of query Access uses is the Pass-through Query. A pass-throrgh query bypasses the Jet engine altogether and passes the query on to a linked source like SQL Server. The versatility of Access connet to and manipulate data from various data sources is truly astounding.

Fourth, Access has a built-in application generator. This itself consists of several parts.

1. Forms builder. Access allows you to create form in much the same way as Visual Basic. However, Access forms are much easier to create and the controls available are specific to database development.
2. Report builder. At some point you have to report the data in your database. In Access, you do this in the report builder. In SQL Server, you would have to use a separate product like SQL Server Reporting Services or Crystal Reports depending on how you want them published.
3. VBA modules. All database applications require a procedural language at some point. There are just some things that can't be done in SQL. In Access, this capability is provide in Visual Basic for Applications modules, both general (global) modules and programming behind the forms and reports (class modules).

Creating an application in another database, SQL Server for instance, would require multiple products: SQL Server as the data engine, SSMS to create and modify tables, Query Builder to run ad hoc queries, Visual Basic to create the application, and Crystal Reports for reporting. In Access, you get it all in one.

So you can see that Access is really a Rapid Application Development (RAD) product than it is a database. In fact, Access might more accurately be called an application generator with a complementary database provided.