Shelly Cashman Access 365 | Module 7: SAM Critical Thinking Project C Connect Marketing Group
Advanced Form Techniques
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_CT7C_FirstLastName_1.accdb as SC_AC365_CT7C_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_CT7C_Search.png
3. With the file SC_AC365_CT7C_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. Assign the ContractTotals query as the Record Source property for the form.
c. Add the ContactID field to the form 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 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. Modify the ProgramEntry form in Design View 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. 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. Add a combo box to the ProgramEntry form as follows 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. Choose to get the values for the combo box from the Services table.
d. Add the ServiceID and ServiceDesc fields to the combo box.
e. Sort the combo box by the ServiceDesc field in ascending order.
f. Hide the key column and resize the ServiceDesc field to its best fit.
g. Choose to remember the value for later use.
h. Use Service as the name of the combo box.
i. Move the combo box control below the ContactID control, if necessary.
j. 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 in Design View as follows to display a full name rather than separate first and last names:
a. Add a text box control below the ServiceType control with the left edge of the new text box at the 3" mark on the horizontal ruler.
b. Convert the new text box to a calculated control that uses the formula =[FirstName]&' '&[LastName] to concatenate the FirstName field, a space, and the LastName field.
c. Update the label using Contact Name as the new text.
d. 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. Modify the ServicesAndAccounts 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. Use Billing as the name of the first tab.
c. Use Chart as the name of the second tab. Save the ServicesAndAccounts form but do not close it.
6. Add a chart to the ServicesAndAccounts form as follows to compare account information:
a. Add a chart to the Chart tab in the tab control.
b. Use 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. Add a subform to the ServicesAndAccounts form 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.
c. Choose to show account data for each record in the ServicesAndContracts query using the ServiceID field. Save but do not close the ServicesAndAccounts form.
8. Add a command button to the ServicesAndAccounts form 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. Assign the Close Form action to the button.
c. Display the text Close Form on the button.
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. Add visual interest to the ContactSearch form by inserting a logo using the picture file Support_AC365_CT7C_Search.png. Save but do not close the ContactSearch form.
10. Add a control to the ContactSearch form as follows to find records:
a. Use the Combo Box Wizard to add a combo box to the Form Header at the 5" mark on the horizontal ruler.
b. Choose to find a record on the form based on the value selected in the 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. Modify the combo box control in the ContactSearch form 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. In the CompanyAccounts form, 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. Modify a command button in the ServiceEntry form 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.