A subform is a great way to
to show calculations on a form.
This database uses queries to aggregate
and calculate data from multiple tables,
and show the results for each customer on a subform
that is synchronized to the mainform
hi, this is crystal
In the Mainform + Subforms lesson, I overviewed the database
that is being used to record customer payments.
This time, we dive deeper into one of its subforms.
This subform shows the sum of orders, the number of orders,
the sum of payments, and the number of payments.
While the balance could be calculated on the form,
it is actually calculated in the underlying query.
The name of this form is fsub_CustomerBalance
The 'f' in the beginning signifies this is a form;
'sub' means it is designed to be used as a subform.
Underscore is used to separate the classification
from the more descriptive part of the name, 'CustomerBalance'.
If you double-click, the form is launched.
In other words, double-clicking opens the form.
The titlebar shows the form name
If a Caption is set, that would show instead.
The property sheet shows that
Record Selectors are set to No
and Scroll Bars to Neither.
This saves space.
Window properties such as Control Box,
Close Button, and MinMax Buttons
won't apply when the form is a subform.
When this form is opened from the navigation pane,
it displays the first record it finds
because there is nothing to direct it to do anything else.
Without reference information,
what we see doesn't make any sense.
That is because this form
was designed to be used as a subform.
When you name the objects you create in Access,
choose logical names.
Also think about uppercase and lowercase.
Access does not generally make a distinction between case,
but human eyes do.
In this database, CAPITAL letters are used
in names of mainforms and menus
so an eye can see them faster.
When a form is dragged from the Navigation Pane
to the design view of another form,
it becomes a subform.
Access automatically sets the Source Object property
to name of the form that was placed.
Source Object is fsub_CustomerBalance
If there is a relationship that Access can detect, it also fills
the Link Master Fields and Link Child Fields properties.
In this example, since the mainform is unbound,
there is no known data to link.
The LinkMasterFields property, however,
can refer to a control name that is unbound.
That means that a subform could be linked
to user specified information,
or information supplied by VBA.
On the payments mainform,
here is the customer balance subform.
The Property Sheet shows
the SourceObject is fsub_CustomerBalance.
To ease confusion, since this is a bound control,
the Name property is set to match.
CustomerID is in both LinkMasterFields and LinkChildFields
because the same name is used for
all field and control names that hold the CustomerID.
You can see that on this relationships diagram.
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), and so on.
Padding is the extra space around an object,
so if you are trying to tighten space,
you can set these to 0.
In the form view, as you navigate from record to record,
the information in the customer balance subform
automatically changes to reflect the current customer.
How is this linking done?
When you look at the design view of fsub_CustomerBalance,
you can see that the CustomerID key field
is on the subform in a control,
but the section it is in is not visible.
Using black for the back color
makes it obvious when one looks at the design view
that the section does not show.
CustomerID is what will be used to synchronize this form,
when it is a subform, to the mainform.
The RecordSource for this subform
is a query called qCustomerBalance,
with one record for each customer.
On the mainform, the customer combo box stores the CustomerID
and displays the customer name.
CustomerID is the primary key in the Customers table
and is an AutoNumber.
In related tables, CustomerID is a foreign key
defined as a Long Integer with no default value.
In the query builder for the CustomerID combo box
you can see that there will be 4 columns
The first column contains the CustomerID and will be hidden.
CustomerID is also displayed in the last column (specificallyput there!).
The first column that will show is the customer name.
Then will come the sum of the order amounts.
On the Format tab, Column Count is set to 4.
The first column width is 0 so it is hidden
The Column Widths add up to 4 inches,
so the List Width is set to 4.2"
to give allowance for a scrollbar also.
In the form view, you can see the 3 columns that show
when the customer list is dropped.
This control provides the value for LinkMasterFields,
which is used to synchronize the subform.
Now that we see how the information changes with the customer,
let's examine the data below the form.
The RecordSource property is on the Data tab of the Property Sheet.
The property has the name of a query.
It could also be a table name,
or an SQL statement.
If RecordSource is blank,
the form is unbound;
or not bound to any data.
When you click in the RecordSource property,
to the right, you see a drop-down arrow
and a builder button.
Click on the builder button, ...
or press Ctrl-F2
This query, qCustomerBalance, is based on 2 other queries,
qCustomerSumOrder and qCustomerSumPayment.
Looking at the design view of qCustomerSumOrder,
you can see this data is grouped by the CustomerID,
and comes from the Orders table.
The calculated total for each order is summed by customer
and called SumOrder.
The number of orders, or Count,
is called CountOrder,
and the last date an order was placed is called MaxDateOrder.
The datasheet view shows the 4 columns of information.
Looking at the design view of qCustomerSumPayment,
you can see this data is also grouped by the CustomerID.
This data comes from 2 tables,
Payments and PayGroups.
Payments are summed by customer and called SumPayment.
The number of payments is CountPayment,
and the last date a payment was made is called MaxDatePay.
The datasheet view shows the result of the calculations.
Let's look again at at the balance query
We see in the SQL statement that shorter aliases
are used for the query names.
qCustomerSumOrder is called qOrder,
and qCustomerSumPayment is qPayment.
The design view graphically shows
what the SQL statement says.
The queries are linked on CustomerID.
The arrow on the relationship line indicates that
all records from orders will show
even if there are not any payments.
The fields that the subform will need are on the grid.
The Balance is calculated using [SumOrder] -Nz( [SumPayment] ,0)
The datasheet view shows the results,
and also the calculated balance.
In this lesson, you have seen how to use a subform
that is synchronized to data on a mainform
to show calculations made by queries.
Thanks for joining me
Through sharing, we will all get better