Project 8 - Lab Discussion
Above the project description document are two MS Access database files. The Biblio2000.mdb file is supported by MS Access versions 2000, XP, 2003, and 2007. If you have MS Access 2007 you should use the Biblio2007.accdb.
This part of the project focuses on building a form, writing three different types of queries, and generating a simple report. This lab discussion document will be using a different database, Northwind.accdb, but you should be able to use the same techniques for the Biblio.accdb database. You will find a raw copy of this database, rawNorthwind.accdb, and a completed project copy of the database, jrnNorthwind.accdb, posted with this Lab Discussion document.
First, I need to make a copy of the database so I can manipulate the data and objects contained in it. Download the rawNorthwind.accdb database to your practice folder, right-click on it and copy it to the Clipboard, and then paste it into the same directory. Change the name from Copy of rawNorthwind.accdb to iniNorthwind.accdb where ini should be replaced by your initials.
Double-click the iniNorthwind.accdb file to open it. You will need to allow macros to run so when the system comes up, you will see a line with a Security Notice and at the end an Enable Content button.Click the Enable Content button. When the Splash screen pops up, just click the OK button to remove the Splash Window and then click the X on the right side of the caption bar in the Main Switchboard window to close this window. Now you have a navigation pane showing on the left side of the screen.If the top of the navigation panel does not show ‘Tables’ then click the little downarrow in the circle and choose Tables. This database has a number of tables.
Since we are interested in building a form based on the Customers table, click the Customers table to select it, Next click the Create menu item in the Ribbon. If you are using Office 2007, then choose the More Forms button downarrow in the Forms section and select the Form Wizard. If you are using Office 2010, then choose the Form Wizard. The first thing we need to do is to select the underlying table or query that will supply the fields and data items to be used on this form. The Customers table should already be the one in the Tables/Queries dropdown control. If it is not, use the dropdown arrow to find the Table: Customers and then click this item. Tables appear before Queries in the Tables/Queries dropdown box so you may need to scroll up to find the Tables: Customers. The fields listed under the Available Fields list box now show all the fields in the Customers table. We can now move the fields into the Selected Fields list box that we want on the form. Many times it is easier to move all the fields using the > button, and then just move the few unneeded fields back using the < button. In this exercise, I moved all the fields over and then moved back the ContactTitle, Region, and Fax fields.
Press the Next button to move to the next choice supplied by the Wizard. We will use the Columnar layout since this is most compatible with displaying one record at a time. Click the Next button to move to the next Wizard dialog window. MS supplies a number of backgrounds for the form; just pick one that looks good to you. Click next to proceed to the dialog window where you can give the form a more meaningful name than the Table or Query name. In my case I named it CustInfo. Before clicking Finish, I suggest that you click the radio button for Modify the form's design. Now click the Finish button.
The form generated by the system looks like the above. We need to make some room on the form for adding an image and placing a rectangle around the address fields. Besides that we want to be able to modify the Form Header and add Form Footer content. To expand the height of the form, move the cursor to the top edge of the Form Footer gray separator. When the cursor becomes a horizontal line with an up arrow and a down arrow, pull the cursor down to open up some space low in the form. Go to the right edge of the form, and when the cursor becomes a vertical line with a left arrow and a right arrow, drag the cursor to the right to increase the width of the form.
To expose some space on the Form Footer, position the cursor at the bottom of the Form Footer and drag down once you get the horizontal bar and up/down arrow cursor. Your form should look similar to that below.
The Address text box width is too long. Click in the white Address text box to select it. Move your cursor to the right edge, position it around the middle, and when the cursor becomes the vertical bar with a left and right arrow, drag the right edge of the Address text box to be about the same size as the Company Name text box.
You can control where you want the fields positioned on the form. If you have Office 2007, MS chose to group all the fields on a form but if you have Office 2010, MS did not group the fields. To break this grouping in Office 2007, click in one of the text boxes. You will now see a little plus sign in a rectangle just to the left and a little above the group of labels and text boxes. Click on this plus sign to select all of the grouped items, select the Form Design Tools / Arrange tab, and click on the Remove option under the Control Layout section. Once the grouping has been removed, you can use the mouse to resize or move around. Now you can manipulate the individual labels and text boxes, moving them, and adjusting their sizes. Click on the text box and move both the label and text box for that field. Click on just the little square in the upper left corner to move just the label or just the text box. Click on one of the sizing handles to adjust the size of the label or text box.We will start by selecting the Phone label and text box by positioning the cursor to the left of the label for the Phone text box and dragging it so the selection box includes the label and the text box for the Phone field. Now both the label and text box are selected, we can move them down by positioning the cursor in the selected area and when the cursor becomes the 4-way arrow, click it and drag the selection near the bottom of the Detail section of the form. Next we need to move the address fields, address, city, postal code, and country, down and to the right so we have room to draw a box around these fields. Position the cursor in the vertical ruler across from the upper most field, the address field.. Holding the left button, drag the cursor down inside the ruler area to contain the desired fields and release the button. When you move the cursor into one of the fields’ areas, the cursor turns into a four-way arrow. Clicking this four-way arrow cursor and holding down, you can move all the fields and in this case, move them all down and a little to the right. Click outside any of the fields to deselect all the fields. The form should now look similar to the form below.
I request that you use four other controls in designing your form. These controls will come from the Design menu item and the Controls section. First we will use the Image control from the Controls section. It is usually found in the second line of controls so you will need to click the down arrow or the More arrow just below the down arrow. It is the icon with the mountain scene, Click on this tool icon, and then draw a rectangle inside your form on the right side of the detail area. Next, you need to insert either some clipart or a picture. MS Access does not provide an icon for a direct connection to Office ClipArt so if you want to insert a clipart image, you will need to position the directory in the Insert Picture dialog box to where Microsoft Office normally stores its clipart - C:\Program Files (x86)\Microsoft Office\MEDIA\CAGCAT10. Unfortunately, MS has stopped showing thumbnails for this folder so you will have to figure out which file you want to select a different way. I find it is easiest to find a image, either from some clipart source or from a image source such as Google Images, and then place it in the Pictures folder. Now when the image box is drawn on the form, you can right click in the image box, select properties, and then the Picture property and clicking the … button, select the Pictures folder and choose your file. Once the picture is in the image box control, you may need to resize the picture since the default size is clip size. Some clipart sizes are too large, others may be too small. Right click inside the image box and select the Properties pop-up menu item. You should now see the Properties window. Make sure that the All tab is selected and then position the cursor in the Size Mode text box and click. Use the down arrow at the right edge to display the Size Mode options you have. I request that you use either Stretch or Zoom. Try out the different options while you change the size of the image box in different dimensions.
I also requested that you place a rectangle control around the address fields. Select the Rectangle control, , found under the Controls section, and draw a rectangle around the address fields. The Rectangle control will still be the control with focus or the selected control. Select the Format tab found under the Form Design Tools on the Ribbon, use the Pail of Paint toolbar icon found under the Font section to select a color for the background of the rectangle. Since the rectangle is sitting on top of the text boxes for the address fields, you will need to use the Arrange tab and in the Sizing and Ordering section choose the Send to Back item to move the rectangle behind the text boxes.
Depending upon the color you chose for the rectangle’s background and the font color of the enclosed label fields, you may need to change the labels’ font color. In this sample, I am changing the font color to white. Click outside the rectangle in an empty part of the form to deselect it, and then click on the first label control in the enclosing rectangle. Press the control key and click on the other three label controls ending up with your form design looking like
. Using the Format tab under the Form Design Tools Ribbon entry, choose the icon with the letter A under the Font section, , and choose a color. I chose white.
We also need to change some information in the Header section and add information to the Footer section. Change the text in the Form Header section to Maintain Customer Information.To change the label control in the Form Header section, select the top of the Detail gray area and when the cursor becomes the horizontal bar and the up and down arrow, drag the header section size down to expose the bottom of the label control. Click on the label control, choose the middle bottom edge sizing handle, and reduce the height. I also changed the Text Box's Back Color property and the Font Color property.Drag this label control into the center of the form’s width.
Now we want to add some information in the Form footer section. We will add your name in a label control and the =Now() function in the text box control. There are two controls that look similar: the Label control and the Text Box control . A Label control is used for static information; that is, information that does not change as we move from one row to another. A Text Box control is usually bound to one of the fields coming from the data source, usually a table or query. The Text Box control can also be bound to a formula or expression. Click on the Text Box control in the Control Section and then draw a rectangle on the right side of the Form Footer area. A label control is automatically associated with a Text Box control.
Click or double click inside the Text Box with Unbound showing so the cursor is at the left side of this text box. Type in =Now().
Click in the label control associated with this text box. Move the cursor over the big sizing rectangle on the label and drag the label control closer to the left edge. Click inside the label and change the text to show your name.
The =Now() function will use the system clock to retrieve the date and time and then display it in the text box. This date and time will be refreshed each time the form moves to a different record in the data source.
We are now done with designing the form and can run it. Use the View icon, in the Views Section to change to the Form view. This view displays the data from the database but does not permit you to modify the design. Below is a picture of the above form running in Form View.
You should notice that it has a Navigation control that allows the user to move to the beginning of the data set, previous record, next record, end of the data set, and new record as displayed from left to right by the 5 little arrow buttons.
That completes the Form part of Project 8. Now we need to move on to the Query part.
The second part of Project 8 covers writing queries to retrieve data from the database. Our book describes queries as asking questions of the database. Queries are used to manipulate the data in the database, most often for retrieving selected data from tables, but they can also be used for updating, deleting, or inserting records in the database. Sometimes we will need to join two or more tables to provide all the underlying fields needed for the query. Joins are done using a common field or fields between two tables. Access provide the Query by Example or Query by Form view to build the database query for us. This is an intuitive approach to selecting the tables that are needed, selecting the fields that we want to appear in the query results or needed to specify a query criterion, setting the criteria in the different fields, ordering the data through specifying some sort sequence, and sometimes allowing us to specify grouping fields and functions. We will use the Create menu with the Query Design icon for building our queries. This will bring up the following window.
The first query is a simple query using a single table with a criterion specified to restrict the number of rows being returned. For this example, we will use the Customers table, so click the Customers table, and then click Add. Since we are done adding tables, just click Close. If you accidently include too many tables, after you click the Close button, just select one of the fields in the extra table window and press delete. This action will remove the table from the table area. If you need to add an additional table, right click in an empty spot in the table area and click the Show Table... button in the pop-up menu. Now that we have the tables we are interested in for this quey, we can start choosing the fields that we want to show in the query output. The fields will be shown in the order that we drop them into the grid and not in the order that they are in the table. There are three different ways to place a field into a grid column. You can drag the field from the table to a column in the grid. You can double click a field in the table window which will place that field in the next available column. You can go to the Field cell in the desired column and use the down arrow to see the list of possible fields that can be selected.