Locked lesson.
About this lesson
Add a subform to a main form and store when a record was changed in a Date/Time field.
Exercise files
Download this lesson’s related exercise files.
Services_19_Start_MainformSubform.accdb1.4 MB Services_19_MainformSubform.accdb
1.7 MB
Quick reference
Mainform and Subform
Application Terminology
Bound Control
A Bound control has a Control Source that specifies a field in the form Record Source.
Bound Form
A Bound Form has a Record Source that specified a connection to a data source such as a table or query. Bound Forms are used to enter, edit, and display data.
Calculated Control
A Calculated Control contains an expression and starts with an equal sign (=). The values in calculated controls cannot be changed by the user.
Calculations can also be added to the Record Source and the calculated fieldname can be specified in the Control Source. Again, however, the user cannot change the value.
Control
A Control is an area on a form or report that contains something.
Forms
Forms give you an easy way to enter information in the database with control to validate information, show calculations, take actions based on what is entered, and a lot more.
It is best to use Forms for entering information as opposed to entering information directly into tables. Like tables, add new records to the end of the records listed in a form.
Forms can also have subforms which are synchronized with LinkMasterFields and LinkChildFields so that values referenced by LinkMasterFields are automatically created in LinklChildFields as new records are made.
LinkChildFields
LinkChildFields are names of fields contained on a subform. The field(s) should also be in a control(s). The Visible property of the control(s) can be No, but they should be ON the subform.
LinkMasterFields
LinkMasterFields and LinkChildFields are properties of a subform control. They specify how Access links records in a main form to records in a subform.
LinkMasterFields actually refers to a control name, not a field name. This enables you to synchronize a subform with unbound controls on a main form.
More than one control can be specified. The number of items in LinkMasterFields must match the number of items in LinkChildFields. Separate multiple values with ; (semi-colon).
Access will automatically set LinkMasterFields and LinkChildFields if there is a relationship between the tables that the forms are based on and the fields are in each respective Record Source.
LinkMasterFields and LinkChildFields are listed on the Data tab of the Property Sheet when the subform control is selected.
Record Source
The Record Source of a form specifies a Table, Query, or SQL statement
Source Object
Source Object is the property of a subform control that specifies the form name to be used for the source of the subform. Source Object is listed on the Data tab of the Property Sheet when the subform control is selected.
Tab Stop
Tab Stop is a property of a bound control that determines if this control will be a place where the user will stop when they press tab
Unbound Control
An Unbound control does not contains a field name or expression in the Control Source. One use of an unbound controls is to give the user a way to specify something to search for.
Unbound Form
An Unbound Form does not have a Record Source specified and is not connected to a data source. Unbound Forms can be used for menus and dashboards.
Visible
Visible is a property of controls and sections that is Yes or No and determines if the object will be seen when a form or report is not in Design View.
Steps
Add a Subform by Dragging
- To add a subform to a main form, go into design view of the main form
- Drag a form to be used as a subform from the Navigation Pane.
- If there is not a relationship, set the LinkMasterFields and LinkChildFields properties if you wish to synchronize the main form and subform.
- Set the Name property to be the same as the Source Object property.
If you are using the same subform multiple times, add something to the end of the name to make it different.
Add a Subform by Creating a Subform Control
- Drop the list of Controls in the Controls group of the DESIGN RIBBON
- Choose the Subform/Subreport Control
- Set the Source Object property.
- If there is not a relationship, set the LinkMasterFields and LinkChildFields properties if you wish to synchronize the main form and subform.
- Set the Name property to be the same as the Source Object property.
If you are using the same subform multiple times, add something to the end of the name to make it different.
Change the Size of a Subform
- Click ONE time on the subform control
- To best-fit, double-click one of the resizing handles
- If the subform is a continuous form or a datasheet, you may want to drag the bottom or top handle to change the Height.
You can also set the size on the Property Sheet (HEIGHT, WIDTH)
Store When a Record was Changed in a Date/Time Field
- Click where the rulers intersect to select the form.
- On the Event tab, click in the form Before Update event
- Choose [Event Procedure] from the dropdown list.
- Click on the Builder button
(...) to the right
or pressto go to VBA
- Press the
(TAB) key to indent your line and type:
Me.dtmEdit = Now()
WHERE
dtmEdit is the name of your tracking field to store when the record was edited. - Choose Debug, Compile from the menu.
- Save
- 00:05 Forms give you an easy way to enter information
- 00:08 into the database, with control to validate information,
- 00:12 show calculations, take actions
- 00:14 based on what is entered, and a lot more.
- 00:17 Hi, this is Crystal.
- 00:19 To make is easier to focus on forms,
- 00:22 collapse the Tables and Queries groups in the Navigation Pane
- 00:26 by clicking their shutter buttons.
- 00:29 Back in lesson 3, we created a form for Services
- 00:32 called frm_Services_sub.
- 00:37 When you open that form, you see that a combo box
- 00:40 was used to specify the service type.
- 00:43 It would be more convenient to use this as a subform.
- 00:48 Like tables, new records are added to the end of forms.
- 00:52 Notice that new records do not have a service type filled.
- 00:56 There is a form called frm_SERVICE_TYPES.
- 01:02 At the top of the form is a combo box
- 01:04 to find a particular service type.
- 01:07 There is space under the service type information
- 01:09 for a subform.
- 01:11 Right-click on the title bar and
- 01:13 choose Design View from the shortcut menu.
- 01:16 If you are maximized, choose Design View from the ribbon.
- 01:21 To add a subform, simply drag it from the Navigation Pane
- 01:25 onto the design surface.
- 01:27 On the Data tab of the Property Sheet, notice
- 01:30 the SourceObject property is the name of the form
- 01:33 as displayed in the Navigation Pane.
- 01:36 Because there is a relationship between
- 01:39 the two tables that the forms are based on,
- 01:42 LinkMasterFields and LinkChildFields contain ServTyID,
- 01:46 which is the field that relates these tables with each other.
- 01:50 Resize the subform to be taller and delete the associated label.
- 01:55 Switch to Form View
- 01:57 On new records, you will see that service type
- 01:59 is automatically filled with the service type of the mainform.
- 02:04 This happens because of LinkMasterFields and LinkChildFields.
- 02:09 LinkMasterFields references control names in the mainform,
- 02:13 not fieldnames.
- 02:15 This gives you the ability to link subforms to unbound controls.
- 02:20 Separate multiple values with semi-colon ;
- 02:24 Some naming conventions suggest naming controls different
- 02:27 than what is in them,
- 02:29 like txtServTyID instead of the fieldname.
- 02:33 I find this confusing,
- 02:35 especially when I change control types.
- 02:38 With bound controls,
- 02:39 it is easier to make the Name property
- 02:42 the same as the ControlSource property,
- 02:45 and let Access sort it out.
- 02:47 That way, fieldnames can be referenced
- 02:49 because they are the same as control names.
- 02:52 In all my years of developing,
- 02:54 I have never come across any situation
- 02:56 that required control names to be different than fieldnames.
- 03:00 Why makes things difficult?
- 03:02 Make sure that whatever you reference with link fields
- 03:05 is actually ON the form.
- 03:08 It can be hidden but has to be there.
- 03:10 To hide a control, set the Visible property to No
- 03:14 on the Format tab of the Property Sheet.
- 03:17 Now that services is a subform of service types,
- 03:20 the service type does not need to show.
- 03:23 It also does not need to be a combobox that users can see,
- 03:27 unless you want to give them the ability to change it.
- 03:30 This may be a good idea.
- 03:32 So, rather than hiding it, move it all the way to the right.
- 03:35 Turn Visible back to Yes.
- 03:38 Select the control and the label, and drag them to the end.
- 03:41 Because the subform uses a layout,
- 03:44 Access will rearrange everthing else.
- 03:47 Now, as you add new records,
- 03:49 the service type will be automatically filled.
- 03:53 Set TabStop to No on the Other tab of the Property Sheet
- 03:57 to keep users from tabbing to it and inadvertently changing it.
- 04:01 It can still be changed by clicking in the control
- 04:04 and choosing something else.
- 04:06 Changing one of the Electrial services to Plumbing
- 04:09 causes it to be displayed when you are looking
- 04:11 at the Plumbing service type in the mainform.
- 04:14 That is not right, though, so let's change it back.
- 04:18 Working with subform controls can be a bit confusing.
- 04:21 Let's do some exploring.
- 04:23 Subform controls are containers that hold another form.
- 04:28 The first click on a subform control
- 04:30 puts handles around the subform control.
- 04:33 Look at the Property Sheet.
- 04:35 On the Data tab, you see the SourceObject name,
- 04:38 LinkMasterFields, and LinkChildFields.
- 04:42 The Format tab shows where the subform control is
- 04:45 and how big it is.
- 04:47 The most important Format properties are
- 04:49 Visible, Width, Height, Top, and Left.
- 04:53 The Event tab enables you to specify what will happen
- 04:56 when you Enter the subform and when you Exit.
- 05:00 The Other tab lets you rename the subform control, and
- 05:03 specify if the subform will be a Tab Stop
- 05:06 when the user presses the TAB key,
- 05:09 You can also set StatusBarText, which displays
- 05:12 in the lower left corner of the screen when you are in a control
- 05:15 that doesn't have its own StatusBarText.
- 05:18 The Tag property is not used by Access.
- 05:22 Tag gives developers a place to put whatever they want.
- 05:26 The second Click on a subform (control) gets you
- 05:28 INTO the subform that is contained by the subform control.
- 05:33 Then when you click on the contained form,
- 05:35 you will see a black square, where the rulers intersect,
- 05:38 in the upper left of the "form" you are "in".
- 05:41 The form that is contained by the subform control
- 05:44 is an independent form that you can open directly
- 05:47 from the Navigation Pane, just as you can with any other form.
- 05:51 It is referred to as a "subform"
- 05:53 because of the way it is being used.
- 05:56 Even though you can edit subforms within the mainform,
- 05:59 It is a good idea to edit forms used as subforms directly,
- 06:04 Close the mainform and go into the design view of the subform.
- 06:08 Turn on the Property Sheet if it is not showing
- 06:10 by right-clicking in the design space
- 06:13 and choosing Properties from the shortcut menu.
- 06:16 As you select controls and sections,
- 06:18 the Property Sheet shows more information about the selection.
- 06:23 The top of the Property Sheet shows what is selected.
- 06:26 Let's add an Event so that when the record is updated,
- 06:29 the date and time is stored in the dtmEdit field.
- 06:33 Click where the rulers intersect to select the form.
- 06:36 On the Event tab, click in the form Before Update event
- 06:40 and choose [Event Procedure] from the dropdown list.
- 06:44 Now click the Builder button to the right, …
- 06:48 This takes you into VBA, Visual Basic for Applications.
- 06:52 Press the TAB key to indent your line and type me.dtmE --
- 06:58 as you type, intellisense pops up and prompts you with choices.
- 07:03 When dtmEdit is selected, press the TAB key to choose it.
- 07:08 Then type: =Now()
- 07:12 Whenever you write code, you should compile.
- 07:15 Choose Debug, Compile from the menu.
- 07:18 If you didn't make any mistakes, nothing will happen.
- 07:22 If you did make a mistake, the line with
- 07:24 the mistake will be highlighted and you can correct it.
- 07:28 I did make a mistake but the compiler doesn't catch it.
- 07:32 I Add: Option Explicit to the top of the code
- 07:35 and Debug, Compile again.
- 07:37 Now the compiler catches my mistake and I can fix it.
- 07:41 Save and switch to form view.
- 07:44 After information is saved in the subform,
- 07:47 dtmEdit now shows the current date and time.
- 07:51 Forms and subforms should be patterned after data structure.
- 07:56 Each form and subform should be based on just one table.
- 08:01 This is a guideline that helps
- 08:03 when you are beginning to use Access.
- 08:05 As you get better, you can bend this rule.
- 08:08 In this lesson, you have learned
- 08:10 about mainforms and subforms.
- 08:13 In the next lesson, we will create a report.
Lesson notes are only available for subscribers.