Subform Selection Slow

[gunslingor]

Hey guys, this is a problem I've been facing for many years and its really slowing down my development in this instance. I'm wondering if anyone else has or "has never" had this problem and what work arounds they know of.

The problem is simple, create a form, plop a subform in it... all works fine as expected, except anytime you select the subform, it takes 1-5 minutes for access to start responding again. If you then select something on the subform, as opposed to the main forms subform control previously noted, it takes another 1-5 minutes. Once the internals of the subform control are selected, you can move around within the subform and modify it as usual quite well... but then when you go back to the parent form, it takes another 1-5 minutes to catch up.

Why is this even happening? What is wrong with MS and there implementation.. this is really really slowing down my developement. There was another DB designed by someone else that I was assigned to modify... this one had like 3-4 layers of subforms... i.e. 20 minutes to navigate to the lowest subform and 20 minutes to navigate back out. Why the hell is this happening! How do I fix? :banghead::banghead::banghead::banghead:

 

[pr2-eugin]

Are you talking about form in design mode? If the answer is Yes, my answer is Yes too.. I have found this behavior in Main Form SubForm scenario in Design mode..

As you know a SubForm acts a Control on the main form, but the SubForm is a Form by itself with their own controls.. So to stop me throwing my keyboard when doing this.. I normally open the (sub)form in question and edit it there.. rather than opening the Main form with all the subforms in it..

 

[CJ_London]

There are four basic things to do:

1. Ensure one or more of the forms are not slightly corrupted. Try:

• vba editor - compile code
• compact db
• try saveastext/loadfromtext - this link covers these plus other things http://www.access-programmers.co.uk/forums/showthread.php?t=99179
• recreate forms from scratch

2. Ensure all tables used in the forms are properly indexed and normalised

3. Check queries in forms for efficiency - use of domain functions in large datasets will really slow things down for example - are you doing excessive/unnecessary sorting or bring through large datasets?

4. Check your form vba code for routines that run unneccesarily or could be quicker - for example some events may run more than once as each of the subforms are loaded - this link explains more http://office.microsoft.com/en-gb/access-help/order-of-events-for-database-objects-HP005186761.aspx. A tip is to put a debug.print "FormName EventName" & now() statement into each form's on load, open and current event so you can track the order of forms opening events and the time between each event to determine which event(s) is giving the problem.

 

[gunslingor]

PR2 understands my frustration... yeah, I know I can edit the subform outside the main form, but when you have 3-4 layers of forms, it sure would be nice to edit them all in the master.

CJ made some interesting points, but note that I am talking about design view here. But his points are interesting indeed; I've been wondering why the hell MS's code is so inefficient for subforms... perhaps its triggering some kind of events loop in design view.

It kind of makes sense: no events are supposed to be triggered in design view; however, if you place the subform control on there along with a reference to an actual subform, the child likely doesn't realize its actually in design view until you navigate to it directly. So I guess clicking the subform control activates certain child form events and freaks out because the parent is in design view and the subform is never truly open.

Note that there are no events on any of my test forms, just a parent, child, no events, no code, for testing purposes. I think you could be right though, I think perhaps the open events are trying to trigger but get lost because the parent is in design view.

Hey MS! Fix this shit! It's been a persisent problem for like 20 years and I think we just solved it you bastards! And stop selling your crappy new windows 8 while your at it!

 

[CJ_London]

Just a thought, if you are using crosstabs as one or more of your subform recordsources, this can slow things down considerably because even in design view access needs to run the query to determine the output fields. Won't be noticable in low data volumes but will be with larger ones - to test the premise, convert the crosstab to a table

 

[gunslingor]

I don't believe anything is based on crosstab queries. Basically the parent form would be a list of cyber devices, and the child form would be a list of all patches relevant to that device.

i.e. The relationships are like Devices (primary_key)-->link table(devices.primary_key & patches.primary_key)-->patches (primary_key)

 

[pondlife]

This is the bane of my life.

1. Open in design view a form that has a subform.
2. click on the subform
3. a minute or three passes before the subform is selected and active
4. by this time I've forgotten what I wanted to do with it.
5. click on the main form again
6. remember what I wanted to change on the subform
7. loop back to (2)

Surely in design view when a subform is clicked from the main form:

• no events are happening (we're in design view)
• no queries are being opened (we're in design view)
• no records are being fetched (we're in design view)

So in desperation I:

• decompile the database
• compile it
• compact & repair it
• loop back to (1)

Someone please hit the CTL-Break in my brain!

 

[JHB]

This is the bane of my life.

1. Open in design view a form that has a subform.
2. click on the subform
3. a minute or three passes before the subform is selected and active
4. .....

I do not recognize any of the above, (lucky me ).
Is it always the same even if you create (a little) database from scratch?
Is it any differences between if you split a database and not having it split?
What about if other people (other computer) open the database?

 

[pondlife]

You're quite right, this slowness only seems to happen for some forms/subforms and some databases (small or large; mine are always split). Note that affected forms/subforms themselves can work perfectly in use - not slow at all.

But my overall point (like gunslingor and pr2-eugin) is that, despite many years of working with Access, we (/I) have little/no understanding of what processes are taking place in design view between forms and their subforms.

The things you're suggesting seem possible candidates and worth a stab when these symptoms occur - but if we all knew what processes/events are going on in design view we could investigate properly.

Has anyone found a good resource for this?

 

[fantomcatutd]

I have had this problem for years too

Seems we still do not know the cause

Problem detail:
When either in main form in design view or in subform in design view, there is code that repeatedly calculates giving on a slight gap in between calculations in which I can click on a field. Super hard to do anything in these circumstances. Subform is based on complex query, but when all you are doing is editing something, maybe to dekete a field, it should not calculate anything. Anyone ever found the cause of this?

Thanks

 

[Minty]

If you have any timer events on an open form running in the background (even if you aren't actively using that form), then you can get a massive pause when the timer event fires.

But that might not be what you where on about.

 

[fantomcatutd]

No timer events, but the query is based on a select query which in turn is based on a crosstab query.

I know this will slow it doesn't but it does not answer the question: Why is there any calculations going on when in design view on a form or subform?

Thanks

 

[JHB]

..
I know this will slow it doesn't but it does not answer the question: Why is there any calculations going on when in design view on a form or subform?

I can not tell if I've had that problem or not, but if yes, I've somehow got it fixed because, as I said, I can not remember the problem and I could have probably if it still existed in a database I have made.
One solution that has not been mentioned in the thread and which helps in many cases: Is to create a new database and import everything into it.
Is it possible for you to post your database with a few sample data in it, (zip it because you haven't post 10 post yet)? Just to see if others here have the same problem with your database.

 

[CJ_London]

but the query is based on a select query which in turn is based on a crosstab query.

just a thought - have you specified column headings for the xtab? when in design view access needs to analysis the query to determine what field names are available to be bound.

Even if your select query is the recordsouce, the fact it is based on a xtab, won't change that situation.

 

[fantomcatutd]

Will check some of those things, DB data is always large so to post db with less records would remove the problem.

I have several that have the same problem

In the current DB I am using 2 things to get around the problem

1. Remove any non-needed data, seems obvious, but complicated DB has many tables, some going back years that maybe will not be in the output so they are unnecessary. Always keep a backup with all the data, but current DB set to current year only.

2. In this particular DB I am updating on a monthly basis and then creating an accdr file for other users to use. The trick is, I update the tables and then I perform a number of queries in a macro to update everything. In these queries, I include a make table query for each form to be based on. This way, the form is based on a final table and therefore very quick. The macro will run all the queries and then make a table based on the final query. This way the DB does not need to calculate anything when opening the forms.