Forms & Reports:
Subforms

Step-by-Step: Subforms

---

What you will learn:

to add a subform using drag-and-drop to modify the source of a subform to format a subform to add a subform to a form using Toolbox to rename a subform

---

Start with:  ,  resource files, worldtravel.mdb from the previous lesson

You will use some forms that you already created and modify them to include subforms to show:

• projects with a subform showing staff assigned to that project

• agents with a subform that shows that agent's clients.

---

Create Subform with Drag-and-Drop

You will modify an existing form of projects by adding a subform of people assigned to the projects, based on an existing table.

1. Open the form Projects Form directly in Form Design View.
   This form was created in a previous lesson.
    

2. Arrange the Database Window and the Project Form window so that you can see both.
    

3. In the Database Window, open the Tables list.
    

4. Drag the table ProjectStaff and drop it below the Budget text box.
   
   The Subform Wizard opens.
    

5. Accept the default choice for fields to link the subform to the form.
    

6. Click on Next.
    

7. Enter as the name Subform-Project Staff.
   It is handy to group subforms together in the Forms list by starting their names with "Subform". This makes it easy to tell what's a subform and what's not.
    

8. Click on Finish.
   The wizard closes, putting you back in Form Design View.
    

   Access automatically resizes the Detail section and creates a default-sized subform control.
    
   The default size is small!

   The AutoFormat used by the subform is the last one you used. This can be disconcerting!
    

9. Switch to Form View.

   Is the subform working?
   It clearly is showing something! But is it useful?
   Looks like too many numbers and not enough user-friendly data.

   Strangely, the subform displayed as a form in Form Design View but it actually shows as a datasheet when in Form View.
    

10. Click on the subform while still in Form View.
     

11. If necessary, open the Properties dialog.
    The Default View property for the Form (in this case your subform) shows a value of "Datasheet". That's why you see a datasheet when in Form View. It does not explain why you see a form when in Form Design View!
     

12. Click the horizontal scroll button at the bottom right of the subform to see what other columns there are.
     

13. Inspect the datasheet for each record in the main form, using the navigation buttons at the bottom of the window.
    
    The subform does change with the records. Good! But there are issues...

    Issues:

    • The column EmployeeID is blank for all records. That's not right!

    • There are no names to go with those Job titles.

    • The label for the subform is overlapping the Budget text box.

    Looks like some formatting is in order plus a change to the source of the subform so we can see some names.
     

14. Save the form as Projects with Project Staff.
     

---

Change Source for Subform

Since there is something wrong with the datasheet (EmployeeID is blank), that should be the first item to fix. We know that you put values in the underlying table, so the problem must be that those values are not linking properly to the main form.

Thinking:
What field do you need to link the subform to the main form? ProjectID.

What data do you really want to see in that subform? The full names of staff members assigned to the project and their jobs.

1. Switch to Form Design View.
    
2. If necessary, click on the subform to select it.
   (Notice carefully whether the handles show up for the subform or for part of the form inside the subform control.)
    
3. Click on the Form Selection button at the upper left of the subform.
    
4. In the Properties dialog, click in the Record Source box and then on the ellipsis button that appears.
   A message box appears, for you to confirm that you meant to open the Query Builder.
    
5. Click on Yes.
   The Query Builder opens with the ProjectStaff table already showing.
    
6. Add the query QFullName-Staff to the Query Builder.
   You created this query earlier to concatenate the name parts into a single name.
    
7. Create a join between the two tables by dragging EmployeeID and dropping it onto StaffID.
    
8. Drag to the grid the fields ProjectID, Job, and SFullName.
    
9. Run the query to verify that you are going to get the data that you want.
   Looks good. It shows the field that links and the two data fields that you want to show in the subform.
    
10. Close the query by clicking the Close button .
    (Do not click the Save button on the toolbar! You don't want this query in the Queries list.)
     
    A message appears asking if you want to save your changes to the SQL statement.
     
11. Click on Yes.
    You are back in Form Design View, with the subform selected.
     

12. Save the form (Projects with Project Staff).

---

Format Subform

In the subform, you need to delete some controls and add a new one.
 (You may need to scroll the subform to see some controls.)

1. Delete the following controls and their labels:
       ProjectStaffID
       EmployeeID
       ProjectID
    
   You may wonder about deleting ProjectID, since it is needed to link the form and subform. It is not needed on the subform itself, just in the query underlying the subform.
    
2. If necessary, click in the Form Selection box to select the subform.
    
3. If necessary, show the Field List.
    
4. Drag the field SFullName and drop it on the subform somewhere.
   It doesn't matter where at this point.
   
   Problem: No SFullName in the list
   You do not have the subform's form selected. Click on a blank area of the main form to deselect and try again.
    
5. Drag the label for the subform to the left and line it up with the other labels on the main form
    
6. Edit the label to read Project Staff.
    
7. Switch to Form View.
    
8. Inspect the main form records and the subform records.
   Much better.
   But the columns are not wide enough. An easy fix.
    
9. While still in Form View, move your mouse over the right edge of the Job column heading and double-click.
   The column widens to fit the displayed values.
    
10. Repeat for the Name column.
    This width will be remembered!
     
    Would you rather see the Name column first? Also easy to do.
     
11. Click on the Name heading.
    The whole column is selected.
     
12. Move your mouse pointer back over the Name heading and drag left until a black bar appears at the left of the Job column.
     
13. Drop.
    The column moves.
    This change will also be remembered. Sweet indeed!
     
14. Save the form (Projects with Project Staff).
    Changing Views: The user can change the view and several other features of the subform while in Form View. Right-click on a column heading. The menu that appears has several useful choices.
     

---

Create a Query

Next you will create a form for agents that shows their clients. You will create a new query to pull together the fields that you need. This information meets in the Trips table. For the purposes of this form, you only need to show the client's name, not all of the other info. You already created a query that glued the name parts together, but to sort those names alphabetically you must refer to the parts again. <sigh>

1. Open a new blank query in Query Design View.
    

2. Add the tables Trips and Clients and the query QFullname-Client to the design.
    
   These already have relationships defined, as the join lines indicate.
    

3. Drag the fields AgentID,  CFullName, LastName, FirstName, MiddleName to grid at the bottom, with the resulting columns in that order.
    

4. Sort Ascending the name parts, LastName, FirstName, MiddleName and uncheck their Show boxes.
    

5. Run the query.
   There are 11 records in neat alphabetical order.
    
   This list shows a client for each trip in the Trips table. Some clients took more than one trip!
    

6. Save the query as QAgents and Clients-fullname.
    

---

Create Subform with Tool

1. Open the form Staff-tabbed in Form Design View.
   You created this form in a previous lesson. The form has a tab control with several pages. You will add another.
    

2. Right click on the tabs.
   A popup menu appears.
    

3. Select  Insert Page .
   A new page appears with a default name, Page + a number, like Page225. Recall that the number depends on how many controls you have inserted already, including the ones that you have deleted.
    

4. In the Properties dialog for the page, change the Name to Clients.
   The tab text changes to match.
    

5. Click on the Subform/Subreport tool on the Toolbox bar.
    

6. Drag on the Clients tab to make a box about as large as the tab page.
   The Subform Wizard opens.
    

7. Accept the first choice, Use existing Tables and Queries.
    

8. Click on Next.
    

9. Select the query that you just created, QAgents and Clients-fullname.
    

10. Click on the >> button to move both fields to the right.
     

11. Click on Next.
     

12. Accept the suggestion selected in the list.
     

13. Click on Next.
     

14. Accept the suggested name for the subform.
     

15. Click on Next.
    Access creates a subform using its default formatting.
    In Form Design View, this looks horrible. Even though you dragged a large control, Access made a little one. Humph!
     

     

16. Switch to Form View.
     
17. Click on the Clients tab.
    Better than design view but still strange. You will fix this up shortly.
     
    Problem: Subform is on top of all the tab pages
    You did not get the subform onto a tab page to start with. It is floating on top of the tab control.
    Solution: In Form Design View, click the edge of the subform to select it. Copy. Delete. Click the Clients tab. Paste.
     
18. Save the form as Agents with Clients.
     
19. Navigate through each of the Staff records with the Clients tab page showing to see how the subform fits.
     
20. Switch to the Database Window. Look at the list of Forms.
    Access created a new form for you, QAgents and Clients-fullnames subform.
     
    The awkward name is from the query that the subform is based on.
     

---

Rename Subform

If you change the subform's name, Access is cooperative and changes the references to it for you! Let's prove that! The main form and subform must be closed.

1. Close the form, Agents with Clients.
    
2. In the Database Window, right click on the new form,  QAgents and Clients-fullname subform.
    
3. From the popup menu select  Rename .
   
   If you did not close the form, a message appears telling you that you cannot rename an open form.
   
    
4. Type Subform- Clients of Agent as the new name and click out to accept the change.
   (Watch the spaces!)
    
5. Open the main form, Agents with Clients and click on the Clients tab.
   The subform shows up, even though you did not edit the form after the name change. Thank you, Access!

---

Modify Form Source

Most of the staff members entered so far in the Staff table do not have any clients. That's because they are not agents! You need to change the source for the main form so that only Agents show. Currently the source is the table Staff.

1. Switch to Form Design View.
    
2. In the Properties dialog for the whole form, click in the Record Source box and then on the ellipsis button.
   A message box asks if you meant to invoke the Query Builder.
    
3. Click on Yes.
   The Query Builder opens with the Staff table loaded.
   You need to restrict the records to just the agents.
    
4. Drag the * to the grid.
   This includes all fields in the query results.
    
5. Drag the field Title from the Staff list to the grid.
    
6. Uncheck its Show box.
   The Title field is already included in the results because of the * column.
    
7. Enter Agent in its Criteria row.
    
8. Run the query.
   Make sure that all of the records show Agent in the Title column and there is only 1 column listing the titles.
    
   Notice that the criteria are not case sensitive. Both "Agent" and "agent" were accepted.
    
9. Close the query by clicking the Close button .
    
   A message appears, asking if you want to save your changes and update the property.
   
    
10. Click on Yes.
     
11. Switch to Form View.
    Aha! A different first record and a different number of records in the Status bar.
     
12. Click on the Company Info tab.
    That is the tab where the Title control is.
    
    Problem: Title field shows #Name?
    Access was confused because there were 2 fields named Title in the query results. You forgot to uncheck the Show box for the field Title that you dragged separately to the query grid. The * column includes Title also.
    Solution: Go back to Design View, open Query Builder, and fix the source query.
     
13. Navigate the records and verify that all have the title Agent and that the subform shows on the Clients tab.
    If you checked the query datasheet, there should not be a problem.

---

Format Subform

That tiny subform needs help! You need to enlarge it and make some changes to clean things up.

It can sometimes be a little tricky to select what you want. You can select the subform control or the form inside the control, plus you can select a section or a control in the subform. Watch the handles!

1. Switch to Form Design View.
    
2. Click on the edge of the subform to select it.
   The handles should be on the outside.
   The label for the subform may be partially hidden at the top.
    
3. If necessary, drag the subform down a bit until you can see the label.
    
4. Click on the label for the subform.
    
5. Delete the label.
    
    
6. Click on the edge of the subform, if necessary, to select it.
   Be sure that the handles are on the outside of the subform control.
    
7. Drag the bottom edge of the subform down toward the bottom of the tab page.
   
   The subform is showing Form View instead of the Datasheet View that we saw in the regular Form View. Confusing!
    (Your subform may have a different look.)
    
8. Click in the Form selection box, upper left of the subform, to select the whole form.
   (You may need to click it twice. Once to select the subform and again to select the box.)
    
9. Select the AgentID control and delete it.
   Now that the form and subform are linked, it is OK to delete the AgentID control from the subform. It is still in the underlying query.
    
10. Edit the label for the CFullName control to read Client.
     
11. In the main form, change the label Staff in the Form Header to read Agents.
     
12. Switch to Form View.
    How is it looking now?
    The extra height you gave to the subform results in blank areas. But as more clients are added to the database, the vertical area will fill up and scroll bars will appear.

    

13. Save the form. (Agents with Clients)

Remaining issue: Duplicate names in the Client list when a client has arranged multiple trips. The duplication of names cannot be resolved easily for a form, but it can be fixed in a report.