Shelly Cashman Access 365 | Module 7: SAM Project B Haverhills Neighborhood Association

GETTING STARTED

1. Save the file SC_AC365_7B_FirstLastName_1.accdb as SC_AC365_7B_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. To complete this Project, you will also need the following files:

Support_AC365_7B_House.png

3. With the file SC_AC365_7B_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 create forms that have features such as combo boxes for finding records and command buttons to make forms easier to use. Add a form to the database as follows to track contacts and account contracts:

a. Create a form in Design View.

b. Open the Property Sheet for the new form, and then apply the Record Source for the form to the ResidentDues query.

c. Add the ResidentID field to the form, and then move the control and its label so that the left edge of the label is at the 1" mark on the horizontal ruler.

d. Add the FirstName, LastName, Phone, and AnnualDues fields (in that order) to the form. Position the left edges of the text boxes at about the 1.5" mark on the horizontal ruler.

e. Resize the controls so that the right edges of the text boxes are at the 3" mark on the horizontal ruler.

f. Save the form using AnnualDues as the name, confirm that the form matches Figure 1, and close it.

Figure 1: AnnualDues Form in Design View

2. Open the CommitteeLookup form in Design View, and then modify it as follows to identify the form and use consistent formatting:

a. Add a title to the form.

b. Edit the title to insert a space between the words.

c. Expand the Form Header section so that the bottom border of the section is at the .75" mark on the vertical ruler.

d. Apply a 22-point font size to the form title.

e. Resize the title control to display the complete title.

f. Use the Format Painter to apply the formatting of the Committee ID label to the other labels in the Detail section. Save the CommitteeLookup form but do not close it.

3. With the CommitteeLookup form still open in Design View, add a combo box to the form to provide a way for users to select values:

a. Expand the height of the Detail section by about 0.5".

b. Use the Combo Box Wizard to add a combo box to the Detail section of the form.

c. To display the current data from the bound record source, select I want the combo box to get the values from another table or query.

d. Select the Residents table as the source of the combo box values.

e. Add the ResidentID and LastName fields to the combo box.

f. Sort the combo box by the LastName field in ascending order.

g. Hide the key column and resize the LastName field to its best fit.

h. Choose to Remember the value for later use.

i. Assign Resident as the name of the combo box.

j. Move the combo box control below the Members control, if necessary.

k. Align the left side of the combo box control with the left side of the Members control, and align the left side of the Resident label with the left side of the Members label. Confirm that your form matches Figure 2, and then save and close it.

Figure 2: CommitteeLookup Form in Design View

4. Modify the ResidentsAndPrograms form as follows to display a full name rather than separate first and last names on the form:

a. Open the ResidentsAndPrograms form in Design View.

b. Add a text box control to the form. Position the left edge of the control at the 3.25" mark on the horizontal ruler below the ProgramType control.

c. Create a calculated control that concatenates the FirstName field, a space, and the LastName field. (Hint: Your control should contain the formula =[FirstName]&' '&[LastName].)

d. Update the label using Resident Name as the new text.

e. If necessary, align the top and bottom of the new text box control and label with the top and bottom of the ResidentID control. Save but do not close the ResidentsAndPrograms form.

5. With the ResidentsAndPrograms form still open in Design View, modify the form as follows to show resident dues and program budget information:

a. Add a tab control to the form. Position the top-left corner of the control at the .5" mark on the horizontal ruler and the 1" mark on the vertical ruler.

b. Change the caption of the first tab using Dues as the tab name.

c. Change the caption of the second tab using Chart as the tab name. Save the ResidentsAndPrograms form but do not close it.

6. With the ResidentsAndPrograms form still open in Design View, add a chart to the form to compare account information:

a. Add a chart to the Chart tab in the tab control.

b. Select the Programs table as the data source for the chart.

c. Select the ProgramID, ProgramType, and Budget fields for the chart.

d. Select the Column Chart as the chart type.

e. Use the ProgramType field as the Series values (if necessary).

f. Accept the ResidentID field as the linking field.

g. Use Program Budgets as the chart title and do not display a legend. Confirm that the form matches Figure 3, and then save but do not close the ResidentsAndPrograms form.

Figure 3: ResidentsAndPrograms Form in Design View

7. With the ResidentsAndPrograms form still open in Design View, add a subform as follows to list resident dues information:

a. Add a subform to the Dues tab in the tab control.

b. Use the existing Dues subform to add to the tab control. (Hint: Default name will be Dues subform.)

c. Choose the Show SQL Statement for each record in ResidentPrograms using ResidentID from the list. Save but do not close the ResidentsAndPrograms form.

8. With the ResidentsAndPrograms form still open in Design View, add a command button as follows to provide a quick way to close the form:

a. Use the Command Button Wizard to add a command button below the tab control.

b. Select Form Operations as the category and Close Form as the action.

c. Select the Text option button with the text Close Form.

d. Assign the meaningful name CloseForm to the button.

e. Position the button so that its left edge is at the 5" mark on the horizontal ruler and its bottom edge is at the bottom border of the Detail section. Display the ResidentsAndPrograms form in Form View, confirm that it matches Figure 4, and then save and close the ResidentsAndPrograms form.

Figure 4: ResidentsAndPrograms Form in Form View

9. Open the ResidentSearch form in Design View, and then add visual interest by inserting a logo using the picture file Support_AC365_7B_House.png. Save but do not close the ResidentSearch form.

10. With the ResidentSearch form still open in Design View, add a control as follows for finding records:

a. Use the Combo Box Wizard to create the combo box in the Form Header at the 5.5" mark on the horizontal ruler.

b. Choose to Find a record on my form based on the value I selected in my combo box.

c. Add the ResidentID, FirstName, and LastName fields to the combo box.

d. Hide the key column and resize the FirstName and LastName columns to their best fit.

e. Assign the label Find Resident to the combo box. Save but do not close the ResidentSearch form.

11. With the ResidentSearch form still open in Design View, modify the combo box control as follows to assign a meaningful name and to make sure the control stands out in the Form Header:

a. In the Property Sheet for the Find Resident combo box, change the name of the control using FindResident as the new name.

b. Change the special effect for the label to Raised.

c. Draw a rectangle around the Find Resident label and combo box. Position the top-left corner of the rectangle at the 4" mark on the horizontal ruler and the 0" mark on the vertical ruler. Position the bottom-right corner of the rectangle at the 6.75" mark on the horizontal ruler and the 0.5" mark on the vertical ruler. Close the Property Sheet, confirm that ResidentSearch the form matches Figure 5, and then save and close the form.

Figure 5: ResidentSearch Form in Design View

12. Open the ResidentDues form in Design View, and then change the chart on the Chart tab as follows to make it easier to interpret:

a. Edit the chart object, and then change the chart type to a Clustered Column chart.

b. Change the placement of legend to Bottom. Confirm that the form matches Figure 6, and then save and close the ResidentDues form.

Figure 6: Chart Tab in the ResidentDues Form in Design View

13. Open the ProgramEntry form in Design View, and then modify a command button as follows to clarify the purpose of the button:

a. Change the caption of the Close button to use Close Form as the new text.

b. Change the Name property of the button to use CloseProgramForm as the meaningful name. Save the changes to the ProgramEntry form and then 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.