6 Reasons Why I No Longer Use Bound Forms to Add Records in Microsoft Access

Just because something can serve two purposes, doesn't mean it should. (Reason number four can be a real game-changer.)

6 Reasons Why I No Longer Use Bound Forms to Add Records in Microsoft Access

Early in my career as an Access developer, the late David Fenton (at the time, the top user in the ms-access tag on stackoverflow) dropped a bomb on my preconceived notions of form design in Microsoft Access in the following StackOverflow comment:

I don't do most record additions in a bound form in the first place -- I tend to use an unbound form to collect the required fields, insert the record, then load the resulting new record into the main data edit/display form.

Young me found this concept to be an absurd waste of effort.  Why build and maintain two different forms when you can combine both functions into a single form?

Ten years later, I finally know the answer to that question.  In fact, I can think of at least six different answers to that question.

6 Reasons to Use Unbound Forms to Add Records in Access

  1. You tend to validate data differently when you add a record versus when you edit a record
  2. The visual distinction between "This is the form I use to add a record" versus "This is the form I use to edit a record" makes the user interface more intuitive
  3. You don't have to worry about the user tabbing out of the last control of the last record and accidentally creating a new record
  4. You can use entirely different UI paradigms for adding (e.g., a multi-step wizard) and editing (e.g., a single compact form)
  5. You completely avoid this super-nasty bug when connecting to tables in a SQL Server merge replication
  6. You maintain the future flexibility to do all of the above, even if you don't take advantage of those options right away

But isn't that double the work?

You spend more time re-writing code than you do writing code.  

So, while it may be less work initially to build a single, dual-purpose form, you will spend more time over the long run fighting to reconcile the differing needs of the add and edit process if they are built into the same form.

I have created many non-trivial, dual-purpose forms over the years.  The logic behind those forms started out very clean.  But over time, edge cases crop up that require different logic based on whether the record is new or already exists.  Eventually, that initially-clean code gets uglier and uglier.  

That's when you step back and realize most of that ugliness would fall away if you weren't using the same form for the two fundamentally different processes of adding and editing records.  

Image by Colin Behrens from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0