Access Developer 54

Split Transactions with Rollback & Percent Splits

In Access Developer 54, we will walk through building a split transaction system in Microsoft Access, similar to those used in accounting software, to allow a single transaction to be divided across multiple categories while maintaining balanced records. We will discuss why split transactions are used, how to design the data model with parent and child tables, create forms and a pop-up editor for managing splits, add rollback protection to keep data in sync, and include features like percentage entry and automatic balance calculation. This framework can be adapted for use in your own Access databases.

Lessons

•  00. Welcome (4:18)
•  01. Split Transactions 1 (19:25)
•  02. Split Transactions 2 (18:21)
•  03. Split Transactions 3 (18:11)
•  04. Split Transactions 4 (19:26)
•  05. What's Next (3:31)
• Total Runtime: 1 hour 23 minutes

Resources

•  File Download
•  PDF Handbook

Navigation

• Prev: D53
• Next: D55

Lesson Summary

Access Developer 54: Split Transactions, Rollbacks - 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.

Lesson 1: Split Transactions: Data Model & Table Setup - In this lesson, we will talk about the need for split transactions and how to structure them from a data design perspective. I will explain the limitations of a single-category transaction model and show how to build the required tables to support splitting a transaction among multiple categories using a parent transaction record and child split records. We will look at creating category and split tables, discuss examples, and outline the rules for tying split amounts back to the main transaction. By the end, you will understand the data structure we will use for the rest of the series.

Lesson 2: Split Subform: Edit Button, Save Validation - In this lesson, we will walk through building the Split Subform and a pop-up editor in Access to manage split transaction records. We will create a display-only subform to show the split categories and amounts, and set up an Edit Splits button that opens a modal dialog allowing users to add, modify, or remove split lines. We will set up form properties to ensure data integrity, establish relationships between forms, and implement basic validation to make sure split totals match the main transaction amount. We will discuss the reasons for separating editing into a pop-up for better control and validation.

Lesson 3: Track Changes, Rollback Splits on Cancel - In this lesson, we add automatic split editing and rollback protection to the transaction form. I show how to automatically open the split editor when debit or credit amounts are changed, store previous values before edits, and restore those values if the user cancels. We set up a temporary table to save split transactions for rollback and implement code to ensure that canceled changes revert both the main transaction and associated splits to their original state. We'll discuss ways to identify records that need splits and preview a new feature on calculating split percentages to be covered in the next lesson.

Lesson 4: Enter Split Percents & Calculate Remainder - In this lesson, we will implement percent-based split entry in Microsoft Access, allowing you to enter a percentage like 10% and have Access calculate the split amount automatically. I will show you how to set up the form so that the remaining unallocated transaction amount is calculated and displayed for the next record. We will discuss handling field events for validation and recalculation, setting default values dynamically, and updating totals as records are edited. Future enhancements like saved transactions are briefly mentioned but not demonstrated.

What's Next: Split Transaction Systems & Next Steps - In this video, I wrap up Developer 54 by summarizing how to build a full split transaction system in Microsoft Access. We review designing the appropriate data model, creating forms to manage transactions and splits, implementing rollback protection, and adding features like percentage-based entry and balance calculations. I discuss possible future topics and changes to the website format, and I invite your feedback on what you would like to see in upcoming lessons.

Outline - 0. Welcome (4:18) Microsoft Access Split Transaction System with Popup Editing, Rollback Protection, Percent Entry 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 1. Split Transactions 1 (19:25) Split Transactions in Microsoft Access: Understanding the Data Model and Building the Tables Limitations of single category transactions Scenarios requiring split transactions Problems with multiple category fields in transactions Benefits of split transactions for financial tracking Parent and child table structure for split transactions Building a one-to-many relationship for splits Designing the transaction table as the parent Creating a split records table for categories Handling totals and ensuring split amounts match parent Building a Category table with CategoryID and CategoryType Populating sample category data Designing a split table with references to transaction and category Storing split amounts as currency, not percentages Adding and testing sample data for split records Creating a category query for combo boxes with income/expense labels Preparing the initial check register database for splits Overview of using a modal form for editing splits 2. Split Transactions {PART} (18:21) Create the Split Subform with Edit Button and Save Validation Building a continuous split subform for transactions Creating a combo box for category selection Linking subform to parent form via master/child fields Displaying split amounts and categories in subform Calculating and showing total split amount in subform footer Configuring subform as display-only (read-only) Adding an Edit Splits button to open a pop-up editor Designing a modal pop-up form (SplitEditF) for editing splits Locking down the display subform (no adds/edits/deletes) Populating new split records with the correct parent ID Adding Save and Cancel buttons to the pop-up editor Preventing closure of the pop-up editor via control box Defaulting hidden parent transaction ID field in editor Validating totals before saving split records Using Me.Dirty, Me.Recalc, and DoEvents for data commits Enforcing that split totals match the transaction amount Refreshing subforms after editing splits in the pop-up form 3. Split Transactions {PART} (18:11) Store Previous Amounts and Rollback Split Records When User Cancels Edits Automatic opening of split editor on debit or credit change Storing previous debit and credit values before editing Restoring original values if user cancels split editing Using TempVars to retain previous field values Creating a temporary rollback table for split transactions Copying current splits into rollback table on edit Restoring splits from rollback table after cancel Deleting and inserting records in Split and Rollback tables Requerying the form after rollback to refresh data Ensuring data is committed before rollback procedures Handling uncommitted records during rollback Synchronizing transaction and split data after editing 4. Split Transactions {PART} (19:26) Calculate Split Amounts from Percent Input and Auto Fill Remaining Balance Adding percent-based split entry in Access Detecting and handling percent sign input Converting percent text to numerical value Using TempVars to pass values between events Calculating split amounts based on percentage Setting split amount in After Update event Automatic calculation of remaining amount Recalculating remaining split amount on record change Dynamically updating default value for split amount Handling default value during record editing Synchronizing calculations with form events Creating a DoRecalc subroutine for recalculation Updating totals immediately after split changes 5. What's Next (3:31) Reviewing Split Transaction Systems and Next Steps This is a video conclusion and community update message, summarizing what was covered in Access Developer 54 and discussing upcoming plans and viewer feedback. 6. Access Developer 54 (0:00) Access Developer 54 Outline

Keywords