Locked lesson.

Upgrade

About this lesson

Create a new table, populate it with information, and link it to the ticket table in preparation for creating a subform. 

Quick reference

Setting Up Subforms - Part 1

Start the process of creating a subform. Create a table that holds related ticket information, import data, and link the new table to the ticket table. 

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

A subform is a form within a form. They help us display additional information. To create a subform, we first need to create a table that contains the subform information, then create a subform and finally add the subform to the main form. 

In our example, we are going to create a subform that shows related tickets. We are then going to add the subform to 'frmTicketMaintenance'. 

Related Tickets Text File

First, let's review the information we will be using for the subform. 

  • Open 'RelatedTicketsImport.txt'. 
  • Review the information. 

Creating a Table

  • From the Create tab, in the Tables group, click Table Design. 
  • Add the following fields:
    • RelatedTicketNumber (Data Type=Number)
    • TicketNumber (Data Type=Number)
    • DateLogged (Data Type=Date/Time)
    • Charge (Date Type=Currency)
    • SLA (Date Type=Number)
  • Check the Field Properties for each field. 
  • Select the ‘RelatedTicketNumber’ and the ‘TicketNumber’ field and from the Table Design tab, in the Tools group, click Primary Key. 
  • Name the table ‘tblRelatedTickets’.

Import Your Information

We now need to add some data to the table. 

  • From the External Data tab, in the Import & Link group, select New Data Source. 
  • Select From File and Text File. 
  • Click Browse and select the file ‘RelatedTicketsImport.txt’. 
  • Append it to ‘tblRelatedTickets’. 
  • Click Next. 
  • Select Delimited. 
  • Click Next. 
  • Select Tab and First Rows Contains Field Names.
  • Click Next.  
  • Click Finish and Close. 

Relationships

  • From the Database Tools tab, in the Relationships group, click Relationships. 
  • From the Add Tables pane, select ‘tblRelatedTickets’ and click Add Selected Tables. 
  • Drag the ‘TicketNumber’ field from ‘tblTicket’ to the ‘TicketNumber’ field in ‘tblRelatedTickets’.  
  • Select Enforce Referential Integrity. 
  • Click Create.
Login to download
  • 00:04 It's time now to move on to talking about sub forms.
  • 00:08 And a sub form is basically a form within form.
  • 00:12 And you can use sub forms to show additional information.
  • 00:17 Now, there's a few different ways that you can go about this.
  • 00:20 But by far, my favorite technique for doing this is to create
  • 00:25 the sub form first and then add the sub form to the main form.
  • 00:30 So, let's take a look at what we're going to be doing here so you understand.
  • 00:34 So let's reopen a new ticket maintenance form.
  • 00:39 Double click to open.
  • 00:40 Now, basically what I want to display on here underneath where we have Charge and
  • 00:46 SLA, I want an area that shows any tickets that are related to this ticket.
  • 00:52 So to do this we're going to need to create a related tickets table.
  • 00:57 Then we're going to create a related tickets subform and
  • 01:01 then we're going to add the subform to this form.
  • 01:04 Now before we get going,
  • 01:06 let's take a look at the information we're going to use in our related tickets table.
  • 01:10 So, here is the information and I've just got this stored in a text file,
  • 01:15 which you'll find in the Course Files folder.
  • 01:18 What we have here are five different columns.
  • 01:20 The first column shows the related ticket number,
  • 01:23 and then the second column shows the ticket number that it's related to.
  • 01:28 So you can see here 123456,
  • 01:30 that's the first ticket that we've been working on, that was our ticket example.
  • 01:35 And all of these tickets are related to this one ticket.
  • 01:39 We then have the date that that ticket was logged,
  • 01:43 the charge and also the SLA information.
  • 01:45 So effectively, what I want to happen is when I go to this ticket 123456,
  • 01:51 what I see underneath are all of the related tickets.
  • 01:56 So let's start this process by creating a related tickets table.
  • 02:00 So let's close down our form for the time being.
  • 02:02 Let's go to the Create tab and we're going to choose table design.
  • 02:07 So now I need to add in my fields based on my text file fields.
  • 02:12 So my first field is related ticket number.
  • 02:16 This is going to have a data type of number and
  • 02:20 next field is going to be ticket number.
  • 02:24 This is also going to have a data type of number.
  • 02:27 We then need the date logged field.
  • 02:29 And this is going to be the date and time data type.
  • 02:35 We need the charge field and this is going to be currency.
  • 02:40 And then finally, we're going to have the SLA field.
  • 02:44 And this is going to be a number like so.
  • 02:47 Let's quickly run through and check our field properties for each of these fields.
  • 02:52 So, related ticket number.
  • 02:54 I'm going to leave that on long integer.
  • 02:56 It's required and yes, it's indexed.
  • 02:59 The ticket number, I'm going to leave that on long integer, required and indexed.
  • 03:04 Date logged, well, let's give this a format.
  • 03:07 I'm going to click the drop down and we're going to choose median date.
  • 03:11 This is required and indexed.
  • 03:13 The charge is currency, required and indexed.
  • 03:18 And finally the SLA.
  • 03:20 We don't need a long integer here because this is a very small number.
  • 03:24 Let's go for integer and yes, it's required, yes, it's indexed.
  • 03:28 Now one thing we haven't done here yet
  • 03:31 is we haven't assigned one of these fields as the primary key.
  • 03:34 And what I'm going to do here is something a little bit different.
  • 03:37 I'm going to create a composite primary key.
  • 03:40 And basically all that means is that I'm using two or
  • 03:43 more fields to create a primary key.
  • 03:46 So, I'm going to select the related ticket number and
  • 03:50 the ticket number field and I'm going to click primary key.
  • 03:54 So effectively, the primary key for this table is going to
  • 03:58 be the related ticket number followed by the ticket number and
  • 04:02 that will give me a unique value to identify the records.
  • 04:05 So let's close our table down.
  • 04:07 Yes, I want to save, and
  • 04:09 we're going to call this table related tickets and click on OK.
  • 04:15 So now if we double click to open this table, we don't have any data.
  • 04:19 And what I'm going to do is quickly import that text file so
  • 04:23 that we have some records.
  • 04:25 So external data, new data source from file, let's import the text file.
  • 04:31 Let's browse and search for our file.
  • 04:34 There it is, just there.
  • 04:35 Related tickets import.txt.
  • 04:39 I want to append these records to the table TBL related tickets.
  • 04:45 Let's click on OK.
  • 04:48 It's telling me my table is open and it needs to be closed.
  • 04:51 So let's click yes to close the table.
  • 04:54 Now we can run through our wizard.
  • 04:57 Let's click on next.
  • 04:59 I have a tab separating my fields and my first row contains field names.
  • 05:05 Let's click on next.
  • 05:06 We're importing to table TBL related tickets, click on finish.
  • 05:12 I'm not going to bother saving my import steps.
  • 05:15 Let's just click on close.
  • 05:17 And now, if we reopen to all related tickets,
  • 05:20 we can see all of those listed out there.
  • 05:23 So now we've imported our data we need to do one final thing before we create our
  • 05:28 subform.
  • 05:28 And that is, we need to link TBl related tickets to TBL ticket.
  • 05:34 And remember, interval related tickets we're using a composite primary key.
  • 05:39 That means the related ticket number and the ticket number combined together make
  • 05:43 up the unique reference point for this particular table.
  • 05:47 Now this is going to throw up a little bit of an issue when we try to link these
  • 05:51 tables together.
  • 05:52 So let's close this table down.
  • 05:54 Let's click yes to save the changes and let's jump into relationships.
  • 05:59 I'm going to add TBL related tickets to the grid.
  • 06:05 And now I'm going to link TBL ticket to TBL related tickets via
  • 06:09 the ticket number field.
  • 06:11 If I drag and drop this down,
  • 06:13 notice the relationship type this time is indeterminate.
  • 06:18 It's not one to many like it normally is.
  • 06:21 And if I try to enforce referential integrity and create this link, it's
  • 06:25 telling me that no unique index found for the reference field of the primary table.
  • 06:30 So it's telling me that the ticket number field,
  • 06:33 interval related tickets isn't unique.
  • 06:36 And that is correct because we have multiple related tickets for every ticket.
  • 06:41 So I need to make a little bit of a change here.
  • 06:43 So let's click on cancel.
  • 06:45 I'm going to reopen TBL ticket.
  • 06:48 Let's double click.
  • 06:49 And if we jump into design view and take a look at the properties of the ticket
  • 06:55 number field, I can see that I have indexed is set to yes duplicates, Okay.
  • 07:00 Now every ticket number in this table should be unique.
  • 07:04 So I'm going to double click that to, yes, we're going to index, but no duplicates.
  • 07:09 Let's click on close, save our changes and go back to our relationships.
  • 07:15 Now, if I drag ticket number down to ticket number, take a look at that.
  • 07:19 We now have a one to many relationship.
  • 07:22 I can enforce referential integrity.
  • 07:24 Click on create to create that link.
  • 07:27 Now that we've done that, it's time to move on to creating our subform which
  • 07:30 we're going to do in the next lesson

Lesson notes are only available for subscribers.

Gift this course