Shelly Cashman Access 365 | Module 8: SAM Critical Thinking Project C Connect Marketing Group

GETTING STARTED

1. Save the file SC_AC365_CT8C_FirstLastName_1.accdb as SC_AC365_CT8C_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_CT8C_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. Connect Marketing Group is a national company that provides marketing services such as contact research, brand building, and merchandising to retail stores. As a sales analyst working with the company database, you and your colleagues want to access forms and reports by clicking tabs and buttons rather than using the Navigation Pane. Modify the Preview CompanyAccounts macro so it opens the report in Print Preview. Save and then close the Preview CompanyAccounts macro.

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

a. With the Single Step tool active, run the macro.

b. Correct the error.

c. Turn off the Single Step tool. Save and close the Open ContactAddresses macro.

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

a. Add the first submacro to the macro, using Open Accounts Table as the name.

b. Add the appropriate action to open the Accounts table in Datasheet View and in Edit data mode.

c. Add a second submacro to the macro, using Open Companies Table as the name.

d. Add the appropriate action to open the Companies table in Datasheet View and in Edit data mode.

e. 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 Services table in Datasheet View to create a data macro as follows that verifies each service team is assigned 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 Team field has 3 or fewer members. If it does, the macro should set the value to 3.

c. Add an Else If statement that checks whether the Team field has more than 12 members. If it does, 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 Services table.

Figure 2: Data Macro for a Before Change Event

5. Create a Datasheet form based on the ProgramContacts table, and then save it using ProgramContacts as the form name. Close the form.

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

a. Create a Horizontal Tabs Navigation form.

b. Add the Contacts, Companies, and Services Datasheet forms to the Navigation form in that order.

c. Use Data Entry Navigation Form as the new form title.

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

Figure 3: Data Entry Navigation Form in Form View

7. Open the Contacts form in Datasheet View, and then complete the following tasks to create a UI macro that opens the ContactSearch form when a user selects a value in the ContactID column:

a. Open the Macro Builder for the On Click event of the ContactID field.

b. Set a temporary variable using CN as the name and [ContactID] as the expression.

c. Add an action that opens the ContactSearch form as a Dialog window when [ContactID]=[TempVars]![CN].

d. Add an action to remove the temporary variable. 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 Contacts Form

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

a. Add the FormsList form as the last horizontal tab.

b. Use Other Forms as the name of the new tab.

c. Move the Companies 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 an action that runs a macro.

c. Select Open Forms.Open ServicesAndAccounts Form as the macro to run.

d. Display Open ServicesAndAccounts Form as the text on the command button.

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

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 the width of the widest button.

b. Adjust the spacing to create equal vertical space between the buttons.

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

Figure 6: Lookups Form in Design View

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

a. Split the cell containing the FirstName value horizontally.

b. Delete the Last Name label.

c. Move the cells so that the value for the LastName field appears to the right of the FirstName field.

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

e. Move both Date of Birth controls below the Contact Name controls.

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

Figure 7: ContactsAndPrograms Form in Layout View

12. Open the ContactsByPrograms 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: ContactsByPrograms 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.