The Sub-Subform You've Always Wanted

Reference url: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart03/html/sa03c1.asp

Peter Heskett

Access won't let you have a subform within a subform if the data has to be displayed in a continuous form. But necessity is the mother of invention, and Peter Heskett found a way by using senior and junior subforms. He also shares a few other tricks for handling forms.

After more than 10 years of developing business systems for clients, I thought I'd done it all. But with one new client, I found myself having to work out a new way of form handling to give them what they wanted.

My client's distribution business involves four levels of detail. First is the Consignment (Level 1), which consists of a container full of boxes, which the company has to distribute to various destinations in Spain. Each Consignment is broken down into Trips (Level 2), one Trip for each destination in the Consignment. On each Trip the company can deliver to a number of different clients who get a Receipt (Level 3). Each Receipt will include a number of Boxes (Level 4), which are priced according to box type.

Are you still with me? The traditional solution to this would be to have a Consignment form with a Trips subform, then a drill down to the next level of detail (the Receipt) with, perhaps, a pop-up for Boxes. But that wasn't going to suit this client, who wanted to manage the Trip with its Receipts and related Boxes all on one screen.

What I needed was a Trip main form with two subforms: one for the customer Receipts and the other for the Boxes to be delivered to the individual customers. The only problem (in Access 97 and 2000) is that you can't have a subform within a subform if the main subform is set to continuous forms. So I had to think of a way to do a senior and a junior subform. In addition, I wanted to be able to pass data from my main form to the subform and from the sub-subform back to the subform. Figure 1 shows the form that I ended up with.

When you click on the Box button on the form, the Box subform is revealed showing the boxes for that client Receipt (see Figure 2).

I encountered a number of problems in getting to this solution. In overcoming all of them, I used Access tools to the fullest and kept the programming as simple as possible. My solution may not be scalable if you're displaying lots of records, but the solution was quick to implement. It will also appeal to those of you who like using code behind your forms and have other things to do apart from programming.

The main problems that I had to overcome included:

• Getting the client pick list to show just the clients in a particular location.
• Displaying the full client name as a ToolTip-like pop-up (the client information displayed on the form isn't enough to identify the client to the user).
• Determining how to get the Box subform to relate to the individual client that was selected in the Receipt subform.
• Getting the Box subform to calculate costs based on standard prices attached to the individual customer records.
• Getting the totals from the Box subform into the client Receipt subform.

Limiting combo boxes

It's easy enough to load a combo box at design time. It's more of a problem to change the pick list for each record at runtime depending on the values on the form. To do this you need to set the Row Source of the combo box in two places:

1. In the AfterUpdate event of the key field (in this case, Destination).
2. When you open a form with the Destination already defined, in the Form's OnCurrent event.

The code that I used behind the Trip's Current event was:

Private Sub Form_Current()
If Not IsNull(Me.DestinationID) Then
   Me.TripReceiptSub.Form.ClientRef.RowSource = _
                                   "qryClientPick"
End If
End Sub

A similar piece of code is required in the AfterUpdate event. When setting the row source in the AfterUpdate, you have to save the record before you can set the new value for the list box's RowSource:

Private Sub DestinationID_AfterUpdate()
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                 acSaveRecord, , acMenuVer70
   Me.TripReceiptSub.Form.ClientRef.RowSource = _
                 "qryClientPick"
End Sub

In this case, I've used the same query every time—qryClientPick (which is shown in Figure 3). The query uses the value on the form as part of its criteria ([forms]![trip]![destinationID]) to regenerate the list box based on the value in the destinationID text box.

Creating your own ToolTip

In a continuous form, unbound controls like ToolTips don't really work. Yet I wanted to display the client name on the continuous form as a kind of ToolTip when the user moves over the ClientRef text box. This required me to carry the client name as a data field on the table for Receipts. This is how I set up my ToolTip-like display:

1. Insert a bound text box, named Company, into the subform with a light background color (say, 14286847). Set the text box's Visible property to False.
2. Add a label called LabelCompany, with text to tell the user to click here to make the tip invisible.
3. On the mouse move event of the ClientRef, add code to display the company name text box:

Private Sub ClientRef_MouseMove
If Not IsNull(Me.ClientRef) Then
   If Me.Company.Visible = False Then
       Me.Company.Visible = True
       Me.LabelCompany.Visible = True
   End If
End If
End Sub

4. On the Click event of Company, add some code to switch the ToolTip off. You need to put the focus somewhere else before you can set the Visible property to False. This is the code that I used:

Private Sub Company_Click()
 Me.Notes.SetFocus
 Me.Company.Visible = False
 Me.LabelCompany.Visible = False
End Sub

Linking the sub-subform

Given the restrictions of Access, I set up a senior and a junior subform, both linked in a master-child relationship to the Level 1 Trip form.

TripReceiptSub is the senior subform and is linked to the main Trip form by TripID. TripBoxSub is the junior subform and is linked to the Trip form by the ReceiptID. The Trip form doesn't have a bound ReceiptID. Rather, when you click the Box button on the main subform, it places a value into an unbound ReceiptID field on the Main Trip form—which will drive the TripBoxSub (see Figure 4). Effectively, TripBoxSub becomes a subform of the TripReceiptSub form.

The code to do this is behind the Box button:

Private Sub btnbox_Click()
' save the record first
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                  acSaveRecord, , acMenuVer70
Me.Parent.ReceiptID = Me.ReceiptID
Me.Parent.ClientRef = Me.ClientRef
Me.Parent.TripBoxSub.Requery
Me.Parent.TripBoxSub.Visible = True
Me.Parent.BoxHide.Visible = False
Me.Parent.TripBoxSub.SetFocus
Me.Parent.TripBoxSub.Form.Company = Me.Company

The junior subform needs to be requeried so that it will link to the new ReceiptID and display just the records for that ReceiptID. The Link Child Fields and the Link Master Fields properties of the junior subform must be set to ReceiptID.

Using data from the main record

Now that I had all my forms synchronized, I needed to use data from the record underlying the main form in my sub-subform. I got around this problem by using the ClientRef combo box on the senior subform. In the qryClientPick you may have noticed a number of extra columns for prices. These are the standard prices the individual clients usually pay.

To make these prices available, I have some unbound price text boxes on the main form. I use the Column property of the list box to set the values for these text boxes. When the user clicks the red Box button, this code transfers the prices from the selected entry in the list box to the unbound fields on the form:

On Error Resume Next
Me.Parent.PriceBig = Me.ClientRef.Column(3)
Me.Parent.PriceNorm = Me.ClientRef.Column(4)
Me.Parent.PriceSmall = Me.ClientRef.Column(5)

The On Error Resume Next is the neatest way of stopping unwanted error messages, should the system find a null value in a column and be unable to set it. Access considers the first column in a combo list as column(0), which is why PriceBig, which is in the fourth column, is referred to as Column(3), and so on.

For this to work, the properties of the Client Ref combo box should be set as shown in Figure 5. In particular, it's important that the number of columns match the number of columns in the query.

Once I had the standard prices on the main form, I picked them up in the AfterUpdate event of the BoxType text box. There are only three different types of box, so I just hard-coded the routine to retrieve the right price:

Private Sub BoxType_AfterUpdate()
On Error Resume Next
If Me.BoxType = "Big" Then
       Me.Price = Me.Parent.PriceBig
   ElseIf Me.BoxType = "Normal" Then
       Me.Price = Me.Parent.PriceNorm
   ElseIf Me.BoxType = "Small" Then
       Me.Price = Me.Parent.PriceSmall
End If
SubValue
End Sub

My SubValue routine that calculates the totals is very simple. I called a subroutine because I calculated Prices numerous times in the application:

Private Sub SubValue()
If Me.Quantity > 0 And Me.Price > 0 Then
   Me.Value = Me.Quantity * Me.Price
End If
End Sub

Updating the senior subform

But I had another problem: I needed to update my senior subform with the totals generated by the records in the junior subform. In other words, I wanted to pass data from the sub-subform back to the subform. The obvious way to do this was to take the totals in the footer of the subform and update the relevant Receipts record. But this only works if the user saves the record before updating, which doesn't always happen. So to make this happen, I needed some proper code.

First, I needed to loop through the records in the table that underlies the junior subform (Boxtbl). I only wanted the records that related to the selected ReceiptID and calculated the total, which I held in variables for later use:

Private Sub btnAct_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                    acSaveRecord, , acMenuVer70
Dim iChk As String
Dim db As DATABASE
Dim rs As Recordset
Dim rc As Recordset
Dim iTotb as Long   ' boxes
Dim iTotv As Double   ' value

Set db = CurrentDb()
Set rc = db.OpenRecordset("Select * from Boxtbl " & _
 "Where receiptID = '" & Me.Parent.ReceiptID & " ' ")

If rc.BOF And rc.EOF Then
       ' No Records
   Else      
     iTotb = 0
     iTotv = 0
   While Not rc.EOF
      iTotb = iTotb + Nz(rc("quantity"))  ' boxes
      iTotv = iTotv + Nz(rc("value"))
      rc.MoveNext
   Wend
End If
rc.Close

With new totals calculated and held in the variables iTotb and iTotv, I update the record underlying the senior subform with this code:

Set rs = db.OpenRecordset( _
   "Select * from Receiptbl " & _
   "Where ReceiptID = '" & Me.ReceiptID & " ' ")
   rs.Edit
       rs("BoxTotal") = iTotb
       rs("ValueTotal") = iTotv
   rs.Update
   rs.Close

The final step is to requery the senior subform to pick up the new totals from the table:

Me.Parent.TripReceiptSub.Requery

DAO references

In the sample database, I've used DAO code, which will work for all versions of Access. But if you're using Access 2000, you'll need to set the module References in Access 2000 to include DAO. To do this, open a module and select Tools and then References, and scroll down and click on Microsoft DAO 3.51 or 3.6. You'll then need to close the References dialog and open it again to check where the DAO reference is positioned. The DAO needs to be third in the list if it's going to take precedence and work properly.

With this code, my client was able to do most of what they had to do on just the one form. Since doing this, I've incorporated this sub-subform method in another client system, and have plans for more. I hope you find it as useful as I do.

Download SUBSBFRM.exe

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the March 2003 issue of Smart Access. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.