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

Article

In this article, we are going to learn how to build a split transaction system in Microsoft Access. This is a technique commonly found in accounting software such as QuickBooks, where you can take a single financial transaction and divide it among multiple categories. This method allows you to record, for example, a single payment that should be allocated across different expense accounts or departments, providing greater flexibility and detail in your financial records.

To start, let's first understand why split transactions are necessary. Most simple databases only allow you to categorize each transaction under a single label or category. Imagine you make one $100 purchase at a store, but $60 is for office supplies and $40 is for refreshments. With a basic system, you would have to enter those as two separate transactions, even though there was only one payment. This leads to confusion and makes tracking the overall flow of money more complicated. By using split transactions, you can keep a single transaction linked to several categories, each with its own amount, keeping your database organized while also simplifying the entry process.

The data model for split transactions relies on a parent-child relationship. The parent table, usually called Transactions, contains the main details of the transaction: date, vendor, total amount, and any general notes. The child table, often named TransactionSplits, references the parent transaction with a foreign key, and contains the specific category, split amount, and any additional notes for each split. This way, each transaction can have any number of splits, letting you allocate the total across as many categories as you need.

For example, your Transactions table might include:

TransactionID (Primary Key)
TransactionDate
Vendor
TotalAmount

Your TransactionSplits table would look like this:

SplitID (Primary Key)
TransactionID (Foreign Key to Transactions table)
CategoryID (Foreign Key to a Categories table)
SplitAmount
Notes

Once the data structure is in place, you will want to build forms to manage the data. Start by adding a subform to your main transactions form to display the split lines underneath the main transaction details. This subform should show which categories and amounts are associated with the current transaction. While you could allow editing directly in this subform, it is often better to use a separate pop-up form for editing splits. This keeps the logic cleaner and lets you control edits more easily.

Set up your subform to be read-only, and place an Edit Splits button on the main form. When the user clicks this button, it opens a pop-up form tied to the same transaction. In this editor, the user can add new split lines, edit existing ones, or delete splits they no longer need. As the user makes changes, your VBA code should validate the total of all split amounts against the transaction total. If the total does not match, inform the user and keep editing disabled until everything balances.

To handle user mistakes or changes of mind, add rollback protection to your split editing form. Before the user starts making changes, store the current set of splits for the transaction. If the user cancels out of the pop-up form, use your saved copy to restore the original splits. This keeps your data consistent and prevents accidental changes from being saved. You can achieve this by copying the split records to a temporary table or variable and reloading them if required.

Here is an example of basic VBA code to restore original splits if the user cancels:

Private Sub Form_Unload(Cancel As Integer)
If Me.Dirty Then
If MsgBox("Discard changes to splits?", vbYesNo) = vbYes Then
' Code to restore split records goes here
Else
Cancel = True
End If
End If
End Sub

You could also use TempVars or hidden forms to hold the original values, depending on your preferred approach.

Improving usability is important, too. Users often want to split transactions by percentage rather than calculating exact amounts. To make this easier, set up your split editor so users can type a percentage value into the amount field (for example, entering 25 for 25 percent of the total). Use VBA to automatically calculate the actual amount for that split line and update it. You can check the sum of all split amounts as records are updated, and display how much remains to be allocated. As soon as all but the last split are entered, automatically assign any leftover amount to the final split, saving the user from manual math.

A simple snippet for percent-based entry might look like this:

Private Sub SplitAmount_AfterUpdate()
Dim perc As Double
Dim amt As Double
perc = Nz(Me.SplitPercent, 0)
If perc > 0 And perc <= 100 Then
amt = (perc / 100) * Forms!TransactionForm!TotalAmount
Me.SplitAmount = amt
End If
End Sub

You can expand this code to handle cases where the remaining amount needs to be filled in on the last line, or to keep the splits synchronized with the total.

When you combine all these techniques, you will have a powerful and flexible framework for handling split transactions in your Access database. Not only does this let you match the functionality seen in leading accounting software, but you also gain valuable experience in table design, form building, event programming, and user interface improvements using VBA.

While this tutorial covers some advanced techniques, you do not need to be a VBA expert to get started. A solid foundation in relational databases, forms, and event-based programming is helpful. You will use TempVars, some SQL, and basic VBA events, but not the most advanced features like recordsets.

By following these steps, you can build a split transaction system that is robust, user-friendly, and extends the capabilities of your Access database far beyond simple single-category transactions. If you have questions or get stuck, remember that there is a thriving community available in Access forums where you can ask for help and share your own solutions. Good luck as you implement split transactions in your own projects!