Subform to show Calculations in Microsoft Access

Sign in to queue

Description

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data.

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.

And finally, see the magic of Access happen ~
_________________________________________________________

Download the database example:

http://www.msAccessGurus.com/MainformSubformsPayments.htm

Video for mainform overview:

Mainform + Subforms
https://channel9.msdn.com/Blogs/MVP-Office-Dev/Mainform--Subforms-for-data-from-mutiple-tables-with-a-Microsoft-Access-User-Interface

~~~
Please Like, Comment, and Share with your friends ~

have an awesome day,
crystal

Embed

Download

Download this episode

Download captions

The Discussion

  • User profile image
    David Nealey

    Crystal, this is another excellent video. I learn something very useful from each of your videos. What I like most about them is that you move through the presentation at just the right speed. I am able to see how to use Access efficiently, relate the information back to something that I have struggled with, and then I realize the value of your knowledge and experience. And I don't get lost.

    If it is acceptable, I want to add links in my application to your videos so that my users can make the best use of my applications. This will save me time when I implement my solutions. Thanks again. David

  • User profile image
    strive4peace

    @David Nealey: you're welcome and thank you, David. I am happy for you to link to my videos

    have an awesome day,
    crystal

Add Your 2 Cents