Question Form Efficiency and Speed with multiple subforms (1 Viewer)

[darbid]

I am currently designing a form which could have 3 Single Subforms and 1 Continuous Sub Form. (Only the 3 single subforms allow user input - the main form and the continuous do not allow user input) I also have a SQL Server Backend. And the main form which I am getting records from has about 68,000 records.

I am considering speed and efficiency.

1. I could link the tables (which currently are new so not linked) and build a very complicated (I hope I can build it) SQL for the form binding the 3 single forms and another SQL for the Continuous form.
2. I could leave the tables unlinked and have a very basic SQL returning the primary key of the record needed and then just get the required fields for each record and fill all the unbound controls.

1. means that the start up time of the form will be longer, but as I understand it Access pulls more records across at a time so moving to the next record would be pretty quick. Plus I have bound controls so I do not have update problems.

2. Would allow the form to open pretty quickly but would require SQL execution for each form etc for each move to a new record. Further I would have to do some fancy coding to check for updates becasue of the unbound forms.

I would be happy to hear your opinions on which direction I should go.

 

[darbid]

I was not looking for an answer but more a discussion. If I have not formulated my initial information properly please let me know.

 

[gemma-the-husky]

try it with the forms all populated, and see what the performance is like. depending what they are, you might find it acceptable anyway.

otherwise, you could have the subforms on a tab container, and just populate them as you move from tab to tab.

or even just have a single tab, and change the presentation, by changing the subforms sourceobject, to view the actiive subform.

 

[darbid]

From your answer I think I have stuffed up the initial information. I will go practical.

I have now put all controls unbound on one form and only have one continuous subform.

The record source of the form will only select the primary key.

In the OnCurrent event I will have at least 4 different SQL queries which use the primary key to fill different parts of the controls and of course to provide a recordsource to the continuous sub form.

I would call this the data on a "need to have" basis. Form will open quick, but changing records will be bogged down by the OnCurrent event.

The other extreme would be some complicated SQL (I am not good with SQL so I am not even sure I can) which could be used as the main forms record source, which will have about 25 fields in the select and have many joins like 8 I think.

This means the form will take a long time to open but I am guessing that while the users is looking at the first record Access is bringing new records into the cache, thus going to the next record will be a lot quicker.