|
|
A main/subform allows you to display a parent-child relationship between two record sources on a single form. The feature that makes the main/subform especially powerful is the way it links its two parts: the main form and the subform. The main/subform restricts the entries on the subform to just those records that match the current record on the main form.
Any pair of tables in a one-to-many relationship is a candidate for display via a main/subform. In relational database terms, the table on the one side of the relationship is the parent, and the table on the many side is the child. Access relies on the matching field values between the parent and child record sources to display just the child records that correspond to the current parent record.
The Access user interface offers at least two ways to create main/subforms. First, the AutoForm Wizard can automatically create a main/subform for any table that has a one-to-many relationship with another table. With this approach, you do not need a standalone form for the child record source. Access displays the Datasheet view of the child record source within the main form for the parent record source. Second, you can create separate forms for the child and parent record sources and then drag the form for the child record source from the Database window into the Design view of the form for the parent record source. As long as the parent and child record sources have a relationship defined in the Relationship window (or a subdatasheet relationship, which we'll discuss in a moment), Access will automatically match the child records to the parent records with related tables or tables with subdatasheet specifications. Otherwise, Access pops up a dialog box that prompts you for the matching fields in the parent and child record sources.
Subdatasheets provide a way to display parent-child relationships from a table's Datasheet view, regardless of whether a one-to-many relationship exists between record sources. Main/subforms interact with and build on Access subdatasheet technology. Figure 5-9 shows subdatasheets for the Order Details table within the Orders table. The screen shot expands the subdatasheets for orders 10248 and 10249. The other orders have their subdatasheet collapsed. Clicking the plus sign (+) next to an OrderID field with a collapsed subdatasheet expands the subdatasheet. To close an expanded subdatasheet, just click the minus sign (-) next to an OrderID field value.
| Note |
You can use the Format-Subdatasheet menu from the Datasheet view of a parent record source to manage the default display of subdatasheets. By default, Access collapses all subdatasheets. You can expand all subdatasheets by choosing Format-Subdatasheet-Expand All. You can restore the default display rule for subdatasheets by choosing Format-Subdatasheet-Collapse All. |
Access automatically builds subdatasheets for tables that share relationships in the Relationships window. If no prespecified relationship exists between two record sources, you can still create a subdatasheet for a child record source within its parent. Simply open the parent record source in Datasheet view and choose Insert-Subdatasheet from the menu. Then, choose the table or query from the current database that you want as a child record source. If the child record source has one or more fields with names that match those in the parent source, Access automatically suggests matching the two record sources on these fields. If no fields in the parent and child record sources have the same names, you can use the combo boxes at the bottom of the Insert Subdatasheet dialog box to specify the fields for matching child records to parent records. Use a semicolon delimiter to separate fields when the record sources contain more than one matching field.
| Note |
You can delete a subdatasheet by choosing the Format-Subdatasheet-Remove command from the Datasheet view menu for the parent record source. This does not delete the child resource—just its subdatasheet relationship to the parent record source. Although Access inherits subdatasheets for linked tables, you cannot remove a subdatasheet from a linked table in the source database. Instead, open the Access database file with the local table and remove the subdatasheet from there. |
When specifying record sources for main/subforms, you will often want to designate queries as record sources. This makes it easy to select columns and join tables to provide main or subform fields. After deciding on your sources for the forms, you can build the query graphically or programmatically. (See Chapter 4 for code samples that illustrate how to programmatically add queries to a database.)
My subform sample has two levels of subforms. The main form has a subform that, in turn, has a subform of its own. The sample relies on three SQL statements. The outer query has the name MyOrders; this query is the record source for the main form. The query nested within MyOrders has the name MyOrderDetails; this query is the record source for the subform on the main form. The lowest-level query is MyProducts; this is the record source for the subform of the subform on the main form. The SQL statement for the main form (frmMyOrders) follows.
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry FROM Orders
The SQL statement for the subform (frmMyOrderDetails) of the main form follows:
SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount FROM [Order Details]
The following is the SQL statement for the subform to the subform (frmMyProducts):
SELECT Products.ProductID, Products.UnitsInStock, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.Phone FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
These queries inherit the relationships between data sources in the Northwind database. Therefore, as you create the forms with the AutoForm Wizard, the wizard automatically builds in the subforms. Because these automatically generated subforms do not depend on any custom record source specifications in custom queries, you can delete the subform controls within forms in order to add subforms with custom RecordSource property values.
After preparing the individual main and subforms, you can start combining them. First, open frmMyOrderDetails in Design view and drag frmMyProducts from the Database window. After saving and closing the new frmOrderDetails form, open the frmMyOrders form in Design view. Then, drag the modified frmOrderDetails form from the Database window.
After completing the nesting layout issues, you still have a couple more matters to address. First, you change the DefaultView property setting for the frmMyProducts and frmMyOrderDetails forms to Datasheet view. This DefaultView adjustment enables the subforms to appear automatically as datasheets. Second, you assign appropriate Link Child Fields and Link Master Fields property settings for the subform control in the main form and the subform control in the subform of the main form. You can do this by opening the subform control's property sheet to the appropriate tab. Select the Data tab of the subform control's property sheet to display boxes for setting the Link Child Fields and Link Master Fields properties. Clicking the Build button to the right of either property displays a dialog box that helps you select fields from the record sources for the main and subforms.
In the example, you click the MyProducts subform control in the MyOrderDetails subform. Then, you designate the ProductID for the Link Child Fields and Link Master Fields settings. Next, you click the MyOrderDetails subform control on the frmMyOrders form and specify OrderID for matching the subform records with its main form.
Figure 5-10 shows the sample form containing a subform within a subform. The main form provides details from the MyOrders record source. Its subform shows details from the MyOrderDetails record source. Notice that all the records on the subform have an OrderID value that matches the one on the main form. Finally, the subdatasheet within the subform displays data from the MyProducts record source. Again, the ProductID value on the subdatasheet corresponds to the subform Product field. However, recall that the ProductID field in the original Northwind Order Details table is a lookup field that shows the Product name instead of the ProductID value, and the ProductID field has a caption that shows Product instead of ProductID for a control based on the field.
When programmatically referencing subform controls, you need to keep a couple of issues in mind. First, the subform is a control on the main form. This means you cannot refer to the subform controls directly. Instead, you must reference the Form property of the subform control. Then, you can reference the subform controls in the standard way. (VBA code can process the controls on forms. The introduction to this chapter concludes with a URL reference containing code samples that demonstrate basic VBA techniques for working with forms.) Second, when dealing with subforms with multiple levels of nesting, you can selectively choose to deal with only the levels your application requires. Therefore, if you have a set of forms with three levels (main form, subform within main, and subform within subform on main form), you can deal with just the top two levels if that's all your application requires.
The following sample demonstrates the syntax for processing the main/subform shown in Figure 5-10. Although the form includes two levels of subforms, this sample processes just the main form and its subform. (The SyntaxForSubForms procedure to follow deals with the three levels of forms simultaneously.) The sample begins by demonstrating a couple of syntax conventions for referencing subform control values. Next, it contrasts conventions that expose the record sources of the main form and the subform. Then, the code illustrates how to count the total number of controls on the main and subforms. The sample concludes by enumerating the first 10 records on the main form and printing the subform records for each.
The procedure in this code sample starts by opening the frmMyOrders form, the main form in the example. After opening this form so that it becomes a member of the Forms collection, the sample sets a form object reference, frm1, to the frmMyOrders form. Then, it sets a control reference, ctl1, to the main form's subform control. This control has the name MyOrderDetails—the same name as the record source behind the subform. The next two lines of code demonstrate two different programming styles for referencing the OrderID text box control on the subform. The style that explicitly invokes the Form property of the subform control is very robust; you will often need to reference subform objects and properties with this syntax. In addition, the syntax makes it clear that the subform is a control on the main form that has a Form property.
The next two blocks of code demonstrate the similarity between referencing properties and collections for the main and subforms. The RecordSource property represents a SQL string or an object name representing the data behind the form. Because queries exist for both the main and subforms, the references to their record sources return the query names. Notice that ctl1.Form represents a form (the subform) in the same way that frm1 represents the main form. A form's Controls collection contains all the controls on a form. In addition to text boxes and combo boxes, controls can include labels, lines, and subform controls. The Count property of the Controls collections of the main and subforms returns the count of all the controls—not just those that display data.
Perhaps the most interesting segment of the sample is the portion that contains three nested loops. The outer loop passes through the first 10 records on the main form. The middle loop iterates through the subform records for the current main form record. The inner loop searches the subform controls for either Text Box or Combo Box controls. When the inner loop finds either type of control, the sample prints a short message stating the name of the control and its current value.
The syntax in these three loops reinforces some familiar concepts and introduces some new ones. Notice that the Form property of the subform control has a hierarchically nested Recordset object. The procedure uses the RecordCount property of this object to loop as many times as there are subform records for the current main form record. The procedure also invokes the MoveNext method of the subform's Recordset object to navigate from the first subform record through the last. Within a subform record, the procedure iterates through the Controls collection of the subform. The TypeOf function ascertains whether the current control is either a text box or a combo box. Let's take a look at the syntax:
Sub SyntaxForSubForms()
Dim frm1 As Form
Dim ctl1 As Control
Dim ctl2 As Control
Dim int1 As Integer
Dim int2 As Integer
'Open a main/subform
DoCmd.OpenForm "frmMyOrders"
'Assign pointers for main form and sub
'form control; MyOrderDetails is the name of the
'subform control on the frmMyOrders main form
Set frm1 = Forms("frmMyOrders")
Set ctl1 = frm1.MyOrderDetails
'Two different ways to print the OrderID control value
'on the subform
Debug.Print ctl1!OrderID
Debug.Print ctl1.Form.Controls("OrderID")
Debug.Print
'Print the record source settings for the main form
'and the subform
Debug.Print frm1.RecordSource
Debug.Print ctl1.Form.RecordSource
Debug.Print
'Print the number of controls on the main and subforms
Debug.Print frm1.Controls.Count & _
" controls are on the main form."
Debug.Print ctl1.Form.Controls.Count & _
" controls are on the subform."
Debug.Print
'Move to the form's first record, and loop through the
'next 10 main form records and the subform records
'corresponding to each main record; within each subform
'record, loop through the controls on the subform
DoCmd.GoToRecord , , acFirst
For int1 = 1 To 10
Debug.Print vbCrLf & "Data for record " & int1 & "."
For int2 = 0 To ctl1.Form.Recordset.RecordCount - 1
For Each ctl2 In ctl1.Form.Controls
If TypeOf ctl2 Is TextBox Then
Debug.Print String(5, " ") & ctl2.Name & _
" is a text box that equals " & _
ctl2.Value & "."
ElseIf TypeOf ctl2 Is ComboBox Then
Debug.Print String(5, " ") & ctl2.Name & _
" is a combo box that equals " & _
ctl2.Value & "."
End If
Next ctl2
ctl1.Form.Recordset.MoveNext
Debug.Print String(5, "-")
Next int2
DoCmd.GoToRecord , , acNext
Next int1
DoCmd.Close acForm, "frmMyOrders"
'Clean up objects
Set ctl1 = Nothing
Set frm1 = Nothing
End Sub
This next sample builds on the previous one by adding another form level. Happily, adding another level of subform does not substantially change how you programmatically access controls. This section demonstrates how to access the subdatasheet of a subform on a main form. You still have to refer to the Form property of the subform control on the main form. In addition, you need to use this same referencing scheme for the subform control on the subform. This second reference provides a path to subdatasheet values.
There is another critical trick for accessing the control values in a subdatasheet for a subform: the subdatasheet must be open. You do this by setting the main form's subform SubdatasheetExpanded property to True. Without this step, references to subdatasheet control values can generate a run-time error. The sample checks to see if the SubdatasheetExpanded property is False. If it is, then the code sets the property to True and invokes the DoEvents function. The execution of the function allows the operating system to respond to the new setting for the SubdatasheetExpanded property.
In this sample, at the subdatasheet level the code merely prints the values of the subdatasheet row. The subdatasheet row specifies the additional detail, such as on-hand inventory or supplier name and contact information for the product in an individual line item of an order. See Figure 5-10 for a sample of the data that is available in the subdatasheet.
The code sample begins by opening the frmMyOrders form and creating three object references, which simplify the expressions throughout the procedure. The first object reference, frm1, points at the main form. The second reference, ctl1, points at the subform control on the main form, and the third reference, ctl3, points at the subdatasheet on the subform.
The next two blocks of code print the RecordSource property settings for the main form, its subform, and the subdatasheet on the subform. Notice that the code sets the SubdatasheetExpanded property to True before attempting to reference the subdatasheet. Next, the procedure illustrates the syntax for counting the controls on the main form and the two subforms nested successively below it.
The next block repeats the code for enumerating the subform control values for each record on the main form. In addition, this sample accesses the control values on the subdatasheet. This provides the additional product detail described a moment ago.
The loop for the subform nested within a subform is less complicated than the loop for a subform on the main form. There are several reasons for this. Only one subdatasheet row exists per subform record. All the controls with data on the nested subform are text boxes. The program follows:
Sub SyntaxForSubDatasheetOnSubForm() Dim frm1 As Form Dim ctl1 As Control Dim ctl2 As Control Dim ctl3 As Control Dim ctl4 As Control Dim int1 As Integer Dim int2 As Integer 'Open a main/subform DoCmd.OpenForm "frmMyOrders" 'Assign pointers for main form and sub 'form control Set frm1 = Forms("frmMyOrders") Set ctl1 = frm1.MyOrderDetails Set ctl3 = ctl1.Form.MyProducts 'Print the record source settings for 'the main form, the subform, and the 'expanded subdatasheet of the subform Debug.Print frm1.RecordSource Debug.Print ctl1.Form.RecordSource If ctl1.Form.SubdatasheetExpanded = False Then ctl1.Form.SubdatasheetExpanded = True DoEvents End If Debug.Print ctl3.Form.RecordSource Debug.Print 'Print the number of controls on the main and subforms Debug.Print frm1.Controls.Count & _ " controls are on the main form." Debug.Print ctl1.Form.Controls.Count & _ " controls are on the subform." Debug.Print ctl3.Form.Controls.Count & _ " controls are on the subdatasheet." 'Move to the form's first record, and loop through the 'next 5 main form records and the subform records 'corresponding to each main record; within each subform 'record, loop through the controls on the subform DoCmd.GoToRecord , , acFirst For int1 = 1 To 5 Debug.Print vbCrLf & "Data for record " & int1 & "." For int2 = 0 To ctl1.Form.Recordset.RecordCount - 1 For Each ctl2 In ctl1.Form.Controls If TypeOf ctl2 Is TextBox Then Debug.Print String(5, " ") & ctl2.Name & _ " is a text box that equals " & _ ctl2.Value & "." ElseIf TypeOf ctl2 Is ComboBox Then Debug.Print String(5, " ") & ctl2.Name & _ " is a combo box that equals " & _ ctl2.Value & "." End If Next ctl2 'Loop through the controls on the subdatasheet 'returning just text boxes and their values For Each ctl4 In ctl3.Form.Controls If TypeOf ctl4 Is TextBox Then Debug.Print String(10, " ") & ctl4.Name & _ " is a text box that equals " & _ ctl4.Value & "." End If Next ctl4 ctl1.Form.Recordset.MoveNext Debug.Print String(5, "-") Next int2 DoCmd.GoToRecord , , acNext Next int1 DoCmd.Close acForm, "frmMyOrders" 'Clean up objects Set ctl1 = Nothing Set ctl3 = Nothing Set frm1 = Nothing End Sub
Figure 5-11 shows an excerpt from the output of this program. Notice that the output starts by listing the query names for the forms. Next, it reports a count of the controls on each form. The last information from the Immediate window displays the subform record values along with the subdatasheet values for that subform record indented to the right. The sample shows the data for two order line items that correspond to the first record in the Orders table.
With a good grasp of the syntax for working with controls on subforms and inserting records into forms, you can readily insert records into subforms based on ADO recordsets. The code sample in this section demonstrates one way to do this.
The sample's first task is to create a recordset for the main form. The SQL string for the rst1 recordset extracts records from the Orders table if their OrderDate field value is in 1998.
I continue using the frmMyOrders main/subform. The sample opens this form and assigns the rst1 recordset to the form's Recordset property. The sample assigns adUseClient to the recordset's CursorLocation property to facilitate data entry via the form. The block of code opening the sample also includes an object reference assignment for ctl1, which represents the subform control on frmMyOrders, namely the frmMyOrderDetails subform.
Before creating a new record, the sample saves the OrderID value for the main form. This OrderID value is the first order in 1998. (The OrderID value happens to be 10808.) The sample saves this value in int1. Then, the procedure invokes the DoCmd object's GoToRecord method with an acNewRec argument. This creates a blank form for data entry. Next, the sample populates the controls in the subform inside a loop. Notice that the sample saves the ProductID field value in int2. After passing through all the subform controls, the procedure closes the form. This action commits the new record to the form's record source.
The remainder of the sample removes the newly added record. This sample merely serves to demonstrate adding a record through the form—we do not actually want to change the standard Northwind table. The procedure invokes the Seek method to find those records from the Order Details table that match the value in int1 (10808). It loops through these records to locate the one with the ProductID value for the newly added record. When the code finds the newly added record, it invokes the Delete method to remove the record.
Sub BindRstToSimpleMainSubFormRWInsert() Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Dim frm1 As Form Dim ctl1 As Control Dim ctl2 As Control Dim int1 As Long Dim int2 As Long 'Create the connection Set cnn1 = New ADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\" & _ "Office11\Samples\Northwind.mdb;" 'Create recordset reference and set its properties 'to permit read/write access to recordset Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenKeyset rst1.LockType = adLockOptimistic rst1.CursorLocation = adUseClient 'Open recordset based on orders in 1998 for 'frmSimpleUnboundForm rst1.Open "SELECT * FROM Orders o " & _ "WHERE Year(o.OrderDate) = 1998", cnn1 'Open a main/subform, assign pointers for main 'form and subform control, and assign recordset 'to the main form's Recordset property DoCmd.OpenForm "frmMyOrders" Set frm1 = Forms("frmMyOrders") Set ctl1 = frm1.MyOrderDetails Set frm1.Recordset = rst1 'Add a new record through the subform to the current OrderID 'value on the main form int1 = frm1.OrderID DoCmd.GoToRecord , , acNewRec For Each ctl2 In ctl1.Form.Controls If TypeOf ctl2 Is TextBox Or TypeOf ctl2 Is ComboBox Then Select Case ctl2.Name Case "OrderID" ctl2.Value = int1 Case "ProductID" ctl2.Value = 1 int2 = ctl2.Value Case "Quantity" ctl2.Value = 1 Case "UnitPrice" ctl2.Value = 1 Case "Discount" ctl2.Value = 0 Case Else MsgBox "Error on Insert." Exit Sub End Select End If Next ctl2 DoCmd.Close acForm, frm1.Name 'Create recordset reference and set its properties so that 'you can seek the records for the OrderID to which you 'added a record Set rst1 = New ADODB.Recordset rst1.Index = "OrderID" rst1.CursorType = adOpenKeyset rst1.LockType = adLockOptimistic rst1.Open "Order Details", cnn1, , , adCmdTableDirect rst1.Seek int1 'Loop through line items for OrderID to which you 'previously added a record; delete the record 'when you find it Do Until rst1(0) <> int1 If rst1(1) = int2 Then 'Delete the record because we do not want to change 'the Northwind database, but only show that we can rst1.Delete Exit Do End If rst1.MoveNext Loop 'Clean up objects rst1.Close cnn1.Close Set frm1 = Nothing Set rst1 = Nothing Set cnn1 = Nothing End Sub
A typical application requirement is the display of some aggregate of subform control values on a main form. For example, many applications might include a form like frmMyOrders and require a total extended price on the main form. This aggregate value derives from the extended price of the individual line items for an order, which are based on values in the subform—namely, the product of Quantity and UnitPrice times one minus Discount. The sample in this section demonstrates how to display total extended price on a variation of frmMyOrders.
I began constructing the sample by copying frmMyOrders and frmMyOrderDetails to new forms named frmMyOrdersSubtotal and frmMyOrderDetailsSubtotal. Next, in Design view I added a text box and its matching label to frmMyOrdersSubtotal. Then, I added a pair of event procedures to the code behind frmMyOrdersSubtotal. The main event procedure for frmMyOrdersSubtotal was Form_Current. This event fires whenever a new record becomes current. The Current event also fires when your application requeries or refreshes a form.
The code inside the Form_Current event procedure iterates through the records in the subform that match the current record in the main form. The ctl1 variable points at the subform control on the main form, frmMyOrdersSubtotal. Although the name of the subform is frmMyOrderDetailsSubtotal, the subform control still has the name MyOrderDetails because the subform is a copy of frmMyOrderDetails. For each record in the subform, the procedure computes the extended price. The procedure uses the Form property of the subform control to access the Quantity, UnitPrice, and Discount control values for each record. The For…Next loop that passes through the subform records also aggregates the extended price across the records. After exiting the loop, the procedure assigns the aggregated value to a Text Box control (txtTotalExtendedPrice) on the main form.
A Form_Load event procedure participates in the solution. This procedure formats the value in the text box on the main form so that it shows its value right aligned. Text Box controls left align their contents by default. An assignment to the TextAlign property for the txtTotalExtendedPrice control achieves this result. It is necessary to make this assignment just once when the form opens. Both event procedures appear next.
Private Sub Form_Open(Cancel As Integer) Const conRightAlign = 3 'Right-align value in text box Me.txtTotalExtendedPrice.TextAlign = conRightAlign End Sub Private Sub Form_Current() Dim ctl1 As Control Dim ctl2 As Control Dim dbl1 As Double 'Assign a pointer for the subform control Set ctl1 = Me.MyOrderDetails 'Iterate through the records on the subform to 'acc
