Jan's Working with Databases

Step-by-Step: Subforms

---

What you will learn:

to add a subform using drag-and-drop to change the source of a subform to edit a subform in a new tab to format a subform control to edit subform datasheet from parent form view to create new query as source of subform to add a subform with tool on Controls palette to rename a subform to modify parent form source to format subform

---

Start with:  , resource files, worldtravel-Lastname-Firstname.accdb from folder databases project4 as updated in the previous lesson

You will use some forms that you have already created but you will modify them to include subforms:

• 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 for projects by adding a subform of people assigned to the projects, based on an existing table.

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

2. In the Navigation Pane, drag the linked table Project Staff and drop it below the Budget text box.
   

   A new unbound control appears on the form. The form enlarges automatically to hold it.

   The Subform Wizard opens.
   

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

4. Click on Next >.
   

5. 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.
    

6. 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.

   In Design View, only part of the form shows. That's because of the ruler and section bars. You cannot really tell whether this size is good or not until you look at the Form View.
    

7. Switch to Form View.

   Is the subform working?
   It clearly is showing something! But is it useful?

   The label for the subform control is above the subform, overlapping the Budget control.

   In the subform itself, it looks like too many numbers and not enough user-friendly data. Where are the names of the people assigned to the project?

   Strangely, the subform displayed as a form in Form Design View but it actually shows as a datasheet when in Form View. The property Default View is set to Datasheet, by default for a subform. Datasheet View gets more records in sight at once. That is usually very helpful. So it makes sense, once you think about it. You just have to manage which fields are really needed.

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

9. 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.
   

10. 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. What can you use as the source that includes both?

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 Property Sheet, 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 Project Staff 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.

   Problem: QFullName-Staff fields are named Expr1, Expr2, etc.
   Access lost track of your tables.
   Solution: Close the Query Builder and open the query in Design View. Delete the Expr1: and similar to return the fields to their normal names. Repeat the steps above to invoke the Query Builder again.
   

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 the subform to the main form and the two data fields that you want to show in the subform.

   Only three projects have staff assigned, Projects 1, 2, and 8.
    

10. Close the query by clicking the Close button at the far right of the query tab.
    (While in datasheet view, there is no close button on the ribbon.)

    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. Switch to Form View.

13. Inspect the subform.
    Whoops. Access did not update the form with the new fields. The subform shows the same fields as before.

    The ProjectStaffID field shows an error now because that field is not in the new source.

    The SFullName field is not showing anywhere.
    More editing to do!
    

14. Save the form and close it.
    [Projects with Project Staff]

---

Edit Subform in a New Tab

You need to make changes to the controls on the form that is in the subform control. That form is named Subform-Project Staff. It is easier to edit the form in its own window.

1. Open the form Subform-Project Staff in Design View.
2. Show the Field List.
3. Delete the controls and labels for ProjectStaffID and EmployeeID.
4. Drag the field SFullName to the top of the form.
5. Resize and rearrange the controls neatly.
6. Resize the form to the minimum to hold all of the controls.
7. 

   Switch to Datasheet View.

    

8. Resize the columns as needed to show the data.
   
9. Save and close the subform.
   
10. Open the form Projects with Project Staff in Form View.
    Better. Now you can clean up the layout.
    

---

Format Subform Control

Now you can get that label out where it belongs.

1. Switch to Layout View.
   Adjusting the subform will be easier to do in this view. Design View has too many parts you could accidentally select.
2. Drag the label for the subform to the left but do NOT line it up exactly with the other labels on the main form.
   
3. Edit the label to read Project Staff
4. Widen the data columns in the subform to show all of the text.
5. Resize the subform form control to show just the two columns of data.
6. If necessary, move the subform control and its label to line up with the other controls and labels.
7. Switch to Form View.
   
8. Navigate through the main form records and the subform records.
   Only Projects 1, 2, and 8 have project staff entered.
   Look much better.
   

---

Edit Subform Datasheet while in Form View

Like any other datasheet, you can change the arrangement and width of the columns and sort directly in the datasheet.

1. While still in Form View, move your mouse over the right edge of the Job column heading and double-click.
   The column resizes to be just wide enough to fit the displayed values.
   

2. Repeat for the SFullName column.
   This width will be remembered!

   Would you rather see the names first? Also easy to do.
    

3. Click on the SFullName heading.
   The whole column is selected.
   
4. Move your mouse pointer back over the Name heading and drag left until a black bar appears at the left of the Job column.
   
5. Drop.
   The column moves.
   This change will also be remembered. Sweet indeed!
   

6. Save the form.
   [Projects with Project Staff]

   You could use the datasheet column menu also while in Form View to sort or filter.

   Note: This form cannot be used to update project staff records. The datasheet will not let you type in the SFullName column, which is a calculated column. To be able to add new staff assignments you need a subform that includes the fields from the underlying table(s). You cannot edit a calculated control like SFullName. This type of subform is actually well suited to a report.

---

Create a Query to be Source of Subform

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.
   You will create a new query to use as the source of a subform listing the clients of each agent. Clients and Agents are connected only through the Trips table.
   

2. Add the table Trips and Clients and the query QFullname-Client to the design.

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! Some clients in the Clients table have not booked trips yet. They do not show in the results.
   

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

7. Close the query.

   Note: The query QFullName-Client also has the name fields, but the Show box was unchecked. You could edit the query to show those fields. You would not need the Clients table in the grid for the new query. But does changing the QFullName-Client query cause a problem somewhere else? You would have to check carefully!
   

---

Create Subform with Tool on Controls Palette

Now you will add a subform to a new page on the tab control. You will use the Subform/Subreport tool on the Controls palette.

1. Open the form Staff-tabcontrol 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 page. 
2. Save the form as Agents, with Clients.
3. Right click on the tabs.
   A context menu appears.  

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

5. In the Property Sheet for the page, change the Name property to Clients.
   The tab text changes to match.

6. Click on the Subform/Subreport tool in the Controls palette on the ribbon.
   

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

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

9. Click on Next >.
    

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

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

12. Click on Next >.
     

13. Accept the default Define my own option.
14. Select from Form/Report fields, StaffID.
15. Select from Subform/Subreport fields, AgentID.
    

16. Click on Next >.
     

17. Change the suggested name for the subform to
    Subform-Clients-Staff-tabcontrol.

    This kind of name tells you what will be in the subform (Clients) and which form it goes with.
    

18. Click on Finish.
    Access creates a subform using its default formatting.

    The tab control enlarges automatically and now covers controls on the main form. Whoops.

    

    In Form Design View, this looks horrible. Even though you dragged a control, Access made a wider but shorter one. Humph!
    

19. Switch to Form View.
    

20. Click on the Clients tab and navigate to each agent, using the Navigation buttons at the bottom of the window.
    Only some of the staff members have clients in the Clients table.

21. Use the Navigation button at the bottom to change to record #2, Hector Chavez.

22. Scroll the subform to see all 6 records. One client appears several times, once for each trip.

    This looks better than design view but it is still strange. The photo control is totally covered up and the full name is overlapping the tab control. That's why there is a gap in the border. The full name control has a white background.
    

    Problem: Subform for clients 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 control to select it. Copy. Delete. Click the Clients tab. Paste.
    

23. Save the form as Agents with Clients.
    
24. Navigate through each of the Staff records with the Clients tab page showing to see how the subform fits on the tab page.
    Access seems to have gotten overly excited about the width needed for this subform.
    
25. Close the form.
    [Agents, with Clients] 

---

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. In the Navigation Pane, right click on the new form, Subform-Clients-Staff-tabcontrol.
   

2. From the context menu select  Rename .

   If you did not close the form, a message appears telling you that you cannot rename an open form.

   You must close the form and try again.

3. Type Subform- Clients of Agent as the new name and click out to accept the change.
   (Watch the spaces!)
   
4. Open the main form, Agents with Clients in Form View 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 Parent 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 Property Sheet 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. There should be 8 records.

   

   Notice that 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.
    You are back in Design View for the main form.
    
11. Switch to Form View.
    Aha! There are 8 records in the Navigation 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.

    The filter accepts both "Agent" and "agent".

There are several things that need fixing.

• The window tab should show the current name of the main form.
• The tab control is so wide that it is hiding the photo.
• The main form should show Agents instead of Staff since the source query is filtering the data that way.
• On the Clients page, the subform's label is poorly positioned and the subform's width is what is making the whole tab control too wide.

---

Format Subform

That little subform needs help! You will 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 for the handles!

1. Switch to Form Design View and click on the Clients tab.
   
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 but stop above the Date Updated control. Do not make the tab control taller.
   You are enlarging the subform area so you can see better to edit it in place.

   The subform is showing Form View instead of the Datasheet View that we saw in the regular Form View. This is still 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 and resize it as Best Fit.
11. Resize the CFullName control to 2" wide.
12. Move the label and control to the top left of the Detail section.
13. Drag the bottom edge of the Detail section up below the CFullName control.
14. Drag the right edge of the form to the left to the end of the CFullName control.
    You should now see all of the calculated control that is on top of the tab control.
15. Drag the right edge of the subform control to the left but do not hide any of the CFullName control.
    
16. Select the tab control itself, which is hiding the agent's photo.
17. Use the right center handle to drag the right edge to the left just past the end of the calculated control and the DateUpdated label.
    Now the Photograph control shows again.
    
    
18. In the main form, change the label Staff in the Form Header to read Agents.
19. Change the form's Caption to Agents, with Clients.
20. Switch to Form View.

21. Click on each tab and make sure that all of the controls show at the new width for the tab control.

    How is it looking now? There is a an extra column in the datasheet and extra space below on the tab. The tab control could be somewhat shorter, but this will do.

22. Save the form.
    [Agents with Clients]

Remaining issue: Duplicate names show 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.