The Heart Break of Code Wadding

Some issues with interface design for Relational Database Applications come up over and over again.

One of those is the problem of adding new records in a junction table. Here's what the tables look like. The junction table, called tblArtist_Songs in this example, joins artists and the songs they've recorded in a Many-to-Many Relationship.

Artists, Songs and Recordings of Songs in a Junction Table

• Each artist can record one or more songs.
• Each song can be recorded by one or more artists.
• Each recording of a song by an artist occurs in a particular year.

Less experienced MS Access developers frequently try to create a query including all three of these tables to serve as the recordsource for a single data entry form. Something like this would be a typical result. It would be bound to a single form to manage artists, songs and records all in one.

SELECT
tblArtist.ArtistID
, tblArtist.Artist
, tblArtist.ArtistGroup
, tblArtist_Songs.ArtistID
, tblArtist_Songs.SongID
, tblArtist_Songs.YearRecorded
, tblSong.SongID
, tblSong.Song
FROM tblSong INNER JOIN
(tblArtist INNER JOIN tblArtist_Songs ON tblArtist.ArtistID = tblArtist_Songs.ArtistID)
ON tblSong.SongID = tblArtist_Songs.SongID;

It almost never works as the recordsource for a form.

Once they realize it's not going to work, the next step is -- all too often -- to resort to VBA. When that happens, the neophyte decides to use a function to select values from controls on a form and insert them into the various tables using a Recordset, or perhaps a couple of dynamic SQL Statements.  Abandoning the benefits of the bound form in Access in favor of code sort of seems like an acceptable compromise.

Code Wadding

I call this approach to solving design problems "Code Wadding." You start with a small bit of code, and keep sticking new bits to it as you encounter new complications. Instead of resolving the basic table or interface design problems, you simply build a bigger wad of code around those problems.

When you have to write a wad of code to solve a basic design problem, you're probably going to regret it sooner or later. Code requires maintenance. Code requires error handling. Code requires additional interactions between the interface, the data, and the user. Code is really important, don't get me wrong. But applying an additional layer of code to all of the essential code in your MS Access Relational Database Application tends to make the code wad bigger, stickier and harder and harder to manage.

Basic Design Principles

In my opinion, the alternative to Code Wadding is adherence to basic design principles. Access became the most popular development tool for Relational Database Applications for many reasons. One of those is that it emphasizes and supports good design principles. It's easy to make mistakes, that's also true. However, the people who created Access were smart enough to make sure it's easy to build highly functional relational tables and interfaces based on good design principles.

Main Form - Sub Form Data Entry

Returning to the subject at hand, the proper interface design principle for Many-to-Many relationships is a main form/sub form design. The main form is bound to either of the two "one-side" tables. The sub form is bound to the junction table. One of the controls in the subform is a combo or list box which is bound to the foreign key for other "one-side" table.

The screenshot below illustrates the relevant characteristics of the main form/sub form design.

Main Form - Sub Form Design for Many-to-Many Relationships

• The subform is bound to tblArtist_Songs. 
• The main form is bound to tblArtist.

Although the Primary Key for tblArtist is visible in design view, it would normally be hidden when the form is displayed in normal view. It's an Access AutoNumber. Being a surrogate Primary Key, it's better NOT to show it to users.

In the subform, bound to tblArtist_Songs, the foreign key to SongID is bound to a combo box, as shown. The other foreign key for tblArtist is hidden in the subform. However, the subform control has been set up to use the ArtistID from the main form, called "Link Master Fields" in the property sheet, to populate the corresponding (hidden) control for the ArtistID in the subform. It's identified as "Link Child Fields" in the property sheet.

That's really all that is needed to make this work. Although you can add code to handle certain data entry errors or other tasks, it's not necessary to add "wads of code" to allow users to add, update, or delete records in the junction table bound to the subform.

In the sample database I created to illustrate this technique, I actually created two sets of forms. That allows users to start with artists and add recordings for existing songs, or to start with songs and add recordings for existing artists.

Rather than wasting time and energy trying to build a bigger wad of code, see if you can't implement a simple, basic interface design to do the job with a minimum of code.