Ideas to speed up a subform

[Dave]

I have a form with 56 subforms. Yea, I know. That's a lot. But the
purpose of the form is to show the contents of a tray with 56 different
vials. There seemed to be no other way to achieve this. Anyway, it
loads up okay on my local machine with just a brief pause. However, as
soon as I split the database and put the backend on the shared drive,
the form won't load at all. Any ideas on how I might speed up this
particular form?

Thanks,

Dave B

 

[Allen Browne]

Dave, could you do this with one form if you added one more field to your
table:
VialID Number
The field contains a value between 1 and 56.
Now you have 56 records for the TrayID if the tray is full.
And each one shows in a different row, in a single subform.

Or perhaps, you might use a main form to show the details of the Tray, and a
subform with 56 records to show the details of each vial.

If necessary, the subform could have another subform within it to give the
details of tests conducted on that vial.

And the main form could have a combo where you select the vial you wish to
see. No code is needed: just include the combo in the
LinkMasterFields/LinkChildFields so the subform loads the correct data for
the vial you select in the main form.

Or, if you prefer the 56-tabs, you could use a tab control with 56 pages,
but a single subform sitting on the main form below the tab control (i.e.
there's nothing actually in the pages of the tab control, and it is only
tall enough to show the buttons.) You can then use the name of the tab
control in LinkMasterFields/LinkChildFields, so that Access shows the
correct data in the subform.

That's the normalized solution. There are others that are nowhere near as
good.

As an aside, Tony Toews has a good list of items here to help with the
performance of split databases:
http://www.granite.ab.ca/access/performancefaq.htm