Andrew Wrigley shows how to design a “Breadcrumb” control that will enhance your users’ experience whenever they need to navigate a hierarchical structure (and bitterly regrets his lack of documentation…).
For those of you who think that you don’t know what a Breadcrumb control is: You already do. Breadcrumbs are all over the Internet. Figure 1 shows a typical example. Continue reading →
The Access subform wizards do much of the workin connecting a main form to its subform. In this article, Rick Dobson goes beyond the wizards toshow you how to perform two new tasks with a subform. He also takes you behindthe scenes to increase your understanding of how to access the subform inside asubform control.
A main/subform design is a standard way of presentingdata in Access applications. In this type of form design, one form (a subform)synchronizes the data that it displays based on the data displayed on the mainform. For example, a subform shows the line items for the sales order numberdisplayed on the main form. The subform refreshes the line items that it showswhen the order number on the main form changes. It’s not uncommon for one mainform to have multiple subforms. Each of the subforms shares one or more fieldvalues with the main form. It’s those shared values that allow Access tosynchronize the rest of the subform values with the data showing on the mainform.
You can think of the main form as a parent to itssubforms. In managing the relationship between main and subforms with code,it’s useful to recall that a Subform control on the main form represents thesubform that the control contains. The Subform control has a Form property,which lets the code from your main form access the controls and recordsetvalues on the subform. By tapping the Subform control and its Form property,you can perform many tasks, including enumerating the subforms on a formor summarizing the data in subforms into a control on the main form.
The sample forms
In order to discuss this topic, I’ll need some exampleforms. The accompanying Download file for this article imports the Categories,Order Details, Orders, Products, and Suppliers tables from the Northwind.mdbfile. Built-in parent-child relationships exist between selected pairs of thesetables, including Orders and Order Details, Products and Suppliers, as well asCategories and Products. When you use the Access 2002 or Access 2003 AutoFormWizard with the Orders, Products, or Categories tables, you automaticallycreate a main/subform. Earlier versions of Access require you to createseparate main and subforms and then drag the child form from the Databasewindow to the design view of the parent form in order to create a main/subform.
I created three main/subforms for the sample application with the AutoForm tool:
• frmOrders–The main form is based on the Orders table; the subform is based on the Order Details table.
• frmProducts–The main form is based on the Products table; the subform is based on the Order Details tables.
• frmCategories–The main form is based on the Categories table; the subform is based on the Products table.
In addition, I created a standalone form (using theAutoForm Wizard) based on the Suppliers tables and called it frmSubSuppliers. Idragged this form from the Database window onto the frmProducts form in designview. As a result, the frmProducts form includes two subforms–one automaticallyinserted by AutoForm and another manually added in design view.
It frequently happens on main forms that you want toshow a summary, such as a count or a sum, of the data on a subform. Todemonstrate, I added an unbound TextBox to the frmOrders form to display thetotal extended price for the detail lines on the subform. Code behind the formtotals the product of three column values on the subform for all the rows onthe subform.
In addition, I added two unbound TextBox controls tothe frmProducts form to show the number of rows in each of its subforms.Populating these TextBoxes requires counting the rows in two separate subforms.You can easily program the contents of these unbound controls if you have asolid grasp of the Form property for the Subform control (and related issues).
Enumerating the forms in a database
Main/subforms are forms with one or more Subformcontrols. Therefore, enumerating the forms in a project lists both the standardforms (forms without one or more Subform controls) and the main/subforms in aproject. Before highlighting some of the special features of a main/subform,I’ll start with a program that enumerates all the forms in a project. You’llneed to build on this technique for listing ordinary forms when you focusexclusively on main/subforms and their Subform controls. The FormNameAndSource procedure shown here lists eachof the forms in a project along with its record source. However, when a formincludes a main form and a subform, the record source will be only for the mainform. Since all the forms in the sample project are main/subforms (exceptfrmSubSuppliers), all these forms show just the record source for the mainform:
The FormNameAndSource procedure uses an AccessObject,abj1, to iterate through the members of the AllForms collection in theCurrentProject. An AccessObject provides the minimum of information aboutwhatever object it refers to but will work with any object in an Accessapplication (for example, forms, reports, tables, and queries). An AccessObjectobject in the AllForms collection is referring to something very different froma Form object in the Forms collection in a project. Objects in the Formscollection correspond exclusively to open forms. AccessObject objects in theAllForms collection correspond to forms whether or not they’re open. TheAccessObject object and its corresponding collections were introduced intoAccess starting with Access 2000.
A member of the AllForms collection has an IsLoadedproperty that returns a Boolean value of True when a form is open and a valueof False otherwise. The FormNameAndSource gathers all the form names with theirRecordSources to a string variable. However, a form’s RecordSource propertyisn’t available unless a form is open. My FormNameAndSource procedure takesadvantage of the IsLoaded property to open an Access form if it’s not alreadyopen. When a form is open, the procedure simply adds the form’s RecordSourceproperty to the string variable (str1) that summarizes all the forms in aproject. If a form isn’t open, then the procedure opens the orm beforeattempting to append the form’s RecordSource property value to str1.
After finishing with a previously closed form, theprocedure returns the form to its former closed state. After iterating throughall the members of the AllForms collection, the procedure displays the formnames along with the record sources in str1 by invoking the Print method of theDebug object. By interspersing vbTab and vbCrLf constants in str1, the outputis formatted with blank lines between forms (making form names appear onseparate lines from their record sources), and indenting record sources fromtheir forms.
Enumerating the subforms in a project
There’s no built-in collection for enumeratingsubforms in a project (or even within a form). However, each Access form has aControls collection. Only the Subform control has the properties for managing asubform, such as the SourceObject property. The SourceObject property points atthe RecordSource for a subform. Therefore, you can present data about thesubforms in a project by searching each of the project’s forms for Subformcontrols and then displaying selected properties of those Subform controls,such as their Name and SourceObject properties.
My CallSubFormNameControlSource andSubFormNameControlSource procedures (see the following code block) show how toenumerate all the subforms in the current project. CallSubFormNameControlSourceiterates through the forms in a project and either immediately callsSubFormNameControlSource or opens the form before calling the sub procedure. Ifthe main procedure opens a form before calling its subroutine, the mainprocedure closes the form after control returns to it:
The SubFormNameControlSource procedure starts bysetting a reference to the form named in its argument (frmName) and printingits argument’s value to the Immediate window. Then, the procedure loops throughthe controls on the form designated in its argument, using a For…Nextstructure. An If…Then statement within the For…Next structure filters forcontrols with a Subform type. The test in the If…Then statement uses a TypeOffunction to filter for Subform control types.
After finding a Subform control, the procedure assignsthree items to a string (str1) that summarizes subform specifications on themain form:
• The Subform control’s Name property.
• The Subform control’s SourceObject property.
• The subform’s name. The subform within the Subform control is accessed through the Subform control’s Form property, giving the line:
The other two properties are properties of the Subformcontrol (rather than the form that it references). Therefore, these items don’tinclude the Form property when designating them.
SubFormNameControlSource writes to the Immediatewindow after looping through all the controls on a form (provided that str1isn’t a zero-length string). The str1 string reports on all the Subformcontrols and their corresponding subforms on the form. Since the For…Nextloop iteratesthrough all the controls on the form, it will display informationfor all the Subform controls on a form. If str1 is a zero-length string, theprocedure writes to the Immediate window that there are no subforms on the mainform.
Figure 1 shows the Immediate window output fromSubFormNameControlSource. As you can see, all of the four forms from the sampleapplication are listed. In addition, the output precisely describes the subformstatus of each form. For example, the output indicates that the frmSubSuppliersform has no subforms, but the frmProducts form has two subforms. ThefrmCategories and frmOrders forms each have a single subform. The output givesthe name of the Subform control for each subform.
Going one step further, through the Form property ofthe Subform control you can access the values on a subform that synchronizewith the current record on a main form.
Enumerating the values on a subform
Being able to enumerate the values on a subform can bevery useful. For example, it lets you accumulate or develop computed fieldvalues on the main form based on subform values. In fact, you can even developa computed value for each row on a subform and then sum the computed valuesacross rows. The rows displayed in the subform will automatically changewhenever the data on the main form changes. Therefore, you must specify mainform row values when enumerating subform values. TheCallEnumerateSubformControlValues and EnumerateSubformControlValues proceduresdemonstrate how to enumerate subform values:
CallEnumerateSubformControlValues manages the mainform and optionally designates a range of rows on the main form for which toenumerate values on a subform. The procedure starts by referencing the mainform and the Subform control pointing to a subform. For the sample application,I used frmOrders as the main form and Child28 as the Subform control name. Youcan use the CallSubFormNameControlSource and SubFormNameControlSourceprocedures to generate appropriate code for your own applications.
One main reason for designating a main form is to openit. CallEnumerateSubformControlValues closes the main form just before ending.When modifying this code for your own project, you’ll need to alter the mainform name and the Subform control name.
EnumerateSubformControlValues lists the values on thesubform for each of a range of rows on the main form.EnumerateSubformControlValues can be called in two ways:
• You can specify just the names for the main form and the Subform control.
• You can also specify a starting row and an endingrow on the main form for enumerating subform recordset values.
CallEnumerateSubformControlValues shows the syntax forboth ways of calling EnumerateSubformControlValues. If you don’t specifystarting and ending main form row values, EnumerateSubformControlValues usesdefault values of 1 and 2 to designate the first and second rows as thestarting and ending rows.
EnumerateSubformControlValues begins by moving to thestarting row (intFirstRow) on the main form for enumerating subform values.This starting row will either be a value explicitly passed to the procedurefrom its calling procedure or the default value of 1. The heart of theprocedure is a set of nested For…Next loops that perform the enumerationeventually displayed in the Immediate window. The outer loop designates thestarting and ending rows on the main form for the enumeration.
The next loop iterates over the rows in the subform.This loop depends on the RecordCount property for the subform recordsetmatching the current row on the main form. The innermost loop passes throughall the controls on the subform. As the code passes through each control, itprints both the control’s name and value for the current row in the subform’srecordset. Both the second and third loops specify the collection of values orobjects over which they iterate by using the Form property of the Subformcontrol (ctl1). Before returning to the top of the outer loop, the procedureadvances the current record for the main form by one using the DoCmd object’sGoToRecord method.
Figure 2 shows an excerpt from the output ofEnumerateSubformControlValues with a starting row value of 2. Notice thatlookup values, such as the product name for ProductID in the Order Detailstable, don’t appear. If you require the lookup values (which are product namesin this case), you need to code the translation yourself. On the other hand,the numeric UnitPrice, Quantity, and Discount column values appear in a formatsuitable for computing extended price.
Summing subform values
One important reason for understanding how toenumerate subform values is to be able to summarize subform data on a mainform. Figure 3 shows the form frmOrders, which contains a TextBox (it’s the onejust above the subform, with the label Total Extended Price). The TextBoxcontains the sum of the extended price for each row in the subform. Theextended price for a row is the product of the values in the Quantity and UnitPrice columns multiplied by 1 minus the value in the Discount column. The formin Figure 3 doesn’t show values for main controls that don’t permit updates (forinstance, Customer, Employee, and Ship Via), or for controls for which there’sno value (for example, Ship Region).
The Form_Current event procedure for frmOrderscomputes the totalextended price for the line items in an order and formatsthe computed value as currency in the TextBox for total extended price. Byusing the Form’s Current event to hold this code, the total extended pricevalue is always fresh whenever a user moves to a new record on the main form orwhen the form initially opens:
The Form_Current procedure commences by setting areference to the Subform control (Child28). Then the procedure loops throughthe rows of the recordset in the subform that match the row showing on the mainform. Within the loop, the procedure computes the total extended price as thesum f the extended price for each row. The procedure uses the Form ropertyfor the Subform control (ctl1) to specify the End value in the For…Next loopand the Quantity, UnitPrice, and Discount control values on the subform. Anassignment statement at the close of the procedure formats the aggregatedextended price values as currency in the txtTotalExtendedPrice TextBox.
Counting subform rows
The frmProducts form illustrates a main/subform designwith two subforms. The Products table is the record source for the main form.The Order Details and Suppliers tables are record sources for two subforms.Figure 4 shows an excerpt from the form with all of the main form and OrderDetails column values for the first product, Chai, along with a clipped versionof the subform for the Suppliers table. In this form there are two TextBoxcontrols just above and to the right of the Order Details subform. TheseTextBoxes show the number of rows in each subform. Since there’s only onesupplier per product in the Northwind database, the second TextBox will alwaysshow one, but subforms aren’t restricted to just one row in their recordset. Byusing two subforms, I can show a common scenario where there are multiplesubforms on a main form.
It’s actually easier to compute the count of rows in asubform than to compute a sum based on the rows in a subform’s recordset. Thisis because all you need to do is assign the RecordCount property for asubform’s recordset to the TextBox on the main form. There’s no need to loopthrough the rows in a recordset for a subform.
The Form_Current event procedure for frmProducts showsthe syntax for processing two subforms from a main form. First, the procedureassigns a reference to the Subform control (Child20) for the subform based onthe Order Details table. Then, the procedure uses the control’s Form propertyto return the subform’s recordset, which, in turn, allows me to access theRecordCount property value. The procedure assigns the value to txtOrderCount.Next, the procedure repeats the process for the Subform control pointing at theSuppliers table to populate txtSupplierCount:
Note: You could use this direct code and skip the ctl1 controlreference Me!txtOrderCount =Me!Child20.Form.Recordset.RecordCount
This article has drilled down on how to process anddisplay Subform control values on a main form. I showed two implementations ofthis kind of processing but, more importantly, provided the backgroundknowledge that will allow you to extend and adapt the two samples shown here.
FOR those of you who have no plans or interest inabandoning the Jet database engine and moving to SQL Server or the MSDE throughAccess DataProjects, our constant harping on Access Data Projects – ADP techniquesand technologies must be driving you nuts. If your future doesn’t include ADPs,then these articles aren’t any help to you.
I firmly believe that most developers whose futuredoesn’t include ADPs aren’t stupid or lazy or dinosaurs. They don’t look atADPs for a simple reason: Jet works. Furthermore, they know how Jet works andcan build the standard parts of their applications quickly (even cannibalizecomponents and code from other applications for faster development). If thatsounds like you, well, you’re right—you’re very productive with Jet.
One of the benefits of hanging around in this business is that I get to meet a lot of people who are brighter than me and far more knowledgeable. Unfortunately, they keep upsetting my vision of the Access universe. For instance, a recent upsetting incident is about to cause me to issue a heartfelt apology to you.
I was presenting at a conference recently when Andy Baron, one of the most intelligent and capable people I know (and who has forgotten more about Access than I could hope to know), commented that the best way of working with SQL Server from Access was to use linked tables. My whole life flashed before my eyes.
In the January issue of Smart Access, I ranted abouthow awful hackers (in the pejorative sense) were and went on to suggest thatMicrosoft ends up carrying a lot ofthe blame for the actions of vandals. AndrewBenner wrote in with what I thought was a great response:
I certainlyagree with you that each dirtbag who writes a virus/trojan and releases it intothe wild is to blame for the havoc their software creates. By the same token,Microsoft is directly responsible for the software it creates.
IT Impact Inc. is a minority owned Software Company specializing in custom software solutions. We offer service in .Net Programs, SQL Server, Microsoft Access and much more! Some of our Microsoft Access Developers are Microsoft Access MVPs, making us one of the largest firms with the most Access MVPs on staff.