Just as the procedures for creating and modifying Web database tables are different, so are the procedures for creating and modifying Web database forms. The most pronounced difference is that instead of using the design view to create and modify forms, you use a view called the layout view. The layout view debuted in Access 2007. One of the advantages of the layout view is that you can design a form in a WYSIWYG fashion with a live preview of the data.
In this exercise, you will modify a couple of the forms in the database that are used to display and update information associated with a particular job applicant.
In Exercise 1, you added some fields to an existing table in the database and added a new table. In order to use these new items in forms, you will modify the ContactsExtended query to reflect the new additions. You will also add a join to the Jobs table to include the Position Title in the query result.
- Right-click on the query ContactsExtended and select Design View
- In the Design tab of the ribbon, click on Show Table
- Select Jobs and click Add. Observe that Access adds the Jobs table to the query designer and the relationship between the tables is apparent. Click Close.
- Right-click on the relationship (the line between the two tables) and select Join Properties
- Select the option labeled Include ALL records from ‘Contacts’ and only those records from ‘Jobs’ where the joined fields are equal. Click OK. This creates a left join so that all contacts are returned regardless of whether there is a value specified in the position field.
- Click on Close
- From the Jobs table, double-click on Position Title to add it to the results of the query
- From the Design tab, click Run to view the query results. Verify that the Position Title field is in the results.
- Close the ContactsExtended query saving your changes
The NameCard form is used as a sub-form on the Main form. In this task, you will modify it to show more information about the job applicant.
- Right-click on the NameCard form in the Navigation Pane and select Layout View
- If the Property Sheet pane is not visible, show it by clicking Property Sheet from the Tools group on the ribbon.
- In the Property Sheet, select Form from the drop-down list
On the Data tab, change the Record Source property from Contacts to ContactsExtended
Figure 17
Record Source Property
- Add the applicant’s status to the form
- Select the cell containing the applicant’s name
- Click on the Arrange tab on the ribbon
- Select Split Horizontally
- On the Design tab of the ribbon, select Add Existing Fields
- Drag and drop Status from the field list to the new cell you just created next to the applicant’s name.
- Select the cell containing the label reading Status and press Delete
Drag and drop the cell containing the Status value and move it one cell to the left
Figure 19
Move the Status Cell
- On the Property Sheet, set the value of the Width property (on the Format tab) to 2.26 or simply drag the guidelines to reduce the size to an appropriate width.
- Using the Format tab of the ribbon, set the font size to 14, change the font color to red, and turn off the Bold format.
On the Arrange tab of the ribbon, select Control MarginsMedium to center the text vertically in the cell
Figure 20
Status Cell with Modified Property Values
- Directly below the applicant’s name, it would be helpful to see the position they are applying for along with the position number (in case there is more than one position available with the same position title). In order to do this, we are going to repurpose the job title text box (txtJobTitle) so that it uses an expression rather than being directly bound to a field value. Perform the following steps to do this.
- Select the cell immediately below the applicant’s name
- Click on the Data tab of the Property Sheet
- Put the cursor in the Control Source property and then click on the ellipses or (…) that appears to display the Expression Builder
- Delete JobTitle and enter =[Position Title] & " (" & [Position] & ")". This formula concatenates the values of the Position Title and Position fields so that you can display them together in a single cell.
- To eliminate potential confusion, clear the Default Value property
Click OK
- Two rows beneath the applicant’s name, you will add the referral source and initial interview date. Begin by selecting the cell two cells beneath the applicant’s name
Using the Property Sheet, set the Height of the cell to 0.25”
Figure 22
Add Referral Source Cell
- On the Arrange tab, select Split Horizontally
- On the Design tab, select Add Existing Fields
Drag and drop the Referral Source field from the Field List to the row, dropping it in the cell highlighted below.
Figure 23
Drag and Drop Referral Source
Figure 24
Adjust Dropdown List to the Left
- Highlight the cell containing the label Referral Source and press Delete.
- Drag and drop the Referral Source one cell to the left.
- Drag and drop the Initial Interview field from the Field List to the cell immediately to the right of the referral source cell
- Select the cell containing the Initial Interview label and press Delete
Drag and drop the drop-down list containing the Initial Interview field and move it one cell to the left. Note that if the current record doesn’t have an initial interview date, nothing will appear in the cell. However, if you view the Property Sheet, you should see that the cell contains a text box with the Control Source property set to Initial Interview
Figure 25
Initial Interview
- Press Ctrl + S to save your work (alternatively right-click on the form tab and select Save).
- Close the form
Exercise 2 Verification
In order to verify that you have correctly performed all steps of exercise 2, proceed as follows:
- Double-click on the form named Main in the Navigation pane to open the form
- If you modified the NameCard form as listed, you should see a navigation form containing a NameCard sub-form showing the selected applicant’s details complete with applicant status, position title and number, referral source, and initial interview date if any.
Navigate to the various Applicants you have entered by clicking on their name in the applicant list (highlighted in red below) on the left side of the Main form.
Figure 26
Contact Card with Modifications