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

GETTING STARTED

1. Save the file SC_AC365_8B_FirstLastName_1.accdb as SC_AC365_8B_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_8B_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 and the other association members want to access forms and reports by clicking tabs and buttons rather than using the Navigation Pane. Open the Preview CommitteeResidents macro in Design View to modify the macro so it opens the report in Print Preview. Save and then close the Preview CommitteeResidents macro.

2. Open the Open ResidentAddresses macro in Design View to correct an error as follows:

a. Select the Single Step button.

b. Run the macro.

c. Correct the error. (Hint: The macro should open the ResidentAddresses report in Print Preview.)

d. Deselect the Single-Step button. Save and close the Open ResidentAddresses macro.

e. Save and close the ResidentAddresses report.

3. Create a new macro with two submacros as follows to make it easy to open frequently used tables:

a. Display the Action Catalog, if necessary.

b. Add the first submacro to the macro, using Open Programs Table as the name.

c. Add the OpenTable action to open the Programs table in Datasheet View and in Edit data mode.

d. Add a second submacro to the macro, using Open Residents Table as the name.

e. Add the OpenTable action to the second submacro to open the Residents table in Datasheet View and in Edit data mode.

f. Save the macro using Open Tables as the macro name. Confirm that your macro matches Figure 1, and then close the macro.

Figure 1: Open Tables Macro

4. Open the Committees table in Datasheet View to create a data macro as follows that verifies each committee has at least three members and no more than 12:

a. Create a Before Change data macro for the table.

b. Add an If statement that checks whether the Members field is less than or equal to 3. If it is, the macro should set the value in the Members field to 3.

c. Add an Else If statement that checks whether the Members field value is greater than 12. If it is, the macro should set the value to 12.

d. Verify the data macro matches the one shown in Figure 2. Save and close the macro and then save and close the Committees table.

Figure 2: Data Macro for a Before Change Event

5. Create a Datasheet form based on the Representatives table, and then save the form using the default text Representatives as the form name. Close the form.

6. Create a form as follows to provide access to the most common lookup forms in the database:

a. Create a Navigation form using the Horizontal Tabs layout.

b. Add the CommitteeResidents, ResidentDues, and ResidentsAndPrograms forms to the Navigation form in that order.

c. Change the title (in the Form Header) using Lookup Navigation Form as the new title.

d. Save the navigation form using Lookup Navigation as the form name. Switch to Form View, display the CommitteeResidents tab, and then confirm that your form matches Figure 3. Save and close the Lookup Navigation form.

Figure 3: Lookup Navigation Form in Form View

7. Open the Residents form in Datasheet View, and then complete the following tasks to create a UI macro for the form:

a. Open the Property Sheet for the ResidentID field.

b. Open the Macro Builder for the On Click event on the Event tab.

c. Create a macro that opens the ResidentSearch form when a user selects a value in the ResidentID column. Set a temporary variable using CN as the name and [ResidentID] as the expression.

d. Add an OpenForm action using ResidentSearch as the form name, [ResidentID]=[TempVars]![CN] as the Where condition, and Dialog as the window mode. Confirm that your macro matches Figure 4. Save and close the macro and then save and close the form.

Figure 4: UI Macro for the On Click Event in the Residents Form

8. Open the Main Menu Navigation form in Layout View to update the form as follows:

a. Add the FormsList form to the Main Menu Navigation form as the last horizontal tab.

b. Rename the FormsList tab using Other Forms as the new tab name.

c. Move the Committees tab so that it appears first in the list. Confirm that the form matches Figure 5, and then save and close the form.

Figure 5: Main Menu Navigation Form in Layout View

9. Open the Lookups form in Design View to complete creating the form as follows:

a. Add a third command button to the form in the approximate position shown in Figure 6.

b. Using the Command Button Wizard, select Miscellaneous as the category and Run Macro as the action.

c. Select Open Forms.Open ResidentDues Form as the macro.

d. Select the Text option, and then enter Open ResidentDues Form as the text.

e. Use Residents as the name for the command button. Save the changes to the form, but do not close it.

Figure 6: Lookups Form in Design View

10. With the Lookups form still open in Design View, fine-tune the form design as follows to improve its appearance:

a. Adjust the size of the three buttons To Widest.

b. Adjust the spacing of the buttons to Equal Vertical.

c. Align the buttons to the Left. Confirm that your form matches Figure 6, and then save and close the Lookups form.

11. Open the ResidentsAndPrograms form in Layout View to modify the layout as follows and make the form easier to use:

a. Split the cell containing the value for the FirstName field horizontally.

b. Delete the Last Name label.

c. Move the cell containing the value for the LastName field to the right of the FirstName field.

d. Use Resident Name instead of First Name as the label text.

e. Move the Date of Birth label and DateOfBirth field value below the Resident Name controls.

f. Change the control margins for the control layout to Narrow and the control padding to Medium. Confirm that your form matches Figure 7, and then save and close the ResidentsAndPrograms form.

Figure 7: ResidentsAndPrograms Form in Layout View

12. Open the ResidentsByProgram report in Layout View to change the arrangement of the fields. Change the control layout in the report to Stacked as shown in Figure 8. Save the change to the report and close it.

Figure 8: ResidentsByPrograms Report in Layout View

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.