ENV* K105

Assignment #10--Continuing with Microsoft Access
Due 11/29/05 (Home)

In this assignment, you will add a calculated control and combo box to a form.

1. Copy the file trcc_cec05.mdb to your I: drive (click here if you need a reminder on how to copy an Access file from the Web).
2. Create a lookup field in a table as follows:
• Open the database "trcc_cec05.mdb". (If Access prompts you with a security warning, click "No" you don't want to block unsafe expressions, "Yes" you want to open the file, and finally if necessary, "Open.") This is essentially the database that you created last week with a new table, "PaymentMethod". This new table will serve as the source for a look-up field in the "Dues2005" table.
• Under the Tables tab in the database window, click on Dues2005 and click Design. Dues2005 will open in the design view.
• Click in the Field Name column in the first empty row, below MemberID. Type the name "PaymentMethod" (don't type the quotation marks).
• Tab to the Data Type column. From the Data Type drop down list choose Lookup Wizard.... The Lookup Wizard dialog box will open.
• Choose "I want the lookup column to look up the values in a table or query" and hit Next.
• Choose the Payment Method table. Hit Next
• Select PaymentMethod and hit the > button. Hit Next.
• Click Next again to skip through the next page and then label the lookup column "Payment Method". Click finish.
• Save the table when prompted.
• Switch the table to Datasheet view using the dropdown list on the View button, . There should be a new column labeled "Payment Method" though part of the label will be cut off because the column is too narrow. Resize the column by double-clicking on the right side of the cell containing the label (grey box in the first row that says "Payment Method").
• Choose a payment method for each record. Set records (ie, PaymentID values) 1, 4, 5, 10, and 11 to "cash." Set record 9 to "work" and the rest to "check." To enter a payment method you can choose from the drop down list or start typing one of the methods. Notice that as soon as you type "ca" the word "cash" appears. What happens if you try to enter a method that is not on the list?
• Close the table. Save the changes when prompted.
3. Next you will add and modify a form.
• Create a form, "MemberPayments", to view and record member data and dues info as follows:
• On the database window, select Forms under Objects. Double click on Create from by using wizard.
• In the Table/Queries drobdown, select the Members table.
• Hit the >> button to select all fields from the Members table. Then select Dues2005 from the drop-down list. Select Date Paid, Amount, and Payment Mehtod from the Dues2005 table using the > button. Hit Next.
• View the data by Members. Choose Form with subform(s).
• Choose Datasheet layout for the subform.
• Choose Standard style.
• Name the form MemberPayments. Click Finish.
• The form will open. What you have is a form with member information on the top and payment information on the bottom.
• You can fix the caption to include the space between "member" and "payments." Be sure the form is open in Design View (use the View button). If the properties dialog is not displayed, select View, Properties.... Then change Caption to "Member Payments" (with the space, but without the quotes) and hit enter. Switch to Form View.
• If necessary, change the width of the Date Paid column in the Dues2005 Subform so that you can see all of the label.
• It's easy to locate a particular record, by name, for example. Click on the Last_Name field. Click the Find button on the toolbar (it has the binoculars on it, ).
• In the Find What: box type "Flinstone" and choose Find First. Choose Close. Then, click Cancel.
• How much has Fred paid? By what method?
• In the first blank row in the Dues2005 subform add a payment from Fred. In the Date Paid column, hold down the "Ctrl" key and type a semicolon (";"-but without the quotes). Today's date should appear. Tab to the amount column. Type 45 ENTER. Select Check as the payment type.
• You have added a payment to the Dues2005 table. Close the form and open the Dues2005 table. The last row should be the one you just added. Close the table.
• An alternative way to find a record uses a form filter:
• Open MemberPayments form in Form View.
• Click on the Filter by Form button, .
• Click in the Last_Name box. Choose Benoit from the drop-down list. Hit the Filter button, .
• The record for my name will appear. Notice at the bottom of the Form window this record is shown as "1 of 1 (filtered)". To see all records, click the Filter button again.
4. It would be very handy if our new form displayed the total paid by each member. You will need to add a calculated control to the Dues2005 Subform. On the MemberPayments form you will need to add a control that displays the results of the calculation. Here's how:
• Select the Dues2005 Subform and hit the Design button in the database window.
• Drag the bottom of the Form Footer bar down to create a small strip of footer.
• Click the Text Box button,, on the toolbox and then click and drag a rectangle into the right side of the footer. The text box will say "Unbound" and its label will say "Text6". Note: If the toolbox is not displayed, click the button with the hammer and wrench on it, which should be in the top row.
• Right click on the text box and choose properties. In the Control Source box type a formula that will calculate the sum of all the payment amounts on the form. The formula is "= Sum ([Amount])" (without the quotes). Close the properties box.
• Switch to Form View. The text box should display the number 380 since that is the sum of all payment amounts.
• Switch back to Design View. Right click on the new text box and choose properties. Set the Format to Currency. Set the Name to TotalPayments.
• Click on the label that says Text6 at the moment.
• Set the Caption to "Total Payments:" (without the quotes).
• Close the properties box, close the Dues2005 Subform. Save changes when prompted.
• Open the MemberPayments form in Design View. Insert a text box in the space under the Zip field. Change the caption of the label to "Total Payments:". Move and size the label and the text box so that they look nice and neat.
• In the Properties for the new text box, click in Control Source. Click on the ... button to get the Expression Builder.
• Double click the + sign next to Forms in the left column. Double click the + sign next to Loaded Forms. Double click the + sign next to MemberPayments.
• Click on Dues2005 in the left column. Then double click on TotalPayments in the middle column.
• The expression should now read: "=[Dues2005 Subform].[Form]![TotalPayments]" (without the quotes). Click OK.
• Set the format to currency. Set the name to MemberTotal.
• Switch to Form View. Well look at that, the total payments for each member are displayed. At least they should be. If not, make sure that you have entered the right form and field names. Check for spelling and spacing errors.
• Once the MemberTotal controls is working, add another payment for Anthony Benoit: $25 in work. Notice that the total goes to $30.00.
• Print the form.
5. Now you will create a combo box to make it easy to find a particular member in the MemberPayment form.
• You know how to find a record using the Find button or the Filter by Form button (if you don't, better review). Now you will create a speedy and easy way of finding a particular record.
• Open the MemberPayments form in Design View. Make some space in the Form Header.
• In the toolbox, make sure the Control Wizards button is on, that is, depressed (it's the one with the magic wand) then click on the Combo Box button, .
• Drag a rectangle into the form header. The Combo Box Wizard dialog box will open.
• Select "Find a record on my form based on the value I selected in my combo box." Click Next.
• The wizard will ask "Which fields contain the values you want included in you combo box?" Double click Last_Name. Click Next.
• Make the column about 50% wider and click Next.
• Label your combo box "&Find Record:" Note: The ampersand ("&") makes the first letter of the label serve as a keyboard shortcut. It will be underlined. Click Finish.
• Click on the label and click the Bold button (with the capital B). Resize the label so that you can read the caption.
• Move the combo box so that it is lined up next to the label. Size it to about one and a half inches. Using the Fill/Back Color button (it looks like a pail of paint tipping over) set the background color of the control to gray. Alternatively you can right click on the control and choose Fill/Back Color.
• Switch to Form View.
• Click on the down arrow in the new combo box. Choose a name.
• The record for that name appears on the form. Wow!
• Wouldn't it be nice if the combo box displayed last and first names in alphabetical order? Make it so.
• Switch to Design View. Right click on the combo box and choose Properties.
• Under the Data or All tab, click on the Row Source property. Click on the Build button on the right (it has three dots on it: "...").
• The query from which the combo box takes its data is displayed. Access created this query for you when you used the Combo Box Wizard.
• Modify the query so that it displays last and first names. The grid in the lower half of the Query Builder window is called the Query By Example (QBE) grid. Click in the Field box that has Last_Name in it.
• Change the box so that it reads:

Last_Name & ", " & First_Name

• Do type the quotes. Be sure to include a space after the comma inside the quotes. The ampersands are joining together multiple field names (this is called concatenating).
• In the sort box, choose Ascending.
• Close the Query Builder and choose yes when prompted to save changes.
• Close the properties box.
• Switch to Form View. Use the Find Record control to select records.
• One more thing: Click the Next Record button (shows a ►) at the very bottom of the form. The next record should be displayed, but the Find Record combo box doesn't update itself to match the displayed record. This is because the combo box is an unbound control-the displayed value is not stored as data. You can synchronize the control with the record displayed if you are brave enough to write a Visual Basic event.
• Switch to Design View.
• Display the properties box for the combo box (right click, properties). Note the name of the combo box (it may be Combo18). Close the properties box.
• Click the Form Selection button in the top left hand corner of the form window (it is the little gray button at the point where the horizontal and vertical rulers would intersect).
• On the main toolbar at the top, click the Form Properties button (it shows a finger pointing at a list). Alternatively choose Properties from the View menu.
• Choose the Event tab. Go to the row named On Current. Click the Builder button ("..."). Choose Code Builder.
• The form module for the MemberPayments form opens with a new event, Form_Current()", automatically inserted.
• Hit TAB then type the following:

Combo18 = MemberID     '     Update the Find Record combo box

• If the name of the combo box is something other than Combo18, type that name in place of Combo18. This simple Visual Basic statement sets the value of the Combo18 control to the value of the MemberID field whenever the current record is changed. The text to the right of the single quotation mark is a comment to help any human who is trying to read the code. The computer ignores comments.
• Close the form module window using File, Close and Return.... Close the Properties box. Switch the form to Form View.
• Your form should now work beautifully. Select a record with the Find Record control. Go to the next record. The Find Record control automatically updates itself.
• What happens if you type a new record using the form?
• Hit the New Record button at the button of the form ("►*"). Type in information for a new member (James Watt, 54 Meridian Street, Groton CT 06340; payment today of $100 cash).
• Check the Find Record control. It is blank. Click the down arrow for the drop down list. Watt, James is not there. If you choose Records, Refresh, the drop down list will be updated.
• Select the record for Watt, James and print the form.
6. Close Access and then use WebCT to send me your *.mdb file.
.