Locked lesson.

Upgrade

About this lesson

Continue setting up the subform and protect it from editing.

Quick reference

Setting Up Subforms - Part 2

Add the subform to the main form and adjust the subform properties. 

When to use

Add a subform to a form to provide related data from a one-to-many relationship. This allows you to present many related records within a record.

Instructions

Creating the Subform

  • From the Create tab, in the Forms group, click Form Design. 
  • From the Form Design tab, in the Tools group, click Property Sheet. 
  • Select Form from the drop-down. 
  • In the Record Source, select ‘tblRelatedTickets’.
  • From the Form Design tab, in the Tools group, click Add Existing Fields. 
  • Select the fields 'RelatedTicketNumber', 'DateLogged', 'Charge' and 'SLA' from ‘tblRelatedTickets’ and add them to the form.  
  • Arrange the fields so they are in one row. 
  • Drag the bottom of the form up to remove the extra space. 
  • Right-click on the tab and switch to Form View. 
  • Check the fields are the correct size. 
  • Select each field and check the Name, Label, and Caption in the Property Sheet. 
  • Rename where required. 
  • Change Allow Datasheet View to Yes. 
  • Change the Default View to Continuous Forms. 
  • Name the form ‘frmRelatedTickets'. 

Adding the Subform to the Ticket Maintenance Form

  • Open ‘frmTicketMaintenance’. 
  • From the Form Design tab, in the Form Controls group, select the Subform/Subreport Control. 
  • Draw a subform box at the bottom of the form. 
  • From the Subform Wizard, select Use an existing form. 
  • Choose 'frmRelatedTickets' from the list. 
  • Click Next. 
  • Select Show tblRelatedTickets for each record in tblTicket using TicketNumber. 
  • Click Finish. 
  • Resize the subform.
  • Switch to Form View to review. 
  • Save and Close. 

Disable the Record Selector, Edits, and Deletions

We can change the subform properties to limit changes to the subform. 

  • From the Property Sheet change the following properties:
    • Record Selectors: No
    • Navigation Buttons: No
    • Allow Additions: No
    • Allow Deletions: No
    • Allow Edits: No
  • Review the changes in Form View.
Login to download
  • 00:04 So now we've created our related tickets table and
  • 00:07 we've linked it to table Ticket, it's time to create a subform.
  • 00:13 So let's jump up to the Create tab and
  • 00:16 we're going to go into forms and Form Design.
  • 00:20 First thing to notice here, take a look over in the Property Sheet,
  • 00:24 this form is currently not bound to any record source.
  • 00:27 Now we want to use fields from the table we've just created, table RelatedTickets.
  • 00:33 So let's bind it to that table.
  • 00:37 Now if we jump up to add existing fields,
  • 00:39 we're going to be able to see all of those fields from that particular table.
  • 00:44 Now I'm just going to add three of these fields.
  • 00:46 We're going to have the related ticket number.
  • 00:49 I'm not really going to need the ticket number that it's related to,
  • 00:52 because we're just going to see it within that ticket number.
  • 00:56 So let's have date logged and the charge as well.
  • 01:01 So let's do a little bit of rearranging and move these into position, Like so.
  • 01:07 Let's make sure these all align.
  • 01:09 So we're going to select them, go to Arrange and align to the top.
  • 01:15 And we're going to drag this canvas up because we don't need all of that
  • 01:19 additional space.
  • 01:21 Now let's quickly check what the form looks like in Form View.
  • 01:25 And if I scroll through my records, I can see that, yes,
  • 01:28 everything's fitting quite nicely.
  • 01:30 I don't think I need quite as much space for this related ticket number.
  • 01:34 So let's go back to Design View.
  • 01:37 And let's just make this a bit smaller, and
  • 01:39 then we can move everything else along a little bit.
  • 01:44 And I think because I do have a little bit more room I can move things along.
  • 01:47 I think I'm also going to add the SLA field as well.
  • 01:50 So let's make this slightly smaller, let's move charge along and
  • 01:56 let's add SLA as well because this is a very small field.
  • 02:02 There we go, let's take one final look in Form View.
  • 02:06 Let's scroll through our records, at the bottom I think that looks a lot better.
  • 02:11 So this is basically going to be our subform.
  • 02:15 Now let's quickly go back to the Property Sheet for this form.
  • 02:18 And I'm going to make sure I have form selected in the Property Sheet.
  • 02:22 Of course, you can see there we have some road labels.
  • 02:25 I would recommend that you go through and rename all of these and
  • 02:29 just make sure that you have allow Datasheet View enabled just there.
  • 02:33 So now let's close down the form.
  • 02:36 Let's say, yes, we want to save it and
  • 02:39 we're going to call this frmRelatedTickets and click on OK.
  • 02:44 Now that we've created the subform it's time to add it to the main form.
  • 02:49 So we're going to double click to open TicketMaintenance.
  • 02:51 Let's jump into Design View and we're going to add a subform at the bottom here.
  • 02:57 Now I need to give this form a little bit more room.
  • 03:01 Drag that down and
  • 03:02 I'm going to use one of my form controls on the Form Design ribbon.
  • 03:07 And the form control we're going to use is this one here.
  • 03:11 It is the subform, subreport form control.
  • 03:15 Let's click and we can just draw, Where we want our subform to go.
  • 03:22 Now a couple of things to notice here.
  • 03:24 We have the Subform Wizard pop up, and currently the subform is unbound.
  • 03:30 Now we're going to work through the Subform Wizard to populate this area with
  • 03:35 our subform.
  • 03:36 So the first thing we need to determine is what data we'd like to use for
  • 03:40 our subform.
  • 03:41 Well, I want to use an existing form and it's this one here, frmRelatedTickets.
  • 03:48 Click on next Next,
  • 03:49 would I like to define which fields link the main form to the subform?
  • 03:54 Now because I've created a relationship,
  • 03:56 it's picked up that we're linking the two tables using the ticket number field.
  • 04:00 So this is correct, let's click on Next.
  • 04:03 What would you like to name your subform or subreport?
  • 04:05 Well, I'm going to keep it on frmRelatedTickets for the time being, and
  • 04:09 click on Finish.
  • 04:11 So now let's take a look at what we have.
  • 04:13 Again, I think I might need to give this a little bit more room.
  • 04:17 And if we drag this down,
  • 04:18 I should find that I can see all of those different fields.
  • 04:23 Now if we right click and take a look at what this form looks like,
  • 04:28 currently I have the subform showing in single form.
  • 04:32 So it's only going to show me one ticket at a time.
  • 04:35 Now I want a big long list of all of the related tickets.
  • 04:39 So we're going to open up frmRelatedTickets.
  • 04:44 We're going to jump into Design View.
  • 04:47 And we're going to change it from single form to continuous forms.
  • 04:53 Let's close this down and save the changes.
  • 04:57 So now if we reopen up TicketMaintenance.
  • 05:00 Go into Form View, take a look at what we have.
  • 05:05 I can now see all of the tickets that are related to ticket 123456.
  • 05:11 Now there are of course some other changes I can make.
  • 05:14 For example, maybe I don't want to show the record selector at the bottom.
  • 05:18 So I can open up my related tickets form again.
  • 05:23 Go back into Design View and make those changes in the Property Sheet.
  • 05:28 So I'm going to say record selectors, we're going to change that to no.
  • 05:31 Navigation buttons, let's change that to no.
  • 05:35 We're not going to allow additions, deletions or edits either.
  • 05:40 Let's close this form down, save the changes,
  • 05:43 open up TicketMaintenance and take a look at it in Form View.
  • 05:48 And now that looks a lot cleaner.
  • 05:51 The final things I'm going to do off camera are just change the name
  • 05:55 of this label and also go through and rename all of these labels.
  • 05:59 But that is effectively how you can create a subform and add it to your main form.

Lesson notes are only available for subscribers.

Gift this course