Shelly Cashman Access 365 | Module 7: SAM Project A Connect Marketing Group

GETTING STARTED

1. Save the file SC_AC365_7A_FirstLastName_1.accdb as SC_AC365_7A_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_7A_Search.png

3. With the file SC_AC365_7A_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 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 ContractTotals query.

c. Add the ContactID 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, City, and ContractTotal 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 ContractTotals as the name, confirm that the form matches Figure 1, and close it.

Figure 1: ContractTotals Form in Design View

2. Open the ProgramEntry 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 24-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 Program ID label to the other labels in the Detail section. Save the ProgramEntry form but do not close it.

3. With the ProgramEntry 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 Services table as the source of the combo box values.

e. Add the ServiceID and ServiceDesc fields to the combo box.

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

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

h. Choose to Remember the value for later use.

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

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

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

Figure 2: ProgramEntry Form in Design View

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

a. Open the ServicesAndAccounts form in Design View.

b. Add a text box control to the form. Position the left edge of the control at the 3" mark on the horizontal ruler below the ServiceType 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 Contact 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 ContactID control. Save but do not close the ServicesAndAccounts form.

5. With the ServicesAndAccounts form still open in Design View, modify the form as follows to show account information:

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

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

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

6. With the ServicesAndAccounts 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 Accounts table as the data source for the chart.

c. Select the AccountNumber and ContractTotal fields for the chart.

d. Select the Column Chart as the chart type.

e. Use the ContractTotal field as the Series values.

f. Accept the ServiceID field as the linking field.

g. Use Contract Totals 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 ServicesAndAccounts form.

Figure 3: ServicesAndAccounts Form in Design View

7. With the ServicesAndAccounts form still open in Design View, add a subform as follows to list account billing information:

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

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

c. Choose the Show Accounts for each record in ServicesAndContracts using ServiceID from the list. Save but do not close the ServicesAndAccounts form.

8. With the ServicesAndAccounts 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 ServicesAndAccounts form in Form View, confirm that it matches Figure 4, and then save and close the ServicesAndAccounts form.

Figure 4: ServicesAndAccounts Form in Form View

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

10. With the ContactSearch 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" 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 ContactID and LastName fields to the combo box.

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

e. Assign the label Find Contact to the combo box. Save but do not close the ContactSearch form.

11. With the ContactSearch 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 Contact combo box, change the name of the control using FindContact as the new name.

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

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

Figure 5: ContactSearch Form in Design View

12. Open the CompanyAccounts 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 CompanyAccounts form.

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

13. Open the ServiceEntry 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 CloseForm as the meaningful name. Save the changes to the ServiceEntry 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.