Form with two subforms = SLOW PERFORMANCE

[keviny04]

I made a test database to illustrate my problem:
Views: 9 Size: 70.7 KB">subform tests.zip

I have two tables, Table1 and Table2, that have a one-to-many relationship. I made a form named "Main Form 1" which sets up the two tables as main form and subform. Everything works as it should. Note that the child records on the subform show up almost instantaneously whenever I move to a different record on the main form.




But then I made a "Main Form 2" that sets up the two tables as two SUBFORMS:



The form is set up so that when I click on a record on Table1 subform, the child records of the Table2 subform appears. This is done by setting the link master & child fields in Table2 subform's property sheet.

My problem is that the child records take HALF A SECOND (or more) to appear. I click on a record on Table1 subform, child records on Table2 subform appear half a second later. Click on another row on Table1 subform, another half second wait.

Anyone know why there is such a delay and is there any way to eliminate or minimize it? Note that it doesn't matter how many child records there are to show. Even if there is only one child record to show, the delay still occurs.

---------

My workaround currently is NOT to use the link master & child fields in Table2 subform's properties. I set the link fields to blanks. Then I go to Table1 subform's OnCurrent event and CHANGE Table2 subform's RecordSource directly:

Private Sub Form_Current()
    
    ' Ignore error caused by "Table2 subform" not being loaded
    On Error Resume Next
    
    ' Change record source of "Table2 subform" directly
    Forms![Main Form 3]![Table2 subform].Form.RecordSource = "SELECT * FROM Table2 WHERE ID=" & Me!ID

End Sub

And the result is in "Main Form 3" of my database. The child records appear INSTANTLY every time I select a record in Table1 subform.

I'm beginning to implement this workaround in several of my projects, whenever performance becomes an important issue.

But I still would like to know why such a performance hit occurs only in a subform-subform setup, but NOT in a mainform-subform setup. Don't the two setups use the same kind of link master & child fields?

[CJ_London]

your workaround is a fairly standard solution - but you might be even quicker if you used

Parent.[Table2 subform].Form.RecordSource = "SELECT * FROM Table2 WHERE ID=" & Me!ID

Why is the linkchild/master slower? probably because you are bringing through the entire dataset and effectively filtering, plus having to have the ID field in the main form react to the oncurrent event - so more steps to go through. Suggest try this as an alternative (in table1 current event) and compare with the linkchild/master times

with Parent.[Table2 subform].Form
 .Filter="ID=" & Me!ID
 .filteron=true
end with

[NTC]

link master & child fields in Table2 sub form is not correct; it must be unbound in your design

if table 1 was a single form (sub form) and table 2 was inset into it - then the master/child linking would be correct

your record source approach is correct; or you could source table 2 on a query that refers to criteria of table 1 - and requery table2 upon select of table 1

[keviny04]

Why is the linkchild/master slower? probably because you are bringing through the entire dataset and effectively filtering,

But then why does the slowdown NOT occur in a mainform-subform setup (See my "Main Form 1")? The subform there also brings through the entire dataset with filtering, just like in the subform-subform setup.

plus having to have the ID field in the main form react to the oncurrent event - so more steps to go through. Suggest try this as an alternative (in table1 current event) and compare with the linkchild/master times

with Parent.[Table2 subform].Form
 .Filter="ID=" & Me!ID
 .filteron=true
end with

Your code also yields pretty fast result, but that is not really in question. What is in question is that when there is NO OnCurrent event, no code, just the subform's built-in properties for the link master & child fields being used, then there is a slowdown. And it only occurrs in subform-subform, not mainform-subform. THAT is the mystery to me.

[keviny04]

link master & child fields in Table2 sub form is not correct; it must be unbound in your design

if table 1 was a single form (sub form) and table 2 was inset into it - then the master/child linking would be correct

The link master field for Table2 subform is the invisible textbox named "ID" near the top of "Main Form 2", with control source =[Table1 subform].[Form]![ID] . It carries over the "ID" field from Table1 subform, so it can be used for the link master field for Table2 subform. This is how it is usually done when you need to link multiple subforms on a form.