Time for a quick Access quiz. These five questions cover a few important details that can trip up even experienced users: form events, parent-child records, subforms, control references, and the always-fun problem of dates that secretly include times.
Give yourself a few seconds to answer each one before reading the answer. Keep score if you like. No fair blaming the database when it gets grumpy about referential integrity.
Question 1: When does a form's Before Insert event occur?
A. After the record has been permanently deleted
B. Just before Access inserts a new record
C. Only when an existing record is edited
D. When the database needs a coffee break
The answer is B: just before Access inserts a new record. The Before Insert event is a useful place to check conditions that must be true before a new record is created. It gives you an opportunity to stop or handle a problem before Access writes that new record to the table.
Question 2: Why can Access reject a new order-detail record when its parent order has not been saved yet?
A. Detail records must always be entered directly in a table
B. A subform cannot contain combo boxes until the parent form closes
C. The child record needs a valid parent key to satisfy referential integrity
D. Access requires all orders to contain exactly one product
The answer is C. A detail record is on the many side of the relationship. It needs an OrderID, or whatever foreign key you are using, that points to a real parent order record. If the parent has not been saved yet, it may not have its AutoNumber ID assigned. Without that valid parent key, Access cannot create the relationship, especially when referential integrity is enforced.
Question 3: A new parent form has default values but no saved record yet. What can make Access create and save that parent record before a child record is added?
A. Change a bound field on the parent record and save or refresh that record
B. Hide the parent form and reopen the child subform
C. Add the child record first and let Access calculate the parent ID
D. Change the parent table's primary key into Short Text
The answer is A. Defaults can appear on a new form record without an actual record having been created in the table. That catches people all the time. If you change a bound field on the parent form and then save the record, or otherwise force Access to save it, Access creates the parent record and assigns its AutoNumber key if appropriate. The subform can then use that key for its child records.
Question 4: A macro is running from a subform but needs to use a control on the parent form. What kind of reference is normally required?
A. Just the control name, because all form controls are global
B. A full reference such as [Forms]![ParentF]![ControlName]
C. The table name followed by an exclamation point
D. The control's Caption property in quotation marks
The answer is B. From a subform, you generally need a full reference to reach a control on another form, such as [Forms]![ParentF]![ControlName]. There are other valid ways to do it depending on where the macro or expression is running. For example, Parent!ControlName can be appropriate from inside a subform. But among these choices, the fully qualified Forms reference is the correct one.
Question 5: An OrderDate field stores both dates and times. What criteria most reliably finds all orders placed on one particular date?
A. OrderDate = the requested date
B. OrderDate Like the requested date
C. OrderDate > the requested date
D. OrderDate >= the requested date And OrderDate < the next day
The answer is D. This is the reliable way to find every record from a particular calendar day when the field also contains a time value. An equality test only finds records stored at exactly midnight. An order placed at 2:37 PM is not equal to the same date at 12:00:00 AM, even though Access may display both as the same date depending on the format.
The correct approach is to search for values that are greater than or equal to the start of the requested date and less than the start of the following date. In other words, include everything from midnight on the requested day up to, but not including, midnight tomorrow. This avoids missing records with times and avoids the messy temptation to use an end-of-day value such as 11:59:59 PM.
How did you do? Five out of five means you have successfully named a new species and survived its welcome hug. If you missed a few, no worries. These are exactly the kinds of little Access details that become important once you start building real forms with related data.
The embedded video has the full quiz presentation, and these topics are covered in more depth in Access Advanced Level 2, Lesson 2.
Live long and prosper,
RR
No comments:
Post a Comment