Welcome

Access Developer 54: Split Transactions, Rollbacks

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this class, we will build a split transaction system in Microsoft Access similar to what is used in accounting software, allowing one transaction to be divided among multiple categories. We will discuss why split transactions are useful, how to design the necessary parent and child tables, and walk through creating forms, including a pop-up editor for managing splits. We will also cover adding rollback protection so changes can be safely undone, and I will show you how to implement percent-based split entry and usability features. Each lesson will focus on a specific aspect of creating this framework.

Navigation

Prev: D54
Next: Lesson 1

Keywords

Access Developer, split transaction system, accounting software, QuickBooks, parent transaction table, child split table, pop-up editor, rollback protection, percent-based splits, tempvars, SQL, event programming, split subform, Edit Splits button, automa

Subscribe to Welcome
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 54 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today we are building a split transaction system in Microsoft Access. It is the kind of thing you see in accounting software like QuickBooks, where a single transaction can be divided across multiple categories.

We will start by looking at why split transactions are needed and how to design the proper data model using a parent transaction table and child split table. From there, we build the forms to display and edit those splits, including a pop-up editor that keeps everything organized and easy for the user to manage.

We will then add protection to the system so if the user cancels their changes, the original values are restored, everything is rolled back, and the splits stay perfectly in sync with the transaction. Finally, we will add some developer-level usability features like typing in a percentage to automatically calculate split amounts. No one likes to have to do math in their head. We will also automatically fill in whatever balance remains on the bottom.

By the end of this class, you will have a solid, flexible framework for handling split transactions in your own Access database, plus a bunch of useful form and VBA techniques you can apply to other projects.

This is a developer-level class, so I strongly recommend you finish all my Beginner, Expert, Advanced, and Developer classes before this one. You do not need to have too much VBA knowledge under your belt. We will be using tempvars, some SQL, and of course event programming, but we are not going to be using recordsets or any other advanced stuff. So again, just more tools for your box. I recommend you do not skip levels, but if you have to, you have to. I get it.

It is currently March of 2026, so Access 2024 is the closest retail version, or get yourself an Access 365 subscription. If you have questions about the material covered in today's class, post them down below. Every video has its own little forum below it, so you can post source code, you can type whatever you need down there. If you have generic Access questions, post them in the forums.

Let's get a quick breakdown of each lesson today.

In lesson one, we are going to talk about why split transactions are needed and how they work from a data design perspective. We will look at the limitations of a simple, single-category transaction model, and then we will build the tables necessary to support split transactions with a parent transaction record and split child records.

In lesson two, we will build a split subform and a pop-up editor. It is easier to manage if there are two separate forms, trust me. We will create a display-only subform to show the split categories and amounts on the parent form. Then we will make an Edit Splits button that opens a pop-up form where the user can safely add, change, or remove split lines while we validate totals and match the parent transaction. It is easier to put all your logic in a pop-up form.

In lesson three, we are going to add automatic split editing and rollback protection to our transaction form, so if the user makes changes and they decide they do not want any, we can automatically roll back the original transaction and all of the split edits as well. This way, everything stays in sync.

In lesson four, we are going to add percent-based split entry so you can type in 10 or 15 or whatever, and we will also see how much is left to go into the splits for the transaction.

That is what is going to be covered in today's class. Sit back, relax, grab your coffee, and get ready for lesson one. Remember, watch each lesson completely through, and then do it again and follow along with me.

Here we go.

Quiz

Q1. What is a split transaction system in Microsoft Access designed to do?
A. Allow a single transaction to be divided across multiple categories
B. Merge multiple transactions into one
C. Convert all transactions to a single category
D. Monitor user access to the database

Q2. Why are split transactions needed in accounting software?
A. To make the database run faster
B. To divide a transaction into multiple categories for accurate accounting
C. To reduce disk space usage
D. To allow batch deletions of transactions

Q3. What tables are required for a split transaction system?
A. Only a single transaction table is necessary
B. Separate tables for each user
C. A parent transaction table and a child split table
D. One table for incomes and one for expenses

Q4. What interface is created to manage split transaction data in Access?
A. Only a single main form
B. A split subform and a pop-up editor form
C. A report-only interface
D. A simple Excel export

Q5. When editing splits, what usability feature is mentioned to improve data entry?
A. Auto-saving with every keystroke
B. Typing percentages to automatically calculate split amounts
C. Locking all fields until validation
D. Exporting splits to CSV automatically

Q6. What does rollback protection do in the split transaction system?
A. Deletes all data after editing
B. Prevents any changes from being saved permanently
C. Restores the original transaction and splits if the user cancels their changes
D. Forces the user to finish all entries before exiting

Q7. How can users see the remaining balance to be split in a transaction?
A. The system automatically fills in whatever balance remains on the bottom
B. Only by running a special query
C. By printing a report
D. It is unavailable unless the database is refreshed

Q8. What level of experience is recommended before taking this class?
A. No experience required
B. Only Beginner level required
C. Completion of Beginner, Expert, Advanced, and Developer classes
D. Only basic SQL knowledge is necessary

Q9. Which of the following is NOT specifically mentioned as being required knowledge for this class?
A. Tempvars
B. SQL
C. Recordsets
D. Event programming

Q10. If you have questions about the class material, what should you do?
A. Call the instructor directly
B. Email support only
C. Post questions in the video's forum below each video
D. Contact Microsoft Access support

Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-A; 8-C; 9-C; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on building a split transaction system in Microsoft Access, similar to what you find in accounting software like QuickBooks. This is important when a single transaction needs to be divided across several categories, making it more flexible and useful for managing complex financial data.

I begin by explaining why split transactions are necessary and outline the correct way to design a data model for them. The structure involves a main transaction table that serves as the parent, along with a related split table that holds the individual breakdowns for each transaction. This allows us to associate multiple detail records with a single transaction header, which is essential for tracking how a payment or receipt is divided among categories.

After setting up the tables, we move on to designing the forms users will need in order to view, add, and edit split transactions. I show you how to create an easy-to-use subform to display these splits right on the parent form, and then walk through building a pop-up editor for managing them. Using a pop-up makes it much easier to keep the process organized, as all logic for editing and validating the splits is centralized. With this setup, users can safely make adjustments without worrying about breaking the sync between the splits and the main transaction.

A key requirement for any robust application is protecting user data. In this session, I add features so that if someone changes their mind and cancels out of the split editor, all their changes are undone, and the data reverts to its original state. Everything gets rolled back seamlessly so the transaction and all its splits remain in sync at all times.

From there, we implement some smart usability enhancements. For instance, I show you how users can type a simple percentage, and the form will automatically calculate the appropriate split amount. Handling percentages this way keeps things simple and saves people from doing the math by hand. We also add a feature where the remaining balance automatically fills in on the bottom row, speeding up data entry and reducing errors.

Once you finish this class, you will have a powerful and adaptable system for handling split transactions in your Access projects, and you will pick up a number of practical form-building and VBA skills along the way that you can apply elsewhere.

As this is a developer-level session, I recommend completing my Beginner, Expert, Advanced, and Developer classes before proceeding. While it's helpful to have some basic understanding of VBA, you do not need advanced experience. In this class, we use things like tempvars, some SQL, and event programming, but not more complex techniques such as recordsets. If you have to skip ahead, that is up to you, but for a smoother experience, it is best to follow the recommended learning path.

This video was recorded in March 2026, so either Access 2024 or an Access 365 subscription will work for following along. If you have questions about anything I cover here, feel free to post in the comments section right below the video, which doubles as a little forum. You can also post code or general Access questions in the main discussion forums on my website.

Here is a quick lesson-by-lesson overview of what to expect:

Lesson one introduces the need for split transactions and how they should be set up from a data modeling point of view. We look at why a single-category transaction model falls short and build both the parent and split child tables for supporting this structure.

Lesson two covers how to build the user interface for managing splits. We design a subform to show the split categories and amounts, and then set up a separate pop-up editor. This layout keeps things easier to manage and allows for a smoother editing experience, where users can add, modify, or remove splits, all while totals are validated and matched to the parent transaction.

Lesson three adds automatic editing and rollback features. If the user decides to cancel their changes, the system will instantly revert to the original transaction and split data, maintaining synchronization.

Lesson four introduces percent-based splits so users can type in percentages for their allocations, and the system will calculate the split amounts automatically. We also make sure the form displays any remaining balance, which speeds up data entry.

That summarizes what will be covered in this class. I encourage you to watch each lesson from start to finish first, and then go through again step by step while following along. This approach will help reinforce everything and ensure you build confidence with each topic.

You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Split transaction data model design

Creating parent transaction and child split tables

Building split subform for display

Creating pop-up editor form for splits

Editing splits via pop-up form with validation

Rolling back split and transaction changes

Ensuring data sync between splits and parent transaction

Percent-based split entry and calculation

Automatically filling balance for split transactions