Forms & Reports:
Subforms

Title: Jan's Illustrated Computer Literacy 101


A subform is a form that is embedded in a form, the main form. A subform can also be inserted into a report.

Views for Subform

It is common to show a subform in Datasheet view because it takes less space. A subform can display in another view if that works better in a particular situation.

Form View: Form with subform shown as datasheet Form View: Form with subform shown as form

WarningChanges that break things:
It is easy to forget what queries or subforms go with what forms and reports. Before making changes to existing objects, check the Object Dependencies. Make sure that your planned changes do not break something elsewhere!


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & Reports Arrow: subtopic open
    Icon: StepSwitchboard Form
    Icon: StepCustom Dialog
    Icon: StepLabel Wizard
    Icon: StepChart Wizard
    Icon: StepMultiple Columns
        Subform/Subreport
    Icon: StepSubforms
    Icon: StepSubreports
    Summary
    Quiz
    ExercisesTo subtopics


Search  
Glossary
  
Appendix



Creating a Subform

You can create a subform by dragging an existing table, query, or form and dropping it onto the main form. Or you can use the Subform/Subreport tool from the Toolbox bar. The Subform Wizard will open in either case and take you through a few choices to create a new form. The wizard automatically adds it to the list of Forms in the Database Window. You can open the new form (your subform) and make changes, just like any other form.


How to Select a Subform

In Form Design View, when the subform is not selected and neither is anything inside it:

  • To select the subform control: Click anywhere on the subform.
  • To select the form inside the subform control:
    • Double-click inside the subform but not on any part of the form itself
        OR
    • Click the subform to select it. Click inside the subform but not on the form itself.
       
  • To select a section or control in a subform:
    • Double-click on the section or control you want to select.
        OR
    • Click the subform to select it. Click on the part you want to select.
       

What's Selected?

A subform is actually a form inside a subform control. It can be tricky to know what is selected and therefore to which object your keystrokes and mouse clicks will apply.

Form Design View: Subform control is selectedSubform control:
Look for the handles. When the control is selected, you see Move handles for the control and for its label. Resize handles appear on the edge.

The Properties dialog shows the name and properties of the subform, like the Link Child Fields and Link Master Fields properties.

Dialog: Properties, for subform control
 

Form Design View: Form inside a subform control is selectedForm inside the subform control:
When the form inside the subform control is selected, a black square shows in the Select Form button at the upper left of the form. There are no handles.

The Properties dialog shows "Form" and lists a Record Source and a Default View.

Dialog: Properties - subform
 

Form Design View: section of a subform selectedSection of form in subform control:
When a section is selected, its selection bar will be black. No handles.

The Properties dialog shows the name of the section, like "Detail" or "Form Header.

Dialog: Properties

Particularly tricky is when a subform is on a page in a tab control. In addition to the selections discussed above, you also have the selection of the tab control and of the particular page.


Open Subform in New Window

Subform tend to be a bit small to work in easily in Form Design View. Access allows you to open a subform in a new window, which you can resize to get more working space. The subform must be selected for this option to be available.

  • Right click on a selected subform. Select Subform in New Window
       OR
  • With subform selected, from the menu select  View  | Subform in New Window

Subform open in a new windowThe initial new window is the same size as the subform and in the same place. It doesn't look different! If you drag the new window to a new location, you can see that the subform control is a plain white box.
 

Message: You can't open a subform when it is open in design viewSubform is blank in Form View because the subform was open in another window.WarningClose separate window to view main form:
While the subform is open in a separate window, you cannot view the subform inside the main form in Form View. That is, if you try to open the main form in Form View, you get a warning message and then the form opens with a blank subform.
 


Icon: Step-by-Step 

Step-by-Step: Subforms

 Icon: Step-by-Step

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:  Class diskresource 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. Form Design View: ProjectsOpen the form Projects Form directly in Form Design View.
    This form was created in a previous lesson.
     

  2. Form Design View and Database Window both showingArrange 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. Subform Wizard: Step 1 - fields to linkAccept the default choice for fields to link the subform to the form.
     

  6. Click on Next.
     

  7. Subform Wizard: Step 2 -nameEnter as the name Subform-Project Staff.
    TipIt 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.
     

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

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

  9. Form View: Projects form with new subformSwitch 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. Dialog: Properties - Default View = DatasheetClick 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. Icon: Save 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. Form Design View: Form SelectorClick 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 Button: ellipsis that appears.
    Message: You invokded the Query Builder on a tableA 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. Query BuilderAdd 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. Query DatasheetIcon: Run 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 Button: Close.
    (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.
     Message: Do you want to save changes made to the SQL statement and update the property?
  11. Click on Yes.
    You are back in Form Design View, with the subform selected.
     
  12. Icon: Save 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. Form Design View: deleted controlsDelete 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. Form View: after revising subformDrag the field SFullName and drop it on the subform somewhere.
    It doesn't matter where at this point.

    Icon: TroubleProblem: 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. Form View: Widened columns in datasheet in subformWhile 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. Form View: Moving Name column in subform datasheetClick 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. Form View: after moving Name columnDrop.
    The column moves.
    This change will also be remembered. Sweet indeed!
     
  14. Icon: Save Save the form (Projects with Project Staff).
    Right Click Menu for subformTipChanging 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. Query Design ViewAdd 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. Query Datasheet View: AgentID and CFullNameIcon: Run 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. Icon: Save 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 Menu: Insert PageRight 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. Form Design View: Tab Control -new tabIn 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. Subform Wizard: Step 1 - Use existingAccept the first choice, Use existing Tables and Queries.
     

  8. Click on Next.
     

  9. Subform Wizard: Step 2 -select querySelect 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. Subform Wizard: Step 3 - fields to link withAccept the suggestion selected in the list.
     

  13. Click on Next.
     

  14. Subform Wizard: step 4 - NameAccept 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!
     

     Form Design View: initial with subform

  16. Form View: initial with subformSwitch to Form View.
     
  17. Click on the Clients tab.
    Better than design view but still strange. You will fix this up shortly.
     
    Icon: TroubleProblem: 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. Icon: Save 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. Database Window: Forms - new subformSwitch 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. Right Click Menu: RenameFrom the popup menu select  Rename .

    If you did not close the form, a message appears telling you that you cannot rename an open form.
    Message: You can't rename the database object while it is open
     
  4. Database Window: renaming subformType 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.
    Message: You invoked the Query Builder on a tableA 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. Query BuilderDrag 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. Query Datasheet View: filtered as AgentIcon: Run 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 Button: Close.
     
    A message appears, asking if you want to save your changes and update the property.
    Message: Do you waqnt to save the changes made to the SQL statement and update the property?
     
  10. Click on Yes.
     
  11. Form View: Agents with ClientsSwitch 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.

    Icon: TroubleProblem: 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. Form Design View: Label is hiddenSwitch 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. Form Design View: label deletedDelete the label.
     
     
  6. Form Design View: default subformClick 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. Form in subform control is selectedClick 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. Form Design View: AgentID deletedSelect 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.

    Form View: after revisions

  13. Icon: Save 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.