Lab 9: Reports and Forms
FIT 100, Spring 2005

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

• Create a report for your database
  
• Create simple forms for artist names and musical styles.
• Create a more complex form.
• Add CDs to your database.

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.  In the new directory, rename your lab8.mdb to lab9.mdb.  You'll also continue making notes in your notes.txt file.  Put here any reminders to yourself about tasks you'll do in this lab, in case anything wasn't clear, or didn't seem to match the instructions - you may need these later for the mini-project 3.  (If you find any questions asked in this lab, go ahead and answer those, too. ;-)
   
   
2. Copy your lab9.mdb to your local computer and open it with MS Access (ignore any security warnings).
   
   
3. Select Reports on the left side of the database window and double click on Create report by using wizard. 
   
   
   
   
   
   
   The wizard will ask questions about what you want in the report, then create it for you:
   
   • Select the qCDs query and select all the fields.  Do this by clicking on the field name in the Avaliable Fields window, and clicking > to move it to Selected Fields.  When all are selected, hit Next.
     
     
     
     
   • We'll view our data by tCDs.  You can experiment with different options later, if you like.  (If you click on the other options, you'll see a preview of what they would look like.)
     
   • Don't add any grouping levels; just go to Next.
   • Sort by ArtistName in Ascending order.
   • Do a tabular layout and portrait orientation.  Feel free to experiment with different options later, if you like.  Make sure the checkbox to adjust field widths so all fields are on a page is checked.
   • Select whatever report style you would like.
   • Title your report CDs Report, and select the option to Preview the report.
     
     
4. 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

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

• You can create a form either using the design view, or with the help of a Form Wizard.  You can modify the design in the design view (and also to open a form to view or enter data).  It may be a good idea to first create the form with a wizard, and then adjust it in the design view.
  
  
• In the design view, you can right click on an item (or on the background) and select Properties... to view and edit the properties of that item.  Or, if you click on the background, you can edit the properties of the entire form.  In the properties window that pops up, you should see information about the item you right clicked on.  If not, you can change the item that is shown by using the drop down list at the top of the property window.
  
  
• A form is normally based on a single table, or on a query.  The table or query the form is based on is called the form's Record Source.  You can display the data in any field from the Record Source in the form.  Or the data from the Record Source can be used to look up data in other tables.  This is often used for drop down menus and selection lists in the form.
  
  
• Each item in a form has many properties.  Some of the more important ones are listed below.  (We haven't described the ones that won't be used in this lab.)
  
  • the Record Source (described above)
    
  • the Control Source (usually a field from the Record Source)
  • the Row Source (usually a table or query other than the Record Source)
  • the Visible indication (usually set to Yes, but can be No for primary and foreign keys that normally make no sense to show)
  • the Tab Stop and Tab Index (whose settings decide if and when the item should be skipped or stopped at when a person press the Tab key).
  • In addition there are properties that control the style of the item, such as its position, size and color.
    
    
• 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 may take less time than to fix a messed up form!)
  
  
• We'll describe how to use a form below, after you've made your first form.
  

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:
   • Select the tArtists table.  This is the Record Source of your form.  Select both the ArtistId and the ArtistName fields.
     
   
   
   • Pick the Columnar layout. This works for most forms.
   • You can pick the standard form style, or whatever style you like.
     
   • Give your form the title fArtists for now. (The f is for form.)
   • Have the wizard open the form to view or enter information when it is done so you can see how the form looks. The form can display one artist at a time.
     
     
   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.
   • Open your form in Design Mode (right-click on title bar of your form, select Design Mode).
   • Resize the form to make sure it has room for two buttons.  Do this by positioning the mouse just above the Form Footer bar (the usual drag arrows should appear), and dragging the Form Footer downward.
     
   • When we add the button, we would like to let the button wizard help.  Look at the Toolbox.  (If there is no Toolbox visible - it's a small window with icons on it - then select View -> Toolbox from the main Access menus.)  Just under its title bar is a wand icon.  If this is not highlighted, click it - this tells the Toolbox that we want to use the wizard.  (It should be on by default, but just make sure.)  We won't mention this in later steps, but if you find that the wizard doesn't start when you add a control from the Toolbox, check the wand icon.
     
   • Add a command button by selecting the button icon in the Toolbox, and then clicking in the form where you want the Undo button.  (The Toolbox is a floating window, and you can move it if it's in the way.)
     
     
   
   
   
   
   • This will cause a button wizard to pop up.
     • Pick the category Record Operations and the action Undo Record.
     • Pick an image that you like to represent the undo action.
     • Give your button a descriptive name, such as Undo.
     • Adjust the position and size of the button until it looks OK.
       
       
   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 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 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:

   • The combo box should display the names in the artist table - we will need to get those from the artists table.  So, select the option for looking up the values in a table or query.
   • Select the tArtists table.
     
   • You'll need both the primary key ArtistId and the ArtistName later, in order to get the name that goes with the id shown in the form field to the left of the combo box, so add both fields from your artist table.
   • Adjust the artist name column to a reasonable width.
   • We don't want the primary key column (ArtistId) to be visible (because it's right beside the combo box) so hide it (this is the default, so it may already be hidden).
   • Let Access "remember the value for later use".
     
   • Name the combo box ArtistNames.
     
   • (Use the settings recommended by the wizard for anything else.)
   
   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:
   
   
   • In the Other tab, Name should be ArtistNames.
     
   • In the Data tab, Row Source Type should be Table/Query. 
   • For Row Source, click on the ellipses button (the "...") to the side of the entry box.  It will pop open the query window.  Create a query (you know how to do this because you did Lab 8) that shows ArtistId and ArtistName from the table tArtists.  Close the query.  If it asks you whether you want to change the query statement, say Yes.
     
   • Make sure Limit to List is set to Yes.
   • Go to the Format tab and set Caption to "Artist Name" (without the quotes).
     
   • Set Text Align to Left. (Obviously, if you want to align your text to the right, or center it, or whatever, feel free to do so).
     
   • Also in the Format tab, make sure just the artist name is displayed by setting Column Count to 2 and setting your Column Widths to 0";1"  (This is if your query displayed ArtistId first and then ArtistName.  What you're doing is hiding the ArtistId by setting the width of its column (the first column) to 0 inches, and the width of the second column to 1 inch.  If the ArtistName came up first and the ArtistId second, reverse the 0 and 1.)
     
     
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. 
   
   • Select the action Open Form in the category Form Operations. 
   • Select the appropriate form (fArtists). 
   • The default settings in the wizard should be fine, but put text on the button saying "Artists..." (or whatever name you think appropriate).
   • Name the button OpenArtistForm.
     
   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.
   
   • Make sure you are in the All tab.
   • Set the Name to OpenArtistForm.
   • Set your Caption to something descriptive for your button.
   • Finally, we need it to do something when it clicks.  Go to the On Click property, and use the macro builder.  In the first row of the Action column, type in OpenForm.  Then at the bottom of the window where it says Form Name type in fArtists.
     
   
   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.

• Add at least 5 more of your own CDs.
  
• Some of the CDs you add should be for the same artist.
• Choose some "artists" that are actually groups, and some that are individuals - you'll need those for mini-project 3.
  
• Add CDs from several different musical styles, e.g., Classical, Latin, etc.  But make sure you have more CDs than styles of music!

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.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!

Check List