PHS badge

PHS CyberSchool

Computer Studies SG 

Modified 17 June 2004

School Supporter's Database - Creating an application system

The Supporter's Club is pleased with the tables, forms and reports you have created.  The club has some additional requests, however.  First, they would like to be able to type only the digits in the Last Order Date field.  They would also like to display the profit (selling price - cost) for an item on the Item Update Form.  Finally, they would like an easy way to access the various tables, forms and reports by simply clicking a button or two.  This would make the database much easier to maintain and update.

Open the database file School Stock.you created in School Supporter's Database - Basic, and worked on in School Supporter's Database - Queries and School Supporter's Database - Maintaining the database.  Perform the following tasks:

  1. Open the Supplier table in design view and create an input mask for the Last Order Date field.  Use the Short Date input mask.  Save the change.
  2. Modify the Item Update Form to create the form below.  The form includes a calculated control to display the profit (Selling Price-Cost).  Format the control as currency.  Save and print just the details from the form for the Cricket hat.

Item Update Form

  1. Open the On Hand Value report.   Add a control to the report to show the Profit.  Make the necessary adjustments to the width and formatting of the page. 

On Hand Value Report

  1. Create macros that will perform the following tasks:
    1. Open the Item form
    2. Open Update Item form.
    3. Open the Supplier Master form.
    4. Open the Item table.
    5. Open the Supplier table.

    Select action in the macro  Further actions for the macro

    1. Preview the On Hand Value report. (Be careful it is Print preview and not Print)
    2. Preview the Profit report. (Be careful it is Print preview and not Print)
    3. Preview the Supplier report. (Be careful it is Print preview and not Print)
    4. Print the On Hand Value report. (Not at PHS as it will print the report and waste paper when it is tested.)
    5. Print the Profit report. (Not at PHS as it will print the report and waste paper when it is tested.)
    6. Print the Supplier report. (Not at PHS as it will print the report and waste paper when it is tested.)
  2. Create your own switchboard illustrated below so that you can access the tables, reports and forms quickly.
    1. In Design view create a blank form.  Save it as Switchboard.  In the Properties view of the form, in Format,  make sure that the Navigation Buttons and Record Selectors are No, and Scroll bars are Neither.    
    2. Put labels on the form for Open Forms, Open Tables, Preview Reports and Print Reports.  Centre the name Supporter's Club, large and clear.  
    3. Make sure the Wizard is switched off.  

    .Make sure wizard is not selected

    1. Under the labels add command buttons as illustrated below.
    2. To each command button, in Properties view / Event / On Click link to the required macro. 

     Linking command button to macro

     

    1. In Properties / Format / Caption change the caption of the command button.

    Supporter's Club switchboard

     

    1. Test and explore the switchboard.
  3. The database can open automatically on the switchboard.  In Tools / Startup under Display Form/page select the name of your Switchboard form.

    Database opens at switchboard 

     

  4. Personalise your database with a name and icon.  Use the tip sheet for guidance.

Personalised database with own title and icon

  1. Save the database for future tasks.

(based on Access 2000 by Shelly, G.B., Cashman, T.J. and Pratt, P.J. published by Course Technology in 2000 www.scsite.com and Databases with Microsoft Access by Sandra Jacobs, published by Study Opportunities in 2002)

Return to Computer Studies lesson plan index. This work may be freely copied for non-commercial use only. Copyright © Pam Miller.