In this lesson, you will learn about using
a mainform with subforms to find and enter data.
Hi, this is crystal.
The sample data comes from a custom shop
that builds and sells movable storage structures
that are delivered to your property.
When a sale is made, it may be paid outright,
carried in-house,
or purchased by a finance company.
This is a mainform with subforms.
The mainform, based on the PayGroups table,
stores customer and date.
There are also calculations,
and ways to find and filter information.
There are 3 subforms.
A form is called a subform when it is
used by embedding it on another form.
The subform for payments is continuous,
and can display multiple records.
You can see amounts, where they come from,
and what they apply to.
A customer may make payments
on several orders with one check or charge,
or pay part of a purchase with cash
and part using another method.
Customer history is a subform
that is synchronized to the mainform using CustomerID
for LinkMasterFields and LinkChildFields.
It shows the sum and total number
of orders and payments,
and calculates the balance due.
In the upper left is yet another subform
that displays the company logo.
On the right are two list boxes showing information
relevant to the customer.
On top, the tan list box displays the customer purchase data
with serial number, order date, and balance.
Click on a row to copy the serial number
to the Windows clipboard.
Once copied, you can paste the serial number anywhere ...
to another document,
or into a form control.
I am pasting this on the last row,
so it turns into a new record ...
and another blank line for a new record appears.
See the pencil in the record selector area?
I don't really want to create a new record here,
so I press ESCape to undo the new entry;
and since the new record is not yet gone,
I press ESC again to undo creating a new record.
So the first escape undid the combo
and the second one undid the record.
The yellow list box shows payment dates,
how much was paid, the number of payment transactions,
and also finds a selected payment group.
As a date in the box is selected,
the payment group shows payments for the selected date
In the form header, there are unbound combo boxes
to find and filter records.
Filters can be set for customer, order, and/or date.
There are more than a thousand payment group records.
When the form is filtered for a customer,
you will see the number of payments group records
for that customer
in the navigation area for Access.
You will also see the list of dates for payments
in the yellow box to the right.
The group shows 8 payments
on 5 orders
with 3 different payment methods.
The combos to the right allow the user
to find existing records
by customer,
payment,
product serial number,
order number,
account or check number,
and date
When the filter is set to another customer,
the combo boxes to find records
are also filtered.
Clicking on ALL shows all the records again,
and also removes the filter from the find combos.
Suppose someone comes in to make a payment.
Filter for the customer.
There are 5 orders.
You can look through the payments
one group at a time
by clicking the navigation buttons built into Access
in the lower left.
As you move, subforms and other information
on the screen is updated.
Click the New button
to make a new record
Because the filter is set for the customer,
the customer is automatically filled.
The date is automatically set to the current date,
and you are ready to enter payment details.
If the customer has a set amount to spend,
and wants to allocate payments,
you can use the calculator area.
Enter 800 into Amount Allocate.
This customer has 5 open orders that are
consecutively numbered.
The first Order is 2142.
It is quick to enter the order numbers this way,
when you can.
The balances for each order are calculated and displayed.
As you enter the payment amounts,
the calculator automatically updates,
making it easy to see how much is left to allocate.
If you want to pay something off,
copy the order balance
and paste it into the payment amount.
Now there is a negative amount left to allocate.
Payments on other orders will have to be reduced.
As amounts are changed,
the calculator updates,
making it easy to adjust how much gets paid on each order.
To disburse all that is left to a single payment, double-click
All these payments are coming from one check.
In the Access settings,
I have my Enter key behaviour
set to go to the next record
This means pressing Enter goes down,
just like in Excel,
and control-ditto to copy the value from above,
also works in Excel.
If you want to make a payment on a specific product,
one way is to click on it in the customer purchase list,
which copies the serial number to the clipboard,
then paste into a new payment record.
The customer wants to close this order
and pay cash for the balance.
The order balance is copied
then pasted into the payment amount.
As payments are made,
the date edited is changed for the mainform, also.
To update the mainform,
click on the pencil in the record selector area.
When the record is saved,
the balances in the customer purchase listbox
are updated.
To write a note, click to set the focus since it is not a tab stop.
The status bar text gives you a friendly reminder to
press "SHIFT-F2 to ZOOM"
since the text may be longer than the display area.
In the Zoom box, you can set the Font.
I like to make it bigger.
The payment detail subform
and the list box to show and find payment dates
are anchored to the bottom of the form.
They stretch and shrink
as the form gets taller and shorter.
Future lessons will address problems,
enhance capabilities,
and explain more about the data structure
and how the mainform and subforms work.
Thanks for joining me.
Through sharing,
we will all get better.