Subforms are one of those Access features that seem simple until the wrong customer's contacts start appearing on the screen. Then suddenly Mission Control has a problem. This expert-level quiz covers five important points about working with subforms, including how parent and child records are linked together.
Give yourself a point for each answer you get right. If you want to play along properly, read each question, make your choice, and then check the answer below. No peeking. Access knows.
Question 1: What is the main purpose of a subform in Microsoft Access?
A. Display related records inside another form
B. Store a backup copy of the main form's records
C. Print the current record as a report
D. Replace a table relationship
Answer: A. Display related records inside another form.
A subform is a form embedded inside a main form. It is commonly used when you have a one-to-many relationship and want to display the related records for whichever parent record is currently selected. For example, you might show a customer's contacts, an order's line items, or a student's class registrations right inside the main form.
A subform does not replace proper table relationships. It is simply a convenient interface for viewing and editing related data without making the user bounce between seventeen different forms like they are navigating a spaceship with a broken joystick.
Question 2: A user enters a new related record directly in an editable subform. What normally happens?
A. The entry exists only until the main form is closed
B. Access creates a new table for the entry
C. The related record is saved to the subform's underlying record source
D. The entry is copied into the parent record
Answer: C. The related record is saved to the subform's underlying record source.
An editable subform is a live view of its underlying table or query. Assuming additions are allowed and the record source is updateable, entering a new row in the subform creates a real record in that source. It is not temporary, and Access does not magically create a new table every time someone types a phone number.
When the main form and subform are linked correctly, Access will also fill in the child foreign key for you. So, if you are viewing Customer ID 42 and add a new contact in the subform, that contact is automatically associated with Customer ID 42.
Question 3: Which setup correctly links a CustomerF main form to a ContactF subform so each customer shows only that customer's contacts?
A. Link Master Fields: CustomerID; Link Child Fields: CustomerID
B. Link Master Fields: ContactID; Link Child Fields: CustomerID
C. Link Master Fields: CustomerName; Link Child Fields: ContactDate
D. Set both link properties to the subform's form name
Answer: A. Link Master Fields: CustomerID; Link Child Fields: CustomerID.
The Link Master Fields property identifies the field on the main form. The Link Child Fields property identifies the matching field in the subform's record source. Access compares those values and filters the subform so it displays only the records related to the current main-form record.
In a typical customer-and-contacts setup, CustomerID is the primary key in the customer table and the foreign key in the contacts table. The field names often match, but they do not have to. You could have CustomerID on the main form and ContactCustomerID in the subform's record source, as long as those are the fields that actually connect the records.
Question 4: You want to change the Link Master Fields and Link Child Fields settings. Which object should you select in Design View?
A. A text box inside the subform
B. The subform control's border on the main form
C. The main form's record selector
D. The subform's detail section only
Answer: B. The subform control's border on the main form.
This one catches a lot of people. A subform has two separate layers. There is the actual form object that displays the related records, and then there is the subform control sitting on the main form that contains it.
The subform itself has properties such as Record Source, Allow Additions, navigation buttons, and so on. The subform control has properties related to how that form behaves inside the main form, including Link Master Fields and Link Child Fields. To change those link settings, open the main form in Design View and select the border of the subform control, not a control inside the subform.
Question 5: A subform uses a custom query that displays ContactDate and Notes, but the query does not include CustomerID. You want to link it to CustomerF by CustomerID. What should you do?
A. Add CustomerID to the subform query's record source, even if it is hidden on the form
B. Change CustomerID in CustomerT to a Short Text field
C. Add CustomerID only to the main form's Record Source
D. Turn on cascade delete related records
Answer: A. Add CustomerID to the subform query's record source, even if it is hidden on the form.
The child link field has to be available in the subform's underlying record source. If your subform is based on a query, that query must include CustomerID so Access can compare it to the CustomerID from the main form. You do not have to put CustomerID in a visible text box for the user. It can remain hidden. Access just needs the field available behind the scenes.
This is a common issue with custom subform queries. Someone builds a nice clean query that shows only the fields they want users to see, then wonders why the link properties do not work. The fix is usually simple: include the foreign key in the query, even if it never appears on the form.
So, how did you do? Five out of five means you are ready to establish New Olympus and make Mars Earth's favorite neighbor. Three out of five means you probably got the rover home before the dust storm started. Zero out of five? Please do not unpack your suitcase outside the airlock.
If any of these questions gave you trouble, subforms and their link properties are covered in much more detail in my Microsoft Access Expert Level 3 class, especially Lesson 3. Watch the embedded video for the quiz walkthrough and demonstrations, then check out the full course when you are ready to get your related records under control.
Live long and prosper,
RR
No comments:
Post a Comment