Long Delay When Saving Changes to Form Design or CHanging from Form Design to Form View

[Micron]

Something's not adding up. To me this

I save a change to the form design

means you alter the label, click save on the ribbon or do ctrl+s to save and it takes minutes to save that change. That does not indicate a data problem. If you switch from design to form view and the switch to form view takes minutes that is something else, and it's likely because of the form data source. Perhaps too complicated, contains a bunch of aggregate functions, etc. In the latter case I'd be opening the query that is the recordsource of the form and see how long it takes to finish running. Don't make the mistake of thinking that the query is finished running as it may only access a partial set of records to try to cut processing time. If you hit the last record navigation button and the query runs again, it wasn't finished. If you jump to the last record, it was.

So if you open the main query (assuming there is one that is the form recordsource) and it takes a long time, that should be the primary issue. Sounds like you have several nested queries that make up the final query (known as a stacked query) for the form recordsource, so the issue could be any one them, or more than one due do compounding the problem. Perhaps review



http://allenbrowne.com/QueryPerfIssue.html

[petes1505]

I think I may have stumbled upon the issue. As I mentioned there are 10 tables linked to 10 csv files. I created a query called qry_FlatFile which includes all of the fields from the 10 linked tables that will be needed for all of the other queries in the database.

In the new database I started importing the queries from the previous database one at a time stopping to check how long it took to save a design change after each import. It worked well until I imported the qry_FlatFile, then it took about 8 minutes to save a change. So it is something in that query that is causing the issue.

[petes1505]

means you alter the label, click save on the ribbon or do ctrl+s to save and it takes minutes to save that change. That does not indicate a data problem. If you switch from design to form view and the switch to form view takes minutes that is something else, and it's likely because of the form data source.

In both scenarios it is taking 7-10 minutes, either to save or change to form view.

In the latter case I'd be opening the query that is the recordsource of the form

There is no single recordsource assigned to the form. I'm not sure what you mean by the statement. The form includes more than 30 different queries. Can you clarify what you mean by the "recordsource of the form."

[Micron]

in design view make sure the form is the selected item. Look at the property sheet (Data tab) and you'll see what the record source property is for the form. You can only have one object listed on that line so I don't get why you think you have 10. If there are 10 tables and/or queries in a query design view, those 10 items (domains) are part of that query, but it's one query. You don't say the form is based on 10 queries. IF you have a main form and 9 subforms, each of those 9 subforms are separate forms with, again, one record source each. The main form still only has 1 recordsource property value.
If still not clear, let us know.

EDIT - If the form has no recordsource property then it is an unbound form and all controls (textboxes, listboxes, combos) are not bound to any table fields. If that's what you mean by the form not having a recordsource and it shows records when you open it, then you are setting the form recordsource property in code (when it opens) or you're using only vba to populate controls and save new and edited records. Is that the case?

[petes1505]

What I have is more like a splash page. When Access is opened that splash page (form) opens. On that page is a number of buttons, each one when clicked will run a different query. When I look at the splash page (form) properties, the Record Source field is blank and the RecordSet Type is Dynaset.

[petes1505]

Micron,

See my previous response. All I have is a simple splash page with multiple buttons, each when clicked will run a specific query and display the results in a datasheet view on a separate tab. That's it. There is nothing more to the database.

[Micron]

Sounds like you have a default opening form that you're using as a switchboard. You didn't create this using the Switchboard Manager wizard by chance?

A splash form is one that simply presents some info about the db (e.g. maybe version number and name of the db or the process it supports) & maybe some eye candy. Often used to give users something to look at while things are checked in the background.

In any event, post 10 seems to be a bit misleading and has caused us to wander down the wrong paths. It appears your form isn't bound; it's only a switchboard form thus you should not say it contains 10 queries. I assume it's more revealing to say it only contains 10 buttons (+ maybe labels) each of which opens 1 query. Those queries would not 'be on the form'. At this point I don't know what to suggest other than a) see what happens if you repeat these tests on a local copy of the db (no vpn involved) or b)copy, compact and zip the db and post it (and the back end if applicable) and we can try to replicate the issue. Something tells me that b) is not an option for you.

Last thought I had before submitting this response is to wonder how many controls you have on this form. How many would cause slow performance would depend on other factors so I can't suggest a number. Rather than have umpteen buttons and labels to run queries, I'd use a combo to present choices to users and run the one chosen. I'd still use a button (but only one) to execute the query because doing this upon combo after update event would cause accidental choices to run when you don't want them to. Those choices would be listed in a table, so if I added queries, all I'd have to do is add a record to the table and not recode and redesign the form.

[petes1505]

Hi Micron,

Yes, you are correct about having a default opening form, My apologies.....my choice of words to describe what I was using was definitely incorrect.

You are also correct in that option B is a no go....this is a company database so I could not share that information. I still think it has something to do with the one query I mentioned earlier in post 17. As soon as I imported that query into the new database the same issue started to occur. All that query includes is four of the linked tables...let's call them Table A, B, C and D. There is one common field in all of the tables, and I linked that field in Table A to the like fields in tables B,C,D using the join property 2 (show all records in Table A and only those from the other table where the joined fields are equal). It is really a simple query so I don;t understand what could be causing the issue.

[Micron]

So the facts are
- your switchboard form isn't bound (has no record source) and only contains controls for running queries. How many we still don't know but the count probably isn't the issue
- this form is very slow to switch from design to form view when a certain query is in the db queries collection. Without that query it switches normally.
- since the form isn't bound, the previous point should have no bearing on the performance when switching views
- we still don't know what the result would be if the form was re-created from scratch in that new test db (as suggested)

That leaves me with:
- the control limit over a forms lifetime is 255 Deleted controls count. I've never experienced hitting the limit or even coming close so wouldn't know the result of the latter. Recreating the form rather than importing would have eliminated that.
- one or more data controls have expressions that are being calculated when the form opens and that is the issue. Not applicable if such expressions required queries that were missing because of your testing.

So beyond recreating the form to eliminate the aforementioned or possible corruption of the form, I'm out of ideas now. The main problem I have with this is an unbound form doesn't "look at queries" when it loads so there's no reason to be slow because of queries or tables when switching views. The only exception I can think of is a bunch of calculated controls.

Then there is this, in case it will help...

https://www.accessforums.net/showthread.php?t=77482

[petes1505]

Thanks Micron. I will work on recreating the form from scratch to see what happens. As far as controls, there are 28 buttons and each button runs a single query. That is all that is on the switchboard form. I do appreciate your time......I know we've been going back and forth all day. Let me work on recreating the form and once I do that I'll come back and update the post.

Thanks again for the assist.
Pete

[Micron]

I think you should consider the combo if you're going to recreate it anyway. Another option would be just that - an option frame with option buttons. However, 28 option buttons is not much better than 28 command buttons.

[petes1505]

Well, was finally able to resolve the issue. In the Options menu for the Current Database I deselected all "Name AutoCorrect" boxes and selected the "Clear Cache on Close" option. Once I did that it worked perfectly. Only takes a second to save changes and navigate from form design to form view. So, if anyone else is having similar issues it might be worth a shot to make the same changes to see if it resolves the problem.

Thank you to everyone who responded. Your help and time is appreciated.