The Sub-SubForm You've Always Wanted

<< Click to Display Table of Contents >>

Navigation:  Sub Forms >

The Sub-SubForm You've Always Wanted

200303_ph1 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.

200303_ph1 Figure 1

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).

200303_ph2 Figure 1

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.

200303_ph3 Figure 3

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

1.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.

200303_ph4 Figure 4

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.

200303_ph5 Figure 5

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.

Your download file is called SUBSBFRM.exe in the file SA2003-02down.zip

This can be purchased with all the other downloads on this page

 

 

Other Articles on Forms

Flexible Normalization and Denormalization of Data

Explorer Forms with a Single Keystroke

Tip: Closing All Forms

 

and Sub Forms

Control Your Subforms

Let Your Users Sort it Out

Zoom, Zoom, Zoom

Using List Regions with Many-to-Many Relationships
Undo in Sub Forms

You Can Do That with Datasheets?