Current Microsoft MVP Awardees

MS Access™ MVP Awardee from July, 2007 through June, 2019


Download a demo accdb with working code and sample data

Accounting Functions in a Tenant Billing Database

*Updated -- March, 4, 2019

We avoid saving calculated values in relational database applications when we can. So how do you handle accounting functions with charges and payments on accounts?

This demo offers some examples of some Simple Accounting Functions (Charges, Invoices and Payments) in a Tenant Billing Database.

------------------------------------------------------------------------------------

Cascading Combo Boxes using Tempvars with Immediate If Function

*Updated -- January 07, 2020

This demo uses a filtering technique involving a combination of the Immediate If Function and tempvars to filter cascading combo boxes and form recordsources.

The main advantages of this approach are that it makes the query filter dynamic, but does not require rewriting SQL statements, and that it doesn't require a form to be open to filter on a value set in a form.

It requires a combo box with options for each record in a table, plus an option to return ALL records in the form's recordsource.

Download and experiment with the demo to see how a combination of Iif() and TempVars makes writing dynamic queries easy and flexible.

After receiving feedback, I added two additional forms to illustrate other aspects of cascading combo boxes in continuous forms using the tempvars filter.

*Modified -- February 20, 2020
Feedback from AUG presentation provided a simpler way to write WHERE clauses using this method. Thanks to Dale Fye for the idea.

*Updated -- February 23, 2020
UPDATE FOR LARGE RECORDSETS WITH DATE RANGE FILTER:
While testing this technique using a larger recordset filtering date ranges instead of primary and foreign keys, I discovered that the Tempvar filtering was extremely slow, with or without the functions created for tempvars, when dates had to be compared in each record.

The solution turned out to be very straightforward. By putting the date range filters in a subquery, I was able to retain the advantages of the IIF() and tempvar solution and achieve acceptable performance on date range filters up to several thousand records.

------------------------------------------------------------------------------------

Check for an Existing Value in a Table

*New -- February 2, 2016

Does a Value already exist in a field in a Table? Find out before trying to add it. This sample Access relational database application 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.

------------------------------------------------------------------------------------

Convert Normalized Rows to Non-Normalized Columns for a Report

*New -- November, 2020
The request which prompted this demo was to convert the rows in a properly normalized table into one or more columns that can be displayed side-by-side in a report. It turned out to be more involved than I thought, but after a couple of false starts, this is the result. You might find a better way to accomplish the task. If you do, please let me know.

------------------------------------------------------------------------------------

Create and Completely Hide a DUAL Table in Access

*New -- August 9, 2012

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, Grover Park George On Access, on this topic.

------------------------------------------------------------------------------------

Create Monthly Reports as Pivot Tables in Excel

*Updated -- May 22, 2020

*Compatible with 32 bit and 64 bit Access

Access is great for storing and managing data. However, Excel provides a better alternative for some kinds of reporting. One example is the ability to create pivot tables to faciliate analysis of that data.

This demo uses an Excel template file with two worksheets. One is a "landing page" into which Access data is exported. The other is the formatted "display page" which links to the landing page to display the data in whatever format is applied to it. This allows variable data to be used with custom Excel formatting.

This demo consists of monthly performance data for the operations department of a health insurance company.

------------------------------------------------------------------------------------

Lose Referential Integrity by splitting tables into multiple back ends

*New -- February 5, 2017

The nominal maximum size of an accdb is 2GB. However, in most relational database applications created with MS Access, the practical maximum size is much less, probably about half that. Unfortunately, one common suggestion offered to deal with "Access Bloat" is to split some of the tables out into multiple Back End accdbs, each containing a few of the tables from the original acdb.

While that reduces the size of each individual accdb, it comes at a hefty price: you lose the ability to enforce Referential Integrity between tables in the different accdbs. Within any single accdb, of course, you can still enforce RI physically in that accdb, but not across them. And, as you probably already know, you cannot enforce RI from the Front End accdb when the tables are in a separate accdb.

The solution? If a compact & repair doesn't restore the BE to a  manageable size, you will need to consider a more robust Relational Database Management Server (RDBMS) like SQL Server Express or SQL Server Developer Edition or MySQL.

The sample accdbs in this download illustrate the single accdb with interface and tables, a properly split accdb with FE and BE files, and a multiple BE split version, in which RI has been lost across the different accdbs.

------------------------------------------------------------------------------------

Main form/subform designs support many-to-many relationships.

*Updated -- March 21, 2020

*Compatible with 32 bit and 64 bit Access

When your relational database application requires many-to-many relationships, data entry becomes more complicated.

You shouldn't use multi-table queries as recordsources for data entry tables, although the many-to-many relationship would seem to indicate that.

This demo illustrates one way to enter records in all three tables in a many-to-many relationship without having to resort to multi-table queries or complicated VBA logic.

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.

------------------------------------------------------------------------------------

Multi-Field Query By Form Searching Combo Box

*Updated -- April 3, 2018

Finding specific records in a large table is a frequent requirement in most relational database applications.

A question at Utter Access brought this out. We needed to filter records on a form, but we found Microsoft templates were too limited for the task at hand in that scenario. I was challenged to make it work for a variety of situations and the result is the Multi-Field Filter Form sample accdb.

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

------------------------------------------------------------------------------------

Open Form In Add Or in Edit Mode

*New -- December 20, 2019
Access forms can be opened as:

  -- Read Only
 -- Add New Records Only
 -- Add New Records and Edit Existing Records

A single argument on the DoCmd.OpenForm action determines which data mode applies.

This simple Access Relational Database Application illustrates two of those options, leaving it to the user to try the third.

------------------------------------------------------------------------------------

Pre-Filtering for Combo Boxes and Free Search Text boxes

*Updated January, 2020
*Validated for 64 Bit Access January, 2020

When a combo or list box uses a large lookup table, populating that list can be quite slow.

Allen Browne created a technique to pre-filter combo boxes to overcome this limitation. I built the original demo around that technique. Recently I expanded it to include a lower code version incorporating Iif() and tempvars.

I also verified that it works properly with 64 bit Access.

------------------------------------------------------------------------------------

Random Selection of "N" members in each Category Plus Create a temp "side" accdb or mdb to avoid bloat

*New January 7, 2017

Random selection of a random number of values.

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

Bonus Code: Adding and deleting records in a temp table required creating a temp external accdb on the fly to avoid bloat. This function can be implemented in any Access relational database application.

------------------------------------------------------------------------------------

Scrolling Progress Message on a Form

*New -- November 19, 2019
A question at Utter Access led to this one.
The specific request was for a simple way to display a progress message in a scollable text box on a form with the most recently completed step always visible.

Another member suggested placing the most recently completed step at the TOP, rather than the BOTTOM, of the text box so that it is always displayed.

As is usually the case, I ended up adding a couple of additional features to make the demo more interesting. These can be used "as is" or replaced in your implementation of the scrollable text box into your Relational Database Application.

This demo incorporates a precise timer feature based on the GetTickCount() API to measure elapsed times as well as the scrolling message box.

------------------------------------------------------------------------------------

Select Last 10 Lines of Text in a Rich Text Field

*New -- February 13,2020

A question at Utter Access led to this one.

The existing Relational Database Application used a Long Text field to capture notes. Unfortunately, instead of adding a new record for each new note, though, users had to enter a new line, appending the new note to the existing lines. This led to extremely long strings of text, which cluttered up reports with lines that were out of date.

The request was a way to capture the most recent 10 lines of notes for the report while leaving the entire string of notes intact in the table.

The result was the function demonstrated here.

------------------------------------------------------------------------------------

Travel "Pack" Demo

*New -- July 5, 2013

One-to-Many Relationships in relational database applications are best handled with 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.

------------------------------------------------------------------------------------

Using Tempvars with Immediate If Function to filter a form's recordsource

*NEW -- December 18, 2019
This demo uses a filtering technique I created using a combination of the Immediate If function and tempvars to filter form recordsources.

The main advantages of this approach are that it makes the query filter dynamic, but does not require rewriting SQL statements, and that it doesn't require a form to be open to filter on a value set in a form.

It requires a combo box with options for each record in a table, plus an option to return ALL records in the form's recordsource.

Download and experiment with the demo to see how a combination of Iif() and TempVars makes writing dynamic queries easy and flexible.

------------------------------------------------------------------------------------

VBA Code to Find Previous Weekday

*Updated -- December 24, 2019

Date logic is a frequent problem for less experienced relational database application developers.

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

Bonus:
Because the question that prompted this demo was from a non-native speaker of English, I decided to add some simple language swapping in it to change captions on controls on the forms from one language to another (English and Español).

------------------------------------------------------------------------------------

George Hepworth and Grover Park Consulting

During a 2½ decade career building and maintaining MS Access™ databases, I've given back to the Microsoft Access database developer community by serving as an administrator or contributor at Utter AccessLinked In and other Access sites.  

Microsoft has repeatedly awarded me as an Access MVP .  Recently, we've been moved into a new classification, "Office App & Services". Nonetheless, I will always think of myself as an Access MVP.

 

Key Access database concepts demonstrated in one or more of the free demos on this page include: Table Design and Normalization, Aggregate Functions including DLookup(), Form Level Error Handling, VBA Error Handling, Embedded Macros, User Defined System Tables for Application Level Constants, Public Enums to provide User-Friendly Names for Error Codes, Using Control Tags to Store Constants, Access Form Control Layout Grid, Completely Hiding Tables, Creating a Scratch accdb for Temp Tables, VBA to Export Tables, VBA to Export Querydefs, Deleting an External, Second accdb File, Load Performance of Combo Boxes with Large Recordsets, Pre-filtered Combo Boxes, Dynamic SQL to Load Single Record Recordsets, Cascading Combo Boxes

©Copyright 2010-2020 Grover Park Consulting. All Rights Reserved.
Free Resources and Full Working Demos for Microsoft Access Database Design and Development