Lecture Notes: January 2024 Series - Paper B, Second Part

Lecture Overview

• Focus: Second form in Paper B
• Objective: Use queries to create information for fields, display gardeners and their reviews
• Form Creation Requirements:
  • Input form to add or view a garden review
  • No validation for fields
  • No automated routines to save data
  • Combo box for gardener selection
  • Automatic information display on selection
  • Display specific data set
  • Combo box to add a new grade review
  • Message about average monthly meeting applicability

Form Creation Process

1. Create a Blank Form
   • Use design view to add necessary controls.
2. Add Controls
   • Dropdown Box:
     • Select values from tables or queries.
     • Use property sheet to edit and set control source.
     • Adjust label using property sheet.
   • Text Boxes:
     • Show most recent review, number of reviews, and best review.
     • Handle overlapping by adjusting labels.
   • Combo Box for New Review Grade:
     • Input review grades between one and three.
     • Label it using CBO prefix.

Query Creation

• Purpose: Pull information for the form when a gardener is selected.
• Fields:
  • Most recent review date
  • Number of reviews
  • Best review grade
• Query Configuration:
  • Group by gardener IDs
  • Use totals to find max, count, and min values
  • Criteria to link to the form's combo box

Form Implementation

• Bind Combo Box:
  • Use query to bind gardener ID and surname.
  • Adjust to display names instead of IDs.
• Data Display:
  • Use DLookup functions to display recent review, number of reviews, best grade.
  • Use system date for new review date.
  • Message Box: Use if-statement to display messages based on review grade.

Data Refresh Issue

• Problem: Data doesn't change on gardener selection.
• Solution: Create a macro for requery on gardener change event.

Final Adjustments

• Design Considerations:
  • Ensure clear labeling and presentation.
  • Remove unnecessary prefixes from visible labels.
  • Add appropriate heading to the form.

Key Takeaways

• Efficient use of queries and form controls to automate and display relevant data.
• Importance of design and user interface in form creation.
• Handling dynamic data updates through event-driven programming.