Processing MainSubforms

Processing Main/Subforms

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

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.

Figure 5.9: Subdatasheets for the Order Details table within the Orders table.

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.

Creating Main/Subforms

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 .

 SELECTOrders.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 FROMOrders 

The SQL statement for the subform ( frmMyOrderDetails ) of the main form follows:

 SELECT[OrderDetails].OrderID,[OrderDetails].ProductID, [OrderDetails].UnitPrice,[OrderDetails].Quantity, [OrderDetails].Discount FROM[OrderDetails] 

The following is the SQL statement for the subform to the subform ( frmMyProducts ):

 SELECTProducts.ProductID,Products.UnitsInStock, Suppliers.CompanyName,Suppliers.ContactName, Suppliers.Phone FROMSuppliersINNERJOINProducts ONSuppliers.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.

Figure 5.10: A main/subform that has a subform within a subform that appears as a subdatasheet.

Programmatically Referencing Main and Subform Controls

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:

 SubSyntaxForSubForms() Dimfrm1AsForm Dimctl1AsControl Dimctl2AsControl Dimint1AsInteger Dimint2AsInteger     'Openamain/subform DoCmd.OpenForm "frmMyOrders"     'Assignpointersformainformandsub 'formcontrol;MyOrderDetailsisthenameofthe 'subformcontrolonthefrmMyOrdersmainform Setfrm1=Forms("frmMyOrders") Setctl1=frm1.MyOrderDetails     'TwodifferentwaystoprinttheOrderIDcontrolvalue 'onthesubform Debug.Printctl1!OrderID Debug.Printctl1.Form.Controls("OrderID") Debug.Print     'Printtherecordsourcesettingsforthemainform 'andthesubform Debug.Printfrm1.RecordSource Debug.Printctl1.Form.RecordSource Debug.Print     'Printthenumberofcontrolsonthemainandsubforms Debug.Printfrm1.Controls.Count&_  " controlsareonthemainform." Debug.Printctl1.Form.Controls.Count&_  " controlsareonthesubform." Debug.Print     'Movetotheform'sfirstrecord,andloopthroughthe 'next10mainformrecordsandthesubformrecords 'correspondingtoeachmainrecord;withineachsubform 'record,loopthroughthecontrolsonthesubform DoCmd.GoToRecord,,acFirst Forint1=1To10 Debug.PrintvbCrLf& "Dataforrecord " &int1& "." Forint2=0Toctl1.Form.Recordset.RecordCount-1 ForEachctl2Inctl1.Form.Controls IfTypeOfctl2IsTextBoxThen Debug.PrintString(5, " ")&ctl2.Name&_  " isatextboxthatequals " &_ ctl2.Value& "." ElseIfTypeOfctl2IsComboBoxThen Debug.PrintString(5, " ")&ctl2.Name&_  " isacomboboxthatequals " &_ ctl2.Value& "." EndIf Nextctl2 ctl1.Form.Recordset.MoveNext Debug.PrintString(5, "-") Nextint2 DoCmd.GoToRecord,,acNext Nextint1 DoCmd.CloseacForm, "frmMyOrders"     'Cleanupobjects Setctl1=Nothing Setfrm1=Nothing     EndSub 

Programming a Subform's Subdatasheet Controls

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:

 SubSyntaxForSubDatasheetOnSubForm()  Dimfrm1AsForm Dimctl1AsControl Dimctl2AsControl Dimctl3AsControl Dimctl4AsControl Dimint1AsInteger Dimint2AsInteger     'Openamain/subform DoCmd.OpenForm "frmMyOrders"     'Assignpointersformainformandsub 'formcontrol Setfrm1=Forms("frmMyOrders") Setctl1=frm1.MyOrderDetails Setctl3=ctl1.Form.MyProducts     'Printtherecordsourcesettingsfor 'themainform,thesubform,andthe 'expandedsubdatasheetofthesubform Debug.Printfrm1.RecordSource Debug.Printctl1.Form.RecordSource Ifctl1.Form.SubdatasheetExpanded=FalseThen ctl1.Form.SubdatasheetExpanded=True DoEvents EndIf Debug.Printctl3.Form.RecordSource     Debug.Print     'Printthenumberofcontrolsonthemainandsubforms Debug.Printfrm1.Controls.Count&_  " controlsareonthemainform." Debug.Printctl1.Form.Controls.Count&_  " controlsareonthesubform." Debug.Printctl3.Form.Controls.Count&_  " controlsareonthesubdatasheet."     'Movetotheform'sfirstrecord,andloopthroughthe 'next5mainformrecordsandthesubformrecords 'correspondingtoeachmainrecord;withineachsubform 'record,loopthroughthecontrolsonthesubform DoCmd.GoToRecord,,acFirst Forint1=1To5 Debug.PrintvbCrLf& "Dataforrecord " &int1& "." Forint2=0Toctl1.Form.Recordset.RecordCount-1 ForEachctl2Inctl1.Form.Controls IfTypeOfctl2IsTextBoxThen Debug.PrintString(5, " ")&ctl2.Name&_  " isatextboxthatequals " &_ ctl2.Value& "." ElseIfTypeOfctl2IsComboBoxThen Debug.PrintString(5, " ")&ctl2.Name&_  " isacomboboxthatequals " &_ ctl2.Value& "." EndIf Nextctl2 'Loopthroughthecontrolsonthesubdatasheet 'returningjusttextboxesandtheirvalues ForEachctl4Inctl3.Form.Controls IfTypeOfctl4IsTextBoxThen Debug.PrintString(10, " ")&ctl4.Name&_  " isatextboxthatequals " &_ ctl4.Value& "." EndIf Nextctl4 ctl1.Form.Recordset.MoveNext Debug.PrintString(5, "-") Nextint2 DoCmd.GoToRecord,,acNext Nextint1 DoCmd.CloseacForm, "frmMyOrders"     'Cleanupobjects Setctl1=Nothing Setctl3=Nothing Setfrm1=Nothing     EndSub 

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.

Figure 5.11: An excerpt of output from the SyntaxForSubDataSheetOnSubForm procedure.

Programmatically Inserting Data into a Subform Record

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.

 SubBindRstToSimpleMainSubFormRWInsert()  Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimfrm1AsForm Dimctl1AsControl Dimctl2AsControl Dimint1AsLong Dimint2AsLong     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb;"     'Createrecordsetreferenceandsetitsproperties 'topermitread/writeaccesstorecordset Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.CursorLocation=adUseClient     'Openrecordsetbasedonordersin1998for 'frmSimpleUnboundForm rst1.Open "SELECT*FROMOrderso " &_  "WHEREYear(o.OrderDate)=1998",cnn1     'Openamain/subform,assignpointersformain 'formandsubformcontrol,andassignrecordset 'tothemainform'sRecordsetproperty DoCmd.OpenForm "frmMyOrders" Setfrm1=Forms("frmMyOrders") Setctl1=frm1.MyOrderDetails Setfrm1.Recordset=rst1     'AddanewrecordthroughthesubformtothecurrentOrderID 'valueonthemainform int1=frm1.OrderID DoCmd.GoToRecord,,acNewRec ForEachctl2Inctl1.Form.Controls IfTypeOfctl2IsTextBoxOrTypeOfctl2IsComboBoxThen SelectCasectl2.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 CaseElse MsgBox "ErroronInsert." ExitSub EndSelect EndIf Nextctl2 DoCmd.CloseacForm,frm1.Name     'Createrecordsetreferenceandsetitspropertiessothat 'youcanseektherecordsfortheOrderIDtowhichyou 'addedarecord Setrst1=NewADODB.Recordset rst1.Index= "OrderID" rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Open "OrderDetails",cnn1,,,adCmdTableDirect rst1.Seekint1     'LoopthroughlineitemsforOrderIDtowhichyou 'previouslyaddedarecord;deletetherecord 'whenyoufindit DoUntilrst1(0)<>int1 Ifrst1(1)=int2Then 'Deletetherecordbecausewedonotwanttochange 'theNorthwinddatabase,butonlyshowthatwecan rst1.Delete ExitDo EndIf rst1.MoveNext Loop     'Cleanupobjects rst1.Close cnn1.Close Setfrm1=Nothing Setrst1=Nothing Setcnn1=Nothing     EndSub 

Summing Subform Control Values on a Main Form

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.

 PrivateSubForm_Open(CancelAsInteger)  ConstconRightAlign=3     'Right-alignvalueintextbox Me.txtTotalExtendedPrice.TextAlign=conRightAlign     EndSub     PrivateSubForm_Current()  Dimctl1AsControl Dimctl2AsControl Dimdbl1AsDouble     'Assignapointerforthesubformcontrol Setctl1=Me.MyOrderDetails     'Iteratethroughtherecordsonthesubformto 'acc