Lab 11: Forms (aka Project 3)
FIT 100, Spring 2006

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 10 before you start with this lab. You must read Chapter 15 ("A Table with a View") before starting this lab.

Objectives

For this lab, you will use your CD database from Lab 10. 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 Simple Forms for Artist Names and Musical Styles

Besides getting information out of our database in a nice format, we also want to be able to put it in conveniently, and without having to use generic forms that aren't tailored to our data.  We can do this by making forms that have appropriate headings for input fields, and where the fields are arranged on the page in some sensible manner.

Here, we'll make forms for adding, changing, or removing artists and styles.

Here is some general information about forms to keep in mind as you follow the specific instructions below:

Now, let's make a form for artists.

  1. 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.  (It will differ depending on what information you have in your database.  Your record collection, for instance, may be less lame than the one shown here.)


  2. 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?  We'll solve this by adding two buttons: one to undo changes and one for removing an artist.


    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.

    Now that you have a form, how do you use it?

    To open your form, select Forms in your database window, and either double-click your form name, or use the right mouse button to get a menu, and select Open.

    Note the navigation bar at the bottom.  It has the same sort of forward and back arrows as on most media players or page viewers (e.g. Adobe Acrobat) - there are arrows that go to the first and last entry, or go back by one or forward by one.

    You'll notice there's one extra button on the right, with a star on it.  That New Record button will make a new entry in the table.  When you click it, you'll get a form with blank fields, which you can fill in.  (When the form first opens, it will show a blank form, ready for you to make a new entry.)  One thing that's a bit odd is, you don't need to do anything special to save the entry.  There is no "save" button.  To add more new entries, just click the New Record again.  When you're done, you can just close the form with its X button, or you can navigate to look at your entries.

    For text fields, click the mouse to put the cursor in the input box, then type the value you want.  There are other types of input "widgets" - they're all fairly standard (menus and suchlike), and it should be not too hard to figure out how to use them (or you can read about them in Access's help info!)   (We'll use a type of input called a "combo box" below.)

    Access won't do anything til you start filling in some field.  When you do, it will assign an ID number for the AutoNumber field.  (If the AutoNumber field is displayed in the form, as it is for the form you just made, you'll see this happen.)  As soon as it assigns the number, it has created the entry, even though the other fields are blank.  If you decide you don't want the new entry after all, you can use your Undo button.  (Delete will also get rid of the record, but Access will make you respond to some popups.)


  3. Test out your new form by changing an artist name followed by clicking on the undo button. Then create a new artist, and try removing it with the delete button.  (Don't worry if you get a "No current record" message after you remove.  Access tries to display the next record after the one you removed, so if you removed the last record, it won't have anything to display, and will warn you that it's not showing you a record.)

  4. Hmm...  What happens if you try to remove an artist whose ID is used in the CD table?  Try it out...  (Remember how we turned on "Enforce referential integrity" when we created the CD table?)

  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

Next, we'll create a more complex form for manipulating CDs.  It's more complex because the information will belong to three tables instead of only one.  This form will let you create a new row in your CD table, with some extra conveniences:  For the artist and style fields in the CD table, it will let you select an existing artist or style by name, and will fill in the matching ID.  If the row needs new artists or styles, it will let you add those first.

  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 (i.e. continue clicking through the wizard until finished, but don't bother to change of the wizard's choices).  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 their names.  A solution to this problem is to show the names from the artist and style tables, so that we can select those.  We'll add a component called "Combo Box" that shows 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.  We'll add one for the artists and one for the musical styles.

    Place a combo box (from the Toolbox) beside the artist identifier box - you may need to resize the form.  (If you put the mouse pointer over an icon on the Toolbox for a short while, it should show you a "tooltip" with the name of that tool - use that to find the combo box tool.)  A wizard will guide you through some important settings for the combo box.  Select appropiate answers as follows:


    If a wizard did not pop up, never fear, we can still set the combo box's properties by right-clicking on it and selecting Properties.  Do this if you didn't use a wizard to set up the combo box:

  3. 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.  We'll fix that next.

  4. Open your form in design view. Open the properties of the newly added combo box (right click on the part that shows the artist names, not the ArtistNames label) and change the Control Source property to be ArtistId.

  5. Close the design view and open the form 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.

  6. 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:

  7. 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. 
    As before, if your wizard didn't pop up, you can adjust 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 something like this (layout may not be the same):



  8. Now you can follow those instructions to create a button to open your Style form.

  9. 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. 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.  Use your fCDs form to add the CDs - remember to click the * button to get a new row in the tCDs table.  Use the buttons for the artist and style forms to add new artists or styles if they're not already in the database.

Before you close your database or Access, select Tools>Database Utilities>Compact and Repair Database... and accept to open project3.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 project3.mdb to your project3 directory on dante (replace the old file).  Do not forget to save your notes.txt with all your comments.  It should, at a very minimum, contain reminders for yourself about how you created your queries and forms, as usual, in your own words.

Reminder:  Always 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!