Be able to create a data entry form including specified fields, appropriate font styles and sizes, appropriate spacing between fields, character spacing of individual fields, use of white space, radio buttons, check boxes, drop down menus

18 Databases – Creating a Data‑Entry Form (IGCSE 0417)

Learning Objective

By the end of this lesson you will be able to design and build a data‑entry form that:

• Contains the required text, numeric and date fields.
• Uses appropriate font styles and sizes for headings and input areas.
• Applies consistent vertical and character spacing.
• Employs white space to improve readability.
• Includes radio buttons, check boxes and drop‑down menus where appropriate.
• Is linked to a relational database table (primary key, foreign key, field data‑types, validation).
• Can be used to add, edit, sort, search and calculate data, and to generate a formatted report.

1. Database Structure – What the Form Will Bind To

1.1. Core Table (Students)

1.2. Lookup Table (Classes)

1.3. Validation‑Rule Checklist (copy into your notes)

• Required – set to Yes for fields that must be filled (e.g. StudentName, DOB).
• Input Mask – controls format while typing (e.g. 999-999-9999 for Phone).
• Validation Rule – logical expression entered in the field’s property sheet:

  • Age: >=5 AND <=120
  • Gender: In ("M","F","O")
  • Postcode: Like "?????"

• Default Value – optional value automatically placed in a new record (e.g. 0 for a numeric total).

2. Form‑Design Requirements (Syllabus 18.1)

3. Mapping Form Controls to Database Fields

4. Step‑by‑Step Construction of the Form (Access / LibreOffice Base)

1. Create the tables first – define fields, data‑types, primary/foreign keys and validation rules (see Section 1).
2. Open the Form Designer and choose Form Design (or Create Form in Design View).
3. Insert a Section (or Tab Control) for each logical group (e.g. “Personal Details”, “Contact Details”).
4. For each field:

   • Add a Label and set its Caption (e.g. “Student Name:”).
   • Drag the appropriate control onto the form.
   • Set the control’s Control Source to the matching table field.
   • Adjust Font, Size, Top/Bottom Margin to meet the design tips.

5. Configure special controls:

   • Option Group – set Option Value for each radio button (M, F, O).
   • Check Boxes – give each a unique Name (e.g. chkMath) and use a small VBA/LibreOffice macro to concatenate selections into the Subjects field.
   • Combo Box – set Row Source to SELECT ClassID, ClassName FROM Classes; and Bound Column to 1.

6. Set the Tab Order (View → Tab Order) so the cursor moves logically from top‑left to bottom‑right.
7. Add a Command Button labelled “Save”. In Access set its On Click event to DoCmd.RunCommand acCmdSaveRecord; in Base use Tools → Form → Save Record.
8. Test the form:

   • Enter a few records.
   • Check that required‑field warnings appear.
   • Verify the Input Mask for Phone and the validation rule for Gender.
   • Confirm that the Class combo box displays the values from the Classes table.

5. Mini‑Exercise – Manipulating Data (Syllabus 18.2)

After the form is working, complete the following tasks to demonstrate calculations, sorting and searching.

1. Calculate Age – Add an unbound text box called txtAge with the Control Source:

   DateDiff("yyyy", [DOB], Date()) - IIf(Format([DOB], "mmdd") > Format(Date(), "mmdd"), 1, 0)

   This displays the student’s age automatically.

2. Create a Query to List All Students in a Selected Class:

   • Query design → add Students and Classes.
   • Join on Students.ClassID = Classes.ClassID.
   • Show fields: StudentName, DOB, ClassName.
   • Set Criteria under ClassName to [Enter class name:] – a parameter query.

3. Sort Records Alphabetically – In the form’s On Load event, add:

   DoCmd.ApplyFilter , "StudentName ASC"

   (Access) or set the form’s Order By property to StudentName.

4. Search for a Specific Student – Add a small unbound text box txtSearch and a button “Find”. Use the macro:

   DoCmd.FindRecord [txtSearch], acEntire, True, acSearchAll, acCurrent, True, True

   (Access) – this jumps to the first matching record.

6. Producing a Report (Syllabus 18.3)

1. Open the Report Wizard (or Report Design) and select the Students table.
2. Choose the fields you want to appear (e.g. StudentName, DOB, Gender, ClassName, Phone).
3. Set the layout to Columnar and select Portrait orientation.
4. In the wizard’s “Style” step, pick a predefined style (e.g. “Professional”). This ensures consistent fonts, headings and colours.
5. After the report is created:

   • Open the Report Header and type a title such as “Student Register – Year 2026”. Set the font to Bold, 16 pt, centred.
   • Open the Page Footer and insert a page number control ([Page]).
   • Adjust the vertical spacing between fields using the Layout View – leave a blank line between each record.

6. Preview the report, then Export → PDF or XPS for submission.

7. Cross‑Topic Tips (File Management, Images, Styles, Charts)

• Inserting an Image into a Form – Use the Image control, browse to a .jpg/.png file, and set its Size Mode to “Stretch”. Images can be used for logos or student photos.
• Applying a Style to Labels – In Access, select a label, open the Format tab and click Style Gallery. Choose a style (e.g., “Heading 2”) to keep colour and font consistent across the form.
• Exporting a Chart – Create a query that totals the number of students per class, then use the Chart Wizard to make a bar chart. Right‑click the chart and select Copy, then paste into a Word document or PowerPoint slide for the project report.
• Saving and Back‑up – Always use File → Save As to create a backup copy of the database file (.accdb or .odb). Store the backup on a separate drive or cloud storage.

8. Quick‑Reference Checklist (Exam Day)

1. Table(s) created with correct primary/foreign keys and validation rules.
2. Form opened in Design View; all required fields have a label.
3. Control Source of every control matches the table field.
4. Font: headings 14 pt bold, input fields 10 pt regular (or default).
5. Vertical spacing: one blank line (or increased top margin) between each label‑field pair.
6. Character spacing: monospaced font for fixed‑width data.
7. White space: separate logical groups with sections or tabs.
8. Correct control types: radio buttons (single), check boxes (multiple), combo box (drop‑down).
9. Tab order set logically.
10. Save button works; test by entering at least two records.
11. Mini‑exercise completed – age calculation, query, sort, search.
12. Report generated, header/footer added, exported to PDF.
13. Optional: image inserted, style applied, chart exported (cross‑topic credit).

Follow this guide step‑by‑step, and you will meet every part of the Cambridge IGCSE 0417 syllabus for creating a data‑entry form, manipulating the data, and presenting it in a professional report.