Shelly Cashman Access 365 | Module 5: SAM Project B Haverhills Neighborhood Association
MULTIPLE-TABLE FORMS
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_5B_FirstLastName_1.accdb as SC_AC365_5B_FirstLastName_2.accdb
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
2. With the file SC_AC365_5B_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.
a. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Haverhills Neighborhood Association works with the city of Madison, Wisconsin, to improve the neighborhood for residents. As a volunteer with database skills, you need to modify tables and create multiple-table forms. Open the Committees table in Design View. Use the Input Mask Wizard to create an input mask for the Phone field to make it easier to enter phone numbers. Store the data without symbols in the mask. Save the change to the Committees table without closing it.
2. Display the Committees table in Datasheet View, and then add a new field named Comments at the end of the table to record comments about each committee. Use Long Text as the data type for the field. Save the change to the table.
3. With the Committees table still open in Datasheet View, update the record for the Communications committee type (CommitteeID 1) as follows to provide a phone number and comment:
a. Using the input mask, enter 608-555-1677 in the Phone field.
b. Enter the text Creates a monthly newsletter for residents in the Comments field.
4. Display the Committees table in Design View, and then add a new field with the name DirectorPhoto following the Comments field. Use Attachment as the data type for the field, and use Director Photo as the Caption property for the field. Save the change to the table, and then close it.
5. Modify the ProgramEntry form as follows to add a missing field:
a. Open the ProgramEntry form in Design View.
b. Open the field list for the form.
c. Add the Donations field to the form directly below the Budget label and text box. Position the left edge of the Donations text box at the 2" mark on the horizontal ruler and position the top of the label and control at approximately the 2" mark on the vertical ruler.
d. Align the left edge of the Donations label with the left edge of the Budget label. Save the changes to the form, confirm that it matches Figure 1, and then close the ProgramEntry form.
Figure 1: ProgramEntry Form in Design View
6. Create a new form in Design View based on the Dues table to store membership dues information, and then update the form as follows:
a. Add the DuesID, ResidentID, DuesAmount, and DuesDate fields to the Detail section of the form. Position the left edges of the text box controls at the 2" mark on the horizontal ruler.
b. Save the form with the name DuesLookup but do not close the form.
c. Add a title to the form, and then insert a space so that the title is "Dues Lookup". Save the form, confirm that it matches Figure 2, and close the DuesLookup form.
Figure 2: DuesLookup Form in Design View
7. Open the ResidentDues form in Design View, and ensure that the Use Control Wizards button is selected. Use the Subform/Subreport Wizard to add a subform as follows to show account information on the form:
a. Use the Dues table for the subform.
b. Select the DuesAmount, DuesDate, DuesMethod, and Paid fields from the Dues table to add to the form.
c. Accept the default link (Show Dues for each record in Residents using ResidentID) to link the main form to the subform.
d. Save the subform as Dues subform (which is the default name).
e. Delete the label for the subform.
f. If necessary, reposition the subform so it is below the Phone label and text box and its left edge is aligned with the left edge of the Phone label.
g. Resize the subform so its right edge is at the 6.5" mark on the horizontal ruler, as shown in Figure 3. Save and close the form.
Figure 3: ResidentDues Form in Design View
8. Open the CommitteesAndResidents form in Design View, and then modify it by resizing the width of the subform so that the right edge is at approximately 7.5" on the horizontal ruler to match the width of the other form controls. (Hint: Your subform might show only a TableResidents label and no fields.) Save but do not close the form.
9. With the CommitteesAndResidents form still open in Design View, format the form title as follows to make it more distinctive:
a. Change the font color of the title to Dark Blue (4th column, 1st row in the Standard Colors palette).
b. Apply a 24-point font size to the title.
c. Resize the title control to display all of its text.
d. Change the width of the form to 8". Save the form, confirm that it matches Figure 4, and close the CommitteesAndResidents form.
Figure 4: CommitteesAndResidents Form in Design View
10. Open the ResidentBirthdate form in Design View, and then modify the form as follows to make the form easier to use:
a. Delete the Area control and the accompanying label.
b. Change the label for the DateOfBirth control using Birthdate as the new label text. Save and close the form.
11. Open the ResidentEntry form in Design View, and then modify the form as follows to rearrange the controls to match Figure 5:
a. Align the ResidentID, CommitteeID, and CommitteeType controls and labels along their top edges.
b. Align the left edge of the Date of Birth label with the left edge of the Phone label.
c. Align the left edge of the DateOfBirth text box with the left edge of the Phone text box.
d. Adjust the spacing of the FirstName, LastName, Address, Area, Phone, and DateOfBirth controls to have equal vertical spacing. Save, but do not close the form.
Figure 5: ResidentEntry Form with Aligned Controls in Design View
12. With the ResidentEntry form still open in Design View, modify the tab properties to make the form easier to use:
a. Change the tab order so that users tab to the FirstName field immediately after the ResidentID field.
b. Change the Tab Stop property to No for the CommType field. Save, but do not close the form.
13. With the ResidentEntry form still open in Design View, modify the form as follows to identify the form and make it more visually appealing:
a. Add a title to the form using ResidentEntry as the name. (Hint: This is the default name.) Save the form, confirm that it matches Figure 6, and then close the ResidentEntry form.
Figure 6: Final ResidentEntry Form in Design View
14. Open the MarchDues query in Design View, and then modify the query to find all dues for March 2029. The query should return 20 records. Run the query, and then save and close it.
Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.