Write HTML using VBA
Home Services Thanks Free Access Tips and Tools About MS Access Gurus Contact

Mainform + Subforms for entering and displaying customer payments and calculating balances

quick and flexible ...
shows calculations

Quick Jump To Sections


Mainform with subforms

Download ACCDB

1. Mainform-Subforms_Payment.zip
(280 kb, unzips to a 1.5mb Access ACCDB file with tables, queries, forms, and VBA code -- Oct 2017)  

2. Subform-Display-Calculations.zip
(343 kb, unzips to a 1.8mb Access ACCDB file with tables, queries, forms, and VBA code; has above + a few enhancements -- Nov 2017)  

Written by crystal (strive4peace). Licensed under CC BY-NC-SA 3.0, ' Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported

Windows Clipboard feature calls code originally by Terry Kreft and modified for 32/64-bit.

Goto Quick Jump

Video Tutorials

1. Overview: Mainform + Subforms for efficiency and flexibility (cc)

Click to Watch on YouTube

or CLICK the ARROW in the middle of image to watch the video

This video shows how to use a Mainform + Subforms to find and enter data for payments on orders in Microsoft Access. The sample data is from a custom shop that builds and sells portable storage buildings that are delivered to your location. When buildings are ordered and sold, the amount due may be paid outright, carried in-house, or purchased by a finance company.

Data entry into multiple tables is done efficiently, and solidly, using a mainform with subforms. There are also calculations, a variety of ways to find and filter information; Synchronizing subforms so they update using LinkMasterFields and LinkChildFields; Calculating sum and total number of orders and payments, and the balance due; Displaying the company logo; and more.

Press ESCape to undo changes to controls and records -- and sometimes do it twice -- look first though. See shortcuts and quick ways to enter data, and about using Access Options to set the Enter key behavior so pressing Enter goes down one row, just like Excel. Disburse all the is remaining on an allocated amount left to a single order by double-clicking. See how Access streamlines everything.

Anchoring subforms and controls enables them to stretch and shrink as the display area changes.

Goto Quick Jump

2. Subform to show Calculations (cc)

Click to Watch on YouTube

or CLICK the ARROW in the middle of image to watch the video

Look below the covers at a subform control, and the form that is inside it. Explore properties in this example that most of you will be able to relate to.

A Microsoft Access subform is used to synchronize the display of calculations to relevant data in the mainform

A subform control is a container, like a bucket, for a form or report. Its properties specify: what it contains (Source Object) , how it is linked (Link Master Fields, Link Child Fields), what it is called (Name), how big it is (Width, Height), where it is (Top, Left), what it looks like (Border Color, Border Style, Special Effect), whether or not it shows (Visible), if the user can modify values (Locked, Enabled), and so on.

As you navigate from record to record in the mainform, data in the subform automatically changes. LinkMasterFields and LinkChildFields are used to synchronize the forms, without any more effort on your part!

On the mainform, LinkMasterFields is a combobox that stores CustomerID but shows the customer name and more. What is displayed in the combobox is influenced by Column Count, ColumnWidths, and ListWidth.

The form inside the subform control is a regular form designed to be used as a subform; and in this case, to display information only, so things like RecordSelectors and ScrollBars are turned off. The RecordSource for the subform (where it gets its data from) is a query that has 2 queries below it doing more calculations. See how queries are stacked to get statistics from multiple tables that are not directly related into one place.

Take a deeper look at the SQL statements that Access stores when you create queries. Get an understanding of the different query views. Switch between SQL View, Design View, and Datasheet View. See how aliases are used for tablenames and calculated field names; and how a Left Join displays graphically with an arrow.

see the magic of Access happen ~

Goto Quick Jump

Future lessons will go into the nuts and bolts of how this form works, fix bugs, and enhance capabilities as desires grow.

Did you learn something new, or get reminded of something you already knew but had forgetten about?

Please Like, Comment, and Share with your friends. Thank you.

Through sharing, we will all get better.

~ have an awesome day ~

Free Book to Learn Access

Access Basics

Free Video Tutorials

Learn Access Playlist

Did you learn something new?

Do you need help with an Access project?


© Copyright 2017 crystal long.  All rights reserved.
Last Updated:  November 2017