Check for a Value in a Table

Validate Value before adding new record to an Access table

Does a Value Exist in a Table? Find out before trying to add it. This sample Access database uses different validation methods, including the form's Error Event, to make sure values entered into fields are valid and not duplicated before they are committed to the table. (Access 2010 or Access 2013)

Random Values and Temp Database Files

Temp Access Database for Random Values Random Selection of "N" members in each Category

Randomly select "N"  items from each of several categories with a Top "N" subquery.  With no primary key, there is no way to order the subquery records. A temp table with randomly generated unique keys for each record solved that.

Bonus: Adding and deleting records in a temp table required creating a temp external accdb on the fly.

Many-to-Many relationships in the Singing Cowboys Access Database

Many-To-Many Relationship Data Entry Access Forms Main form/subform designs support many-to-many relationships.

Here's one way to enter records in all three tables in a many-to-many relationship.

Two main form/sub form pairs allow the user to enter either songs or artists or both and slip back and forth easily between them.

Accounting Functions in a Tenant Billing Database

Calculating Results for Multiple Transactions in an accounting database Simple Accounting Principles in a Tenant Billing Demo

Accounting in an Access database: Billing tenants for charges at leased properties. Track charges and payments in two different workflows and calculate balance due.

Bonus: This one is an Access 2007 accdb built entirely with macros.

Create and Completely Hide a DUAL Table in Access

This demo was created by my friend Banana Republic

Use a Hidden "System-Like" table to support UNION queries in Searching Combo Boxes Create and Completely Hide a DUAL Table in Access

You can COMPLETELY hide a table in Access. This demo shows how.

Dual tables have these characteristics:

a) They are hidden, so users are not tempted to use them for other purposes.
b) They consist of a single column and contain a single record.
c) They are not editable.

Read more in my blog on this topic.

Travel "Pack" Demo

Classic One-to-Many Main Form/Sub Form in Microsoft Access One-to-Many Relationships in a main form/subform design.

This demo, based on a travel scenario where groups of people travel together "in a pack", was created for someone at Utter Access . It is a good illustration of the main form/sub form design required to manage one-to-many relationships in a transaction scenario. The transaction itself (the journey, or travel) is tracked in the one-side table, and the details of the transaction (the "pack members" traveling together) are tracked in the many-side table.

Multi-Field Query By Form
Searching Combo Box

Multi-Filter and Searching Combo Box in Access Forms

Multi-Field Filter Form and Searching Combo Box

A question at Utter Access also led to this one. We needed to filter records on a form, but we found Microsoft templates were too limited. I was challenged to make it work for a variety of situations and the result is Multi-Field Filter Form

A second form models the method I use to search for a record using a selecting combo box. This one if very common and there isn't anything too unique in my approach. This requirement is very common; one more good example seems worthwhile.

VBA Code to Find Previous Weekday

Find any WeekDay with VBA code plus language swapping for control captions on forms VBA Code to Find a Previous Weekday

This Access database, available in Access 2003 (mdb) and Access 2007 (accdb) formats, is fairly simple. it takes two arguments--a start date and a weekday--and returns the date in the prior 7 days on which that weekday falls.

It also has some simple language swapping in it to change captions on controls on the forms from one language to another (English and Espa├▒ol).

Key Access database concepts demonstrated in one or more of the free demos on this page include the following: Table design or Normalization, the DLookup() Function, Form Level Error Handling, VBA error Handling, Embedded Macros, user defined system table for application level variables, Public Enums to provide user-friendly terms for error codes, use Control Tags to store values, Access 2010 Control Layout Grid, completely hide a table, create scratch accdb for temp table, VBA to export table, VBA to export querydef, delete a second accdb file, load performance of combo boxes with large recordsets, pre-filtered combo boxes, dynamic SQL load single record recordsets, cascading combo box

┬ęCopyright 2013 Grover Park Consulting. All Rights Reserved.
Free Resources and Working Demos for Microsoft Access Database Design and Development