Subform to show Calculations in Microsoft Access
Posted on
7,371 Points
10:50
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
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 show relevant calculations for 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 ~
Please Like, Comment, and Share with your friends ~
have an awesome day,
crystal
A Microsoft Access subform is used to show relevant calculations for 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 ~
Please Like, Comment, and Share with your friends ~
have an awesome day,
crystal
Video Steps
1. download the database ACCDB example:
Subform-Display-Calculations.zip
2. envision the statistics you want to show on a subform
3. Line up the data using one or more queries
4. make a form based on the query just created
- the RecordSource can later be changed to an SQL statement if desired
5. add this form to another form as a subform
6. set the Name property of the subform control to match the name in the Source Object property (as a general rule)
7. Link the mainform and subform by setting the LinkMasterFields and LinkChildFields properties for the subform control
- make sure what is needed is actually ON each respective form (Visible can be No).
- data in the subform will automatically change to reflect what it is linked to!
8. Set and consider other properties
9. if you want an overview of the mainform that this subform is used on, look at the this video:
Mainform + Subforms
https://www.experts-exchange.com/videos/9818/Mainform-Subforms-designed-for-efficiency-and-flexibility.html
0 Comments
Suggested Videos
Over the past few months, many folks seem to be having a variety of Outlook problems. This article contains troubleshooting and repair steps that will often solve the various issues people have been encountering. Step-by-Step instructions and illust…
- Microsoft
- Windows 10
- Microsoft Office
- Microsoft 365
- Outlook
- Office Productivity, Windows OS, Troubleshooting
I am not a database expert nor am I conversant with database design, but I have had a couple of run-ins with databases that are either badly designed or use a platform that doesn’t meet the needs of the end-user. I would like to think this is a "le…
Suggested Courses
Next Video:Windows Insider Preview 19613