Full Version: Form Takes Exactly 60 Seconds To Open, First Time Only?
UtterAccess Forums > Microsoft® Access > Access Forms
MCL1981
Riddle me this.
I have a form, bound to a table. The first time you open the form, it takes exactly one minute to open the form. After one minute, the form opens and loads. While the application is still open, you can close and open this form all day long without delay. It is just the first time you open it. I can not find anything that would cause this. The OnOpen event just reads the OpenArgs to determine which form called this form, storingit in an unbound control. It also changes the RecordSource to match the record the form needs to display. The OnLoad event just makes the calling form visible = false.
I have over 30 other forms and 50 something tables. None of them experience this. It isn't network, since everything is on my local disk. And yes I checked for a timer, no dice.
WHY?!?! This is driving me nuts.
MCL1981
Update: I put some msgboxs in the OnOpen and OnLoad to see where it is hanging up in the process of opening. The answer is nowhere. Upon opening the form, either from the nav bar or another form, it sits there with an hour glass for a full minute, then executes the OnOpen and OnLoad without delay. What could cause this form to hang up for a full minute before even beginning to execute code?
jleach
Odd... I've never heard of anything like this, except if there were some timer conflict or the like (which you've said you already checked). I believe (though I haven't verified), that Open should run immediately even if it were a timer issue being run from the form.
Have you tried exporting the form and re-importing it (SaveAsText and LoadFromText), or importing everything to a new DB? Decompile perhaps? General corruption fixes... (backup first!)
Bob G
do you have a lot of dlookups and such on the main form ?
RAZMaddaz
MCL1981,
I have no idea what the problem is, but I have a question or two and a suggestion.
You said that the Tables are all on you local disk drive, so I assume the is a FE/BE system you are working with? If so, you might want to simply remove the Links and then restablish the Links.
Have you ever opened the Table and run Compact and Repair for this Table?
RAZMaddaz
EDIT: Sorry everyone!
MCL1981
In response to all, here's things I've tried.
regularly decompile, compile, compact & repair. No effect.
Ocopied the form controls and code to a new blank form. No effect.
I imported everything into a new blank database. No effect.
There are NO dLookups on the form. In fact, there are no dLookups anywhere. I wrote my own pretty little lookup, that I called mLookup (m for Matt, my name). In the mLookup module, it creates a quick little recordset to lookup the value you want. Includes handlers for nulls and other things that bomb out dLookups. Works much faster and more efficient than dLookups. But anyway, there are no mLookups either. The only thing it does when the form loads is look at the OpenArgs and set an unbound control on the form. I even commented out all of the onOpen and onLoad code. It still does it.
It is indeed a BE/FE. Everything is relinked frequently. I have the development FE/BE on my local disk. When I put it out for use by the users, the tables get re-linked to the BE on a network drive. It makes no difference whether the table is linked to the local disk or on the network.
MCL1981
Ok, so I think I fixed it, although I didn't actually fix anything. The only thing that seems to have made the problem go away was actually deleting the linked table, then re-importing it as a linked table. It has not done it since no matter how many times I try. No rhyme nor reason why this would made a difference, but oh well.
RAZMaddaz
Awesome!!! Glad you got it working and that WE all could help you!!!

RAZMaddaz
MCL1981
Nevermind. Still happening. I guess it was just trying to trick me. I don't know what else to do with this thing.
Bob G
CODE
The only thing it does when the form loads is look at the OpenArgs and set an unbound control on the form.

Can you hard code what the openargs are?
Just trying to eliminate things for you
MCL1981
I completely commented out all the code in OnOpen and OnLoad. Same problem. The problem appears to be before OnOpen even fires.
Bob G
is the table that the form is bound to something that is related to many others ?
Jeff B.
You initially indicated that the form is bound to a table. Does that mean that Access is trying to "load" all of the table's records "into" the form the first time you open it?
That happens if you:
*create a query that only returns ONE of that tables records (think something like "WHERE xxxxxID = nnnn", using your ID field and one of your record IDs)
*bind the form to that query
?Still have the performance issue?
MCL1981
The form's record source (as set in design view) is "Select * from tbl_Projects where 1=2;". This way, it is not trying to load anything before all my code executes. The onOpen event changes the recordsource to "Select * from tbl_Projects where ProjectID = " & CPID & ";". I have all my forms like this and works flawlessly. This has worked flawlessly too up to now on this form. And after you load something once, it continues to work flawlessly until the next time you open the application.
Yes, tbl_Projects has lots of relationships.
Bob G
if the tables are linked have you removed the links and relink?
MCL1981
Yes. Many times, no effect.
also just tried deleting 50% of the records in the table to see if it was getting too big. Took it from 700 records to 300 records and no effect.
What kills me is that it's exactly 60 seconds. Like something is doing this on purpose by accident.
RAZMaddaz
Just a stupid suggestion...
If you create a blank, unbound Form and have this unbound Form be the Form that Displays when the database first opens does it open any faster? If so, then create a button on this "welcome" Form and have it open the Main Form and see if opens any faster.
RAZMaddaz
MCL1981
It isn't being the first form to open unfortunately. There is a home form, a project list form, and about 30 other forms you could go to instead that work fine. You could open this problematic project form after an hour of doing other things and it still hangs up.
GroverParkGeorge
Two further things to consider. First, is the first bound directly to a table, or to a query? Sorry if I missed where you indicate that already, but I didn't pick it up on a quick scan. If it's a query, how does that query perform outside the form?
econd is there a control on the form which is bound to a non-existent field (i.e. the field was there at one point and later deleted from the form's recordset). Or, is there a calculated control on the form?
MCL1981
It is bound to the table directly. I can open the table itself from the nav bar with no delay. There are queries and list boxes that use data from this table long before the form is even opened. It does not appear to be related to the table itself. There are no orphaned controls either.
Jerry Whittle
Do you have Compact on Close enabled? If so, try disabling that function.
The first time Access runs a query, it takes longer (but not 60 seconds usually) so that Access can optimize the execution plan for that query. Then it should run much faster in the future. Possibly doing a Compact on Close might removed the stored statistics.
It's also possible that you may have some code that changes the query which populates the form. Depending on how the query changes, Access could see it as a different query and need to optimize it.
MCL1981
Nope. I definitely have that turned off.
The form's record source (as set in design view) is "Select * from tbl_Projects where 1=2;". This way, it is not trying to load anything before all my code executes. The onOpen event changes the recordsource to "Select * from tbl_Projects where ProjectID = " & CPID & ";". I have all my forms like this and works flawlessly. This has worked flawlessly too up to now on this form. And after you load something once, it continues to work flawlessly until the next time you open the application.
GroverParkGeorge
Well, no, the form isn't bound directly to a table. "The form's record source (as set in design view) is "Select * from tbl_Projects where 1=2;". "
That's a SQL statement, executed against the table even though it's not saved as a named query. You are also creating a different SQL statement, "... event changes the recordsource to "Select * from tbl_Projects where ProjectID = " & CPID & ";". "
Sounds like Jerry's on the right track there, to me.
Try hard-coding in a value for CPID. Don't change the recordsource on open. See if you still get the delay.
ToddW
I assume you have a key or at least an index on ProjectID? Not sure if you said you did this already, but if you create a query with the same SQL as what you're changing the record source to, then open that query, does it take just as long?
MCL1981
Follow-up to this. I was never able to determine exactly what was causing this. I wiped out and rebuilt a lot of code involved. I didn't functionally change how anything does anything. But it works fine now.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.