Slow Opening Form using DoCmd.OpenForm

[timfoster]

Folks,

I have an unbound form that contains 4 sub forms and a few unbound controls that I populate OnLoad. The form opens from a ribbon button which fires a function to create a series of queries that get the relevant data from SQL Server ready for the unbound form. This function executes up to the point of running the DoCmd.OpenForm in less than 1 second. There is an amount of code in the OnLoad event that executes in just over 1 second.

However, the entire form takes 10-15 seconds to load.

I have proven that the problem is the DoCmd.OpenForm as I have written debug statements with a timestamp as the line prior to the OpenForm and another debug as the first line inside the OnLoad. The difference between the two debug statements is 10-15 seconds.

All my data comes from SQL Server, but it's not the connectivity to SQL that's my problem. I've checked the speeds there with both debug statements and using profiler.

Can somebody explain exactly what goes on under the hood of DoCmd.OpenForm? It defo isn't just loading the form into memory and firing the OnLoad event.

[rpeare]

http://office.microsoft.com/en-us/ac...005186761.aspx

Link showing order of events

[timfoster]

http://office.microsoft.com/en-us/ac...005186761.aspx

Link showing order of events

Thanks for the reply. I understand the order of events, but this form is taking 10-15 seconds to go from the DoCmd.OpenForm action to the first line of code inside in the OnLoad event. There is no other code in any of the other form events.

[June7]

I have a couple of complex forms with subforms and they take several seconds to load. The longest appears to be about 6 seconds. I am not using SQLServer as backend, just an Access file. I have a report with 7 graphs and code to manipulate the graphs that takes about 30 seconds to render.

Is the frontend running on desktop or is it opened from the server?

[orange]

Have you limited those queries in the onLoad to only retrieve the records you need?
Does the OpenForm have OpenArgs to limit the number of records retrieved(if appropriate)

[timfoster]

It isn't the OnLoad code that's slow. That code will run in under 1 second. I have the statement debug.Print Now() as the last line before the DoCmd.OpenForm and the same line again as the first line inside the OnLoad event. The difference between those two statements is approx. 10-15 seconds. The only thing that occurs between the two statements is the OpenForm

[timfoster]

Is the frontend running on desktop or is it opened from the server?

The front end is running locally on the users machine. The shortcut to open the app runs a script to copy a central version locally. It's this version that they log in to.

[orange]

So every user has a copy running on his/her PC. And the Backend is on SQL Server
Do you have any OpenArgs, filter, where condition on the OpenForm command.

Does any code behind the form deal with Domain Aggregate functions? (DCount.DLookup...)
Are you building/creating data to populate form controls?
Why an unbound form?

[timfoster]

There are a couple of domain aggregates, but as I said, the form is taking 10-15 seconds between between OpenForm and the first line of code in the OnLoad. The domain aggregates are calculated further down in the code that takes less than 1 second to execute.

[ItsMe]

There are a couple of domain aggregates, but as I said, the form is taking 10-15 seconds between between OpenForm and the first line of code in the OnLoad. The domain aggregates are calculated further down in the code that takes less than 1 second to execute.

Maybe you can do some further testing by creating a new, unbound form. In the new form, place one or two of your domain functions in a click event.

[Missinglinq]

...I have an unbound form that contains 4 sub forms...

...Can somebody explain exactly what goes on under the hood of DoCmd.OpenForm...

It's not actually the OpenForm command. I think what you're missing here is the fact that when you have a Main Form with Subforms, and you start that Form (regardless of how you start it) all of the Subforms load before the Main Form loads. In other words, as strange as it sounds, all four of your Subforms are loaded before the Main Form's OnLoad event fires, which is where you're experiencing your logjam.

The workaround to this kind of thing is usually leaving the RecordSource of the Subforms blank until after the Main Form has Opened/Loaded, then assigning their RecordSources.

Linq ;0)>

[orange]

Good point Linq! I knew about subforms loading first but it never crossed my mind. (gettin' old I guess).
I did ask about limiting the record source to needed records only.

[timfoster]

thanks guys. I'll give that a go on Monday