Two objects, two directions
A form is designed for the screen and for input: it presents one record at a time, laid out for a person who is typing. A report is designed for paper and for output: it presents many records at once, grouped, totalled and formatted to be read.
The distinction that matters in the exam: a form can change the data, a report cannot. A report is read-only by design, which is what makes it safe to hand to someone who should not be editing anything.
| Form | Report | |
|---|---|---|
| Designed for | the screen | printing |
| Shows | usually one record at a time | many records together |
| Can edit data? | yes | no — read only |
| Typical use | entering a new student | the end-of-term mark sheet |
| Grouping and totals | rarely | the main reason it exists |
Why enter data through a form rather than the table
Data could be typed straight into the datasheet, so a form must earn its place. It does, for four reasons the exam asks for.
A form can show only the fields a particular user should see, and hide the rest. It can lay them out in the order of the paper form being copied from, which halves the mistakes. It can pull related data from several tables into one screen, so the user never has to know that the data is normalised across four tables. And it can use controls — a drop-down list of existing classes rather than a typed class name — which makes an invalid entry impossible rather than merely detected.
- Text box — displays and edits one field.
- Label — fixed text, such as a heading or a field caption.
- Combo box / list box — choose from a list, usually drawn from another table.
- Check box — a yes/no field.
- Command button — runs an action: save, next record, open a report.
- Subform — a form inside a form, showing the "many" side of a relationship.
Why a combo box beats a validation rule
A validation rule catches a wrong class name after it has been typed. A combo box listing the classes that exist means the wrong name cannot be typed at all — and it stores the underlying key while showing the readable name. Preventing an error is always better than detecting one, and this is the clearest example of it in the syllabus.
Forms with subforms
A single form handles one record of one table. Most real data entry involves a one-to-many relationship: an order and its several order lines, a student and their several enrolments.
A subform solves this. The main form shows the "one" record — the order — and the subform embedded inside it shows the related "many" records in a datasheet. Access links them on the shared key, so the subform automatically shows only the lines for the order currently displayed, and a new line entered there is automatically given the right foreign key.
A shop needs a screen for taking an order: customer details at the top, and the items ordered below. Describe the design.
- Main form bound to the ORDER table, showing OrderNo, Date and the customer.One order is the "one" side, so it belongs on the main form.
- The customer chosen through a combo box listing existing customers.It stores CustomerID while showing the name, so the foreign key is always valid.
- A subform bound to ORDERLINE, linked on OrderNo.The many side goes in the subform, and the link means lines from other orders never appear.
- Within the subform, the product chosen by combo box and the quantity typed.Again the combo box makes an invalid product code impossible.
- A calculated control showing the line total, and another totalling the order.Calculated rather than stored, so it cannot disagree with the price and quantity it comes from.
Main form on ORDER with a combo box for the customer, and a subform on ORDERLINE linked by OrderNo.
Reports: grouping, sorting and totals
A report exists to make many records readable, and its power is in grouping. Grouping a mark sheet by class produces a heading for each class, its students listed beneath, and a subtotal or average at the end of each group before the next begins.
A report is built from named sections, and knowing which content goes in which section is the examinable part.
| Section | Printed | Put here |
|---|---|---|
| Report Header | once, at the very start | the title and the date produced |
| Page Header | at the top of every page | column headings |
| Group Header | once per group | the class name |
| Detail | once per record | the student's row |
| Group Footer | once per group | the class subtotal or average |
| Page Footer | at the bottom of every page | the page number |
| Report Footer | once, at the very end | the grand total |
The section decides how often it prints
A grand total placed in the Page Footer prints at the bottom of every page and is wrong on all but the last. Column headings placed in the Report Header appear once and are missing from page two onwards. When a report question asks where something belongs, it is really asking how many times it should appear — and the section name answers that directly.
Before you leave this chapter
- Forms are for input on screen; reports are for output on paper and cannot edit data.
- A form can hide fields, reorder them, combine tables and prevent invalid entry.
- A combo box prevents an invalid value instead of detecting it afterwards.
- A subform shows the "many" side of a relationship and sets the foreign key automatically.
- The report section determines how often its content prints: once, per page, per group or per record.