Navigation:  Articles > Jul-1998 >

Right Clicks, Subforms, and Numbers

Previous pageReturn to chapter overviewNext page

Angela J.R. Jones          
 
Angela solves a few day-to-day form and report problems, including how to stop dangerous right-clickers and how to dynamically add line numbers.
 
 

In Access 95 and 97, I've noticed that when I have a form with multiple subforms, I sometimes have a peculiar viewing problem -- it seems that when I change records on the main form, some of my related subform records seem to disappear! They don't actually disappear, but they're not visible until I scroll or Shift-Tab up to view them. How can I fix this?
 
Annoying, isn't it? This problem occurs on forms with one or more related subforms. When you change the current record on the parent form, the child forms are requeried as expected in order to synchronize their data, but not all the records are visible.
 
You can see a great example of the problem in Northwind. Open the Customer Orders form, and go to customer "Save-a-lot Markets" (record number 71, if you don't feel like hunting). The parent form (Customer Orders) only has a little customer information -- name and country. The parent form also has the Customers table as its RecordSource, which includes the [CustomerID] field. No surprises here.
 
The first subform, Customer Orders Subform1, lists all the Orders for the Customer currently displayed on the parent form. The two forms are linked by straightforward master/child links on the [CustomerID] field. No surprises here, either.
 
The second subform, Customer Orders Subform2, lists the details of the Order that's currently active in Customer Orders Subform1. The master/child links are from [CustomerID] on the child form to [Customer Orders Subform1].Form![OrderID] on the master form. In English, that means the second subform links to the [OrderID] on the first subform. Still no surprises.
 
So while you're looking at the order history for Save-a-lot Markets, try browsing through order 10607, which has five items. The last one is for "Uncle Bob's Organic Dried Pears" -- see for yourself. While the fifth record (Uncle Bob's) is still active in the second subform, click on the next order number in the first subform, order 10612. The items for order 10612 are displayed, but notice which record is active in the item subform -- it's the fifth order item, "Ikura", and you can't see the first record. If you were unobservant, you'd think that order 10612 only had three items, since that's what it looks like. But that's not true -- there are five items, you just can't see the first two without scrolling or navigating up to them (see Figure 2).

199807_aj2 Figure 2
 
This phenomenon is especially problematic when dealing with subforms without record navigation buttons. The two subforms on the Customer Orders are a perfect example: The only visual clue that there could be some records that aren't displayed is that the button on the scroll bar isn't resting at the top of the bar. I don't know about your intended audience, but about half my clients can't be trusted to know how to use a scroll bar at all, so I'm not enthusiastic about trusting them to know how to scroll up to see more records. Scroll down is plenty tricky enough.
 
So that's the problem. How do you solve it? The most difficult part of the solution is remembering to use it -- I have a bad habit of not remembering to put it into my forms until the testing phase. Fortunately, it just takes a few lines of code. Your code will need to switch to the "problem" subform, move to the first record there, and then set the focus back to the appropriate place in the original form. You'll also need to trap for a particular error when the form loads: your OnCurrent event will occur before the GoToRecord command is available.
 
Using generic names, the three lines of code you need to add are:
 

Forms!ParentForm!ProblemChildForm.SetFocus

DoCmd.GoToRecord acActiveDataObject, , acFirst

Forms!ParentForm!SubformWhereChangeOccurred.SetFocus

 
 
The error-handling code you need to put in the routine is:
 

If Err.Number = 2046 Then Resume Next

 
 
In the Customer Orders form in Northwind, the code will need to be put in the OnCurrent event of Customer Orders Subform1. It would read as follows (complete with the stamped-in code that was already there):
 

Sub Form_Current()

Dim strParentDocName As String

Dim ctl1 As Control, ctl2 As Control

On Error Resume Next

 

strParentDocName = Me.Parent.Name

Set ctl1 = _

    Forms![Customer Orders]![Customer Orders Subform1]

Set ctl2 = _

    Forms![Customer Orders]![Customer Orders Subform2]

 

If Err <> 0 Then

   GoTo Form_Current_Exit

Else

   On Error GoTo Form_Current_Err

   Me.Parent![Customer Orders Subform2].Requery    ' This how you requery a subform, you reference the subform object name in the main form.

   'next 3 lines ADDED for our problem child form

   ctl2.SetFocus

   DoCmd.GoToRecord acActiveDataObject, , acFirst

   ctl1.SetFocus

End If

 

Form_Current_Exit:

    On Error Resume Next

    ctl1 = Nothing

    ctl2 = Nothing

    Exit Sub

 

Form_Current_Err:

    'next line ADDED for our problem child form

    If Err.Number = 2046 Then Resume Next

    MsgBox Err.Number & ": " & Err.Description

    Resume Form_Current_Exit

 

End Sub

 
 
Now the form works exactly as expected, and when you view a new customer or a new invoice, the affected subforms all have their first record made active.
 
 
In closing
 
As much as I enjoy venturing into fun stuff like ActiveX controls or OLE graphing, I keep finding that most of my customers just need solid working forms and easy-to-read, accurate reports. All four of these questions are things I run across all the time, and the answers are immediately usable for most developers.