Featured Sample: Really Bad Design Decisions

Really Bad Design Decisions

This is a case study of a really bad database design . Often, initial design decisions can have a cascading effect, creating multiple, secondary design errors.

One such error, commonly made by novice developers, is to slavishly follow a pre-existing paper form to determine their table design. But paper forms are not created with proper database design in mind. They are designed to make it easy for humans to fill out.

This design can be at odds with established design principles. By blindly following the paper form to determine the database design, the developer can create a system subject to numerous data and relational integrity errors.

Sample contains an explanatory document and two databases: "ReallyBadDatabase.mdb" and "ReallyBadDatabaseReborn.mdb". The former shows the initial design, the second shows an improved design. Sample include both the data model and application.

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

Featured Sample: At Your Survey (AYS)

At Your Survey

Author: Duane Hookom

At Your Survey (AYS) is a full featured application that allows users to create their own surveys by designing the questions and providing a lookup of possible responses. ATS uses a fairly normalized table structure so the same tables, forms, code, queries, and reports can be used for any number of surveys. There is a brief manual to help you get started as well as a sample survey with data.

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

Featured Sample: Training Registration Database

By Roger Carlson

This database is a fairly robust application for registering employees for training classes.  In it, you can create Courses, create Students, create Classrooms, schedule Courses to Classrooms, and assign Students to Scheduled Courses.   Your first stop will be the Main Menu, where you will have the following choices:

Schedule Classes

Clicking "Scheduled Classes" from the Main Menu brings you to the Scheduled Classes screen. It gives you a summary of the classes already scheduled.

From there,  select an Application and a Course Name to see a listing of all the scheduled classes.  You can filter the selection further by choosing the Past Classes or Future Classes buttons.  You can also schedule a new class with the New Class button.

To view or edit a particular class, click the GO> button to be sent to the Scheduled Class Entry screen.

The Scheduled Class Entry screen allows you to view, edit, or a class, and assign students to it.  By default, the fields in BLUE are locked to prevent accidental changes.  To unlock these fields, click the Unlock button.

Below the Class Information, you can assign students to the class.  The student fields are NOT editable (even if you unlock the other fields).  The only editable field is the Attended field to indicate the student attended the class. Even though you can't edit the information in the student fields, you can re-size and re-arraign the fields at run-time.

You can print a Class Roster from this screen and also certificates of attendance.  The certificates are bare-bones versions that you will want to modify for your company.

As you enroll students, the screen will track the number of seats taken and the number remaining.  It will allow you to over-book a class, but will give you a warning when you do.

By default, the Scheduled Class Entry screen shows just the records you selected on the Scheduled Classes screen.  Clicking the Show All Records button (bottom) will fill the form with all the classes for that Application.  You can use the Record Selector buttons to scroll between them.

Schedule Students

The Schedule Students screen allows you to enter students and assign classes in one step. 

The screen opens to the New Record all set for you to enter a new student.  If you want to edit a student, you can select one from the drop down box at the top of the screen.  You can also use the scroll buttons at the very bottom of the screen.  When you enter an existing student, the fields turn BLUE, which indicates they are locked.  This is to prevent accidental changes.  Click the Edit Record button to unlock the fields.

You can also assign classes to students in this form.  Just select the class from the drop down list.  The particulars of the class will appear below in the blue fields.  Thes cannot be edited in this form.  To do so, you must go to the Scheduled Classes screen.

Reports

The Reports screen allows you to run various reports.  Each report appears as an item in a listbox.  Select the item and click "Open".

There are two types of reports: 1) Simple Reports and 2) Parameter Reports

Simple reports show the same information all the time.  The "All Courses" report is an example.  It displays all of the current courses offered.

Parameter reports are a little more useful in that the user can select options from a drop down box to limit the output.  The "Classes By Instructor" report is an example.  When you run the report, a small form appears that allows you to choose an instructor.

Simple Reports:  To add a simple report to the Reports List, simply name the report with an "rpt" prefix.  By using a prefix, you can prevent reports (like the "ClassCertificate" report) from showing up in the list.  (ClassCertificate runs from the "Scheduled Class Entry" screen only.)

Parameter Reports:  To add a parameter report to the Reports List is a little more complex.  First of all, you have to create a form which has controls to hold the parameters and reference those controls in the Report Source query.  Next, name the form with an "rpt" prefix.  Lastly, you'll want to name the report itself with an "rpf" prefix.  This will prevent these reports from appearing in the Reports List.

Example:  The form "rptClasses By Instructor" is the form which will run the "rpfClasses by Instuctor" report.  The Record Source for the report (a saved query called: Scheduled Classes Query by Instructor) references the combo box value on the form to restrict the values in the report.

Maintain Information

The Maintain Information screen allows you to edit your reference tables.  In it, you can add or edit Applications, Buildings, Classrooms, Courses, and so forth.  These options will then be available to the drop down lists in the data entry screens.

If you add new reference tables, you'll also want to create a simple edit form for the table.  If you name the form with a prefix of "Maintain ", it will automatically appear in the Table Mainenance list.

The Getting Started screen is a little different from the rest as it allows you to turn the Getting Started screen on and off.

You can find this sample here: http://www.rogersaccesslibrary.com/forum/topic232.html.

Featured Sample: Unbound SQL

UnboundSQL.mdb

by Roger Carlson

This sample illustrates how to use UNBOUND controls to add, delete, and edit records in a table using SQL statements. Companion sample for the Action Queries Series.

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

.

Featured Sample: ActionQueriesInCode.mdb

Action Queries in Code.mdb

by Roger Carlson

There are a variety of circumstances under which you might want to run a query in VBA code. You may want to just display the results of a Select query to the screen at the push of a button. You may want to run an Action Query in a code module as part of another process.

This sample illustrates how to suppress confirmation messages when running an Action query in VBA code, and is a perfect companion to the Action Queries Series.

Full documentation included.

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

.

Featured Sample: Archives Manager

Archives Manager

by A. D. Tejpal

This sample db demonstrates management of personal knowledge base (e.g. notes / articles / extracts etc). Copies of important messages from various discussion groups can also be stored.

Self generating cascaded lookup lists for category / subject / topic facilitate data entry as well as search & retrieval through preview or print.

The db is in Access 2000 file format, developed on Access-XP installation.

You can download this sample for free here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=140.

.

Featured Sample: Listbox with Multiselection used in Query

Listbox with Multiselection used in Query

by Duane Hookom

This sample demonstrates how a multiselect listbox can be used as the criteria in a query. A generic function is used to return a True or False depending on if a field value is selected in a listbox on a form.

There are two list boxes in the demo: one is bound to a numeric field and the other a text field.

While this solution works great for smaller tables, it may not be the best solution for tables with 1000s of records.

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

.

Featured Sample: Charts_AccessAndExcel.mdb

Charts_AccessAndExcel.mdb

by A.D. Tejpal

This sample db demonstrates management of chart objects on forms and reports. Display of Excel chart on Access form based upon Access data, is also covered.

Examples covered pertain to the readings of Blood Pressure and Pulse for patients. Each individual spell of stay in the hospital is identified by unique InPatient_ID.

Important tips for handling the chart object are also included.

Version - Access 2K/XP/2K3 (Access 2000 File Format)
References:
(a) Microsoft Excel Object library (version 9.0 or later)
(b) Microsoft Scripting RunTime
(c) Microsoft Graph - (Appropriate Version)
(d) DAO 3.6

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

.

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

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

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

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