Quick reference

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