Why a Free Resource Site for Microsoft Access Databases?

Does a Value Exist in a Table?

Find out before trying to add it.

Check for a Value in a Table

This is a common question at Utter Access: "Before I add a new value to a table I want to check whether it exists already. How can I do that?"

The sample database uses several validation methods, including the form's Error Event, to make sure values entered into to fields are valid and not duplicated before they are committed to the table.

Generate Random Values in VBA and Create Temp Database Files

Avoid Database bloat in Access databases.

Random Selection of "N" members in each Category

A user at Utter Access needed to randomly select three records each from several categories. A Top 3 subquery does that, but with no primary key on the table, there was no way to order the subquery records for that query. Creating a temp table with randomly generated unique keys for each record solved that. I modified it select any number, "N", of records per category.

Adding and deleting records in a temp table without bloating the main accdb required an external accdb for the temp table.

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

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.

The demo was created for a question at Utter Access about recordings and artists. One artist can record many songs, one song can be recorded by many artists; that's the many-to-many. Two main form/sub form pairs allows 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

Simple Accounting Principles in a Tenant Billing Demo

This question at Utter Access was about accounting in an Access database: Billing tenants for charges at leased properties. We needed to rack charges and payments in two different workflows and calculate balance due. I also ended up with a fairly complex union query for one  report.

This one only has an Access 2007 version built entirely with embedded macros. It worked out really well.

Create and Completely Hide a DUAL Table in Access

Here's a way to completely hide an Access table.

Use a Hidden "System-Like" table to support UNION queries in Searching Combo Boxes

Dual tables have characteristics similar to the Oracle DUAL table:

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.

Most of the code in this demo was created by my friend Banana Republic

Travel "Pack" Demo

One-to-Many data entry requires a main form/sub form approach.

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 Filter Form and Searching Combo Box

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

This one also shows selecting a language option for labels on the Access 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).