Figure 1 shows the subform that displays the number of and payments for orders for an individual customer, as well as the amount that customer owes. This particular customer made five orders, for a total owed of $12,340.07, and has made 29 payments totaling $8,055.56. The result is a balance due of $4,284.51.
The name of this form is fsub_CustomerBalance. The “f” at the beginning of its name signifies this is a form, and “sub” indicates it’s designed to be used as a subform. The underscore is used to separate the classification from the descriptive part of the name that describes the form’s primary purpose: “CustomerBalance.” But before creating the subform, we need to get the data for it first.
THE UNDERLYING QUERY
The subform is based on a query called qCustomerBalance, which contains one record for each customer. The tricky part in building the subform is getting the query to provide the needed information. The data comes from multiple tables and involves calculations. The Orders table stores customers’ purchases. Payment information is in the Payments and PayGroups tables. To get it all in the qCustomerBalance query requires two intermediate queries.
The first query, named qCustomerSumOrder, calculates how much a customer needs to pay. It is based on and includes four fields from the Orders table. The first field is the CustomerID. Its Total line should be set to Group By. Next comes Sum Order: CalcTotal, with the Total set to Sum. As its label indicates, this gives us the sum of the customer’s orders, which will appear in the subform. Third is CountOrder: OrderID, with the Total set to Count. This gives us the number of orders the customer has. Finally, there’s MaxDateOrder: DateOrder, with the Total set to Max. This gives us the date of the most recent order.
The second query figures out how much a customer has paid. The qCustomerSumPayment query uses the PayGroups and Payments tables as its sources. Like the previous query, it’s grouped by CustomerID. SumPayment: Payment, with the Total set to Sum, calculates the total payment amount. CountPayment: PaymentID, with the Total set to Count, provides the number of payments. And MaxDatePay: DatePay, with the Total set to Max, gives the latest date a payment was made.
Finally, qCustomerBalance uses both of these queries to bring all the elements together. The join line goes from CustomerID in the qCustomerSumOrder query to CustomerID in the qCustomerSumPayment query. Right-click it and choose Join Properties. Include all records from qCustomerSumOrder and only those records from qCustomerSumPayment where the joined fields are equal. This will ensure that orders show even if there aren’t any payments. Display CustomerID from qCustomerSumOrder and add the rest of the fields from both queries, except CustomerID from qCustomerSumPayment. Add Balance: [SumOrder]-Nz([SumPayment],0) to the query to calculate the balance.
Once the data is all lined up, this query can be used as the data source for the fsub_CustomerBalance form. Create that form and adjust the design to display the data however you want it to appear.
ADD TO MAIN FORM
The next step is to add the subform to the main form. Open the main form in Design view. In the Navigation Pane, find the fsub_CustomerBalance form in the list of objects. Click and drag it into the main form’s design. When you do this, Access automatically adds a subform control to the main form.
The subform control is a container for the fsub_CustomerBalance form. The control’s properties specify how this subform functions within the main form. This includes what it contains (Source Object), how it’s linked to the main form (Link Master Fields, Link Child Fields), what it’s called (Name), its size in the main form (Width, Height), its location (Top, Left), appearance (Border Color, Border Style, Special Effect), whether or not it displays (Visible), if the user can modify values in it (Locked, Enabled), and more.
A customer combo box is used on the main form to select the customer the user wishes to work with. This control stores CustomerID and displays the customer name. Selecting the customer there tells the subform to display that customer’s balance from the qCustomerBalance query. CustomerID is used because it’s the primary key (set to AutoNumber) in the Customers table. (In related tables, CustomerID is a foreign key defined as a Long Integer with no default value.)
To synchronize the information so that the subform automatically changes when the record in the main form changes, the LinkMaster Fields and Link Child Fields properties for the subform control are each set to CustomerID (see Figure 2). That’s all you need to do. Access will then do all the heavy lifting.
Download this month’s database: SF_1712_Database_Subform-Display-Calculations.
SF SAYS
Subforms can be used to display calculations that automatically synchronize to the information displayed in the main form.
December 2017
