Lab 9: Reports and Forms
FIT 100, Autumn 2004

Key Words: queries, forms, form properties, fields, identifier, primary key, foreign key, Record Source, Control Source, Row Source, form wizard, navigation bar, button wizard, action, combo boxes
 
See Wikipedia (http://www.wikipedia.org/) for a detailed description of the key words.

Preparation

You must have completed Lab 8 before you start with this lab. You must read Chapter 14 ("A Table with a View") before starting this lab.

Objectives

For this lab, you will use your CD database from Lab 8. You will write more queries and build a graphical user interface to make it easier to view and enter new data into your database.

Create a Report for your Database

  1. Create a lab9 directory on dante. Copy everything from your lab8 directory into your lab9 directory. (You do not need to copy the checksum file.) In the new directory, rename your lab8.mdb to lab9.mdb.
  2. Copy your lab9.mdb to your local computer and open it with MS Access (ignore any security warnings).
  3. We've got some information in our database, but we'd like a nicer way to view it all, instead of just in the database tables.  To do so we will create a report. 
  4. Select Reports on the left side of the database window and double click on Create report by using wizard.  The wizard will basically show you how to create a table:

  5. Voila! Admire your pretty report.  If you feel like adjusting your report later, right-click on it in the database window, and select Design View and change whatever you'd like (for instance, you might want to change the ArtistName label to Artists and the CDName label to Album Titles.

Create Simple Forms for Artist Names and Musical Styles

  1. In this section you will create two forms that simplify the manipulation of your artists and musical styles. When you deal with forms, you should keep the following in mind:

    Feel free to experiment and change the properties of your form and the items in the form. If the form becomes messy, you can easily start over with a new form. To start over normally takes less time than to fix a messed up form!

  2. Select Forms on the left side of the database window and double click on Create form using wizard. The form wizard will guide you through the process of creating a form:
    The tArtists table should be selected and the ArtistId and ArtistName fields chosen.
    Your form should look similar to the following (your data may vary depending on what information you have in your database.  Your record collection, for instance, may be less lame than Sandra's and not have any Britney Spears in it):

    Note the navigation bar at the bottom. With this you can go back and forth between your artists. The New Record button can be used to enter a new artist into the table.
  3. With the form, it is easy to add a new artist and to change existing artists. But what if you accidentally changed an artists name or really want to remove an artist? Solve this by adding two buttons: one to undo changes and one for removing an artist.

    Open your form in Design Mode (right-click on title bar of your form, select Design Mode) and resize the form to make sure it has room for two buttons. Add a command button by selecting the button icon in the list on the left (in the main Access window).



    Then click somewhere in the form where you would like the Undo button to be placed. This will cause a button wizard to pop up.

    When you are done with the undo button, add another button to represent the Delete Record action. After you are done, save your work and close the design view.  Test out your new form by changing an artist name followed by clicking on the undo button. Then create a new artist and then removing it with the remove button.
  4. What happens if you try to remove an artist whose ID is used in the CD table? Try it out.
  5. You have now successfully created a form to simplify manipulation of artists! On your own, do the same thing for the musical styles. That is, create a form called fStyles in the same way you created your fArtists form. Make sure both forms behave similarily.  Below is an example of how fStyles could look like.

Create a More Complex Form: fCDs

You will now create a more complex form for manipulating the CDs. It's more complex because information will be collected from three tables instead of only one.

  1. Create a form using the form wizard. The Record Source should be the CD table and all its fields. Give your form the title fCDs. For everything else you can use the values suggested by the wizard. Also add undo and delete buttons! The result should have the following components (yours may be positioned differently):

  2. One problem with the current form is that we see the IDs of artists and musical styles instead of the names. A solution to this problem is to add a component called "Combo Box" for the artists and one for the musical styles. A combo box is a drop down list with values based on a query or table. The value shown in the combo box depends on the Control Source of the combo box.

    Place a combo box (from the Toolbox window/bar) beside the artist identifier box. A wizard will guide you through some important settings for the combo box. Select an appropiate answer based on the following:


    If your wizard did not pop up, never fear, we can still add a drop-box, and then appropriately set it by right-clicking on it and selecting Properties.

    Move the combo box to the position of your choice. Switch to form view and try out your form. You should see the combo box and be able to select an artist name, but there is currently no connection between the artist displayed in the combo box and the ArtistId in the form.
  3. Open your form in design view. View the properties of the newly added combo box and change the Control Source property to be ArtistId.
  4. Switch to form view so that you can enter data. The artist name in the combo box should now be linked with the ArtistId. To test if your link works or not, try changing the artist for one of your CDs. You should see the ArtistId change numbers when you select a different artist from the drop down list. Undo your changes by clicking on the undo button.
  5. On your own, add another combo box for the style of music. Verify that your form still functions properly. Below is an example of the form:

  6. Your form is taking form! It is quite easy to add CDs but there is one slight problem. You can not add artists or musical styles by using the combo boxes. The solution is to add a button beside each combo box that opens a form to manipulate the data for artists or musical styles. Add a button next to your ArtistName drop-box using the button wizard. The purpose of this button is to open your fArtists form. 
    If your wizard didn't pop up, you can create the button by setting its Properties (right-click as usual to get the Properties window).  This time, instead of clicking on separate tabs, we're just going to change everything in the All tab. You could have done that before too, with the combo boxes.  It just depends on how you like to do things.

    Your result should look like this:

  7. Now you can follow those instructions to create a button to open your Style form.
  8. Even though you can add new artists and musical styles very easy, there is still one problem. After adding a new artist or musical style and returning to your CD form, the combo box is not updated! This can be solved by adding a refresh button to your CD form. Add the button. The action should be Refresh Form Data in the category Form Operations

    If your button wizard didn't pop up, create its setting using the Properties menu, and set the On Click property using the macro builder.  Create two Requery actions, one for the artist names combox box, and one for the style names combo box.  Where it says Control Name for the query, set each Requery action to a name of the combo box you want to refresh (e.g. Combo_ArtistNames).

    Below is an example of the CD form that has the new buttons.

Add More CDs to Your Database

Now you can start to use your database! Add more CDs! If you do not know what CDs to add, search for music stores on the web and pick CDs from there.

Before you close your database or Access, select Tools>Database Utilities>Compact and Repair Database... and accept to open lab9.mdb. This will remove unneeded space in your database file, which otherwise can become very large!

Close all of your database windows and close Access. Copy your lab9.mdb to your lab9 directory on dante (replace the old file). Do not forget to save your notes.html with all your comments. It should, at a very minimum, contain the answers to questions asked in the lab and reminders for yourself about how you created your queries and forms, as usual, in your own words.

Select Tools>Database Utilities>Compact and Repair Database... before you close your database, after you have worked with the database for a while. This will decrease the size of your database file. Also close your database file and Access before copying your database file to your dante account, otherwise you may end up with an empty file!

Check List

 

I know how to create a report using the report wizard.

 

I know how to create a form using the form wizard.

 

I know how to modify a form using the form design view.