Impact of keeping forms open (1 Viewer)

[Acme]

I have a form that takes too long to load so I have resolved to do the following:
After the form is opened for the first time, closing the form from the form nav changes the visibility property, and when you open it again from the switchboard, it opens quickly. It closes when you exit the application. I believe that if the user closes (hides) the form while a record is current, that other users will still have edit access to it and the users will receive a message if they try to save it ("The record has been modified by another user since you last saved it"). Just want to confirm.

Also, I originally tried to open the form acHidden on app launch, but in run time this stopped the application from running. Anyone experience that before? Seemed odd to me since I got that idea from Microsoft. It could be that I just had other issues. Any insights are appreciated.

 

[pr2-eugin]

The main question we need to ask here is - Why is your Form over loaded? What code have you placed under Form Open, Load, Activate, Current? See if you can move them around..

Hiding a Form is useful for Unbound forms.. Bound forms will cause the problem you have mentioned when used in a Multi User environment..

 

[Acme]

It's not like I haven't been working on the main question for over a month YAKNOW
Bottom line is, there is no way around the form load time. On Current, I have a status check to either enable or disable controls. On Open I am going to new or specific record or first record (depending on add, goto or browse user selection). I had to do some moving around so that the control status check did not interfere with the load which crashes the system. The issue is the number of calculated fields some of which include late binding of excel to do calculations. Each of these takes over a second, so that is the rub. I experimented with replacing the functions with a sub. However, this has led to issues, namely, the user experience punching different buttons and possibly forgetting if they did or not and redoing them or forgetting and saving bad data. I wanted to keep the one step refresh option open. Also, the time to hit several buttons, vs the total time to load, is longer, so its a reasonable tradeoff to me. Also, with the subs, I noticed that sometimes they work, and later they don't when there is an excel call involved and this may be an access 2013 issue I don't know. It's still an option, but I am leaning towards keeping the functions. So my bottom line is that if the user has the form open, if others open the same record, and save it, then the first user tries to save it later, they will receive the warning that "the record has been modified by another user". I am ok with this because I have worked with systems that let this happen. Plus, the purpose of the system make the chances of two users modifying the same record low, and if the record is in a completed status, no one can change it.

 

[Pat Hartman]

Why are you automating Excel? Can't you do the calculations in a query or in the form itself?

 

[Acme]

I am automating in excel on some formulas that Access doesn't offer. I shopped around for an Access adding to install and distribute with the run time version but found nothing online. Are you aware of any? Example of formula, SSEReg, in excel, not in access. Would like to have some add-in do this for me. As it is, even though I am using late binding, I am wondering if I will encounter future issues with library references. Thanks in advance

 

[Pat Hartman]

I think FMSINC.com has a statistics package. There may be another company that also offers one but I can't think of their name. Try something like "Database Creations".

Late binding is also adding to the slowness. You only need late binding if you have to work in a multi-version environment.

 

[Acme]

Thanks Pat. I won't know the environment unfortunately. I'll look at those vendors. Hey, let me see if I can stump you. I will also post a new thread, but check out this problem. I have a table (call it A) that holds records that are part of a multiple items optional field in another table (B). Issue for me is, how do I code it so that the items deleted in the A table remove any associated flags in table B?

 

[Acme]

Just checked those sites. Doesn't look promising but I can email them, just worried I might get a sales promise that will cost a bundle. I am doing my error trapping now, and have saved a couple seconds. Also, I was able to keep the original design, open the forms on login as hidden, leave them open when users close them (visible false), and then close them on exiting the app. I am not worried about errors on closing the forms on exit because the forms are set up so that soon as the key field changes from new record to an autonumber, the record is saved with an incomplete status. This makes the user experience much better. Just concerned about the stigma of requiring users to have Excel. Not a very polished bit of news to deliver.

 

[Acme]

In searched around and Found FMSIncs statistics page and sent a query on a version for MSA13. Thanks again.

 

[Pat Hartman]

I have a table (call it A) that holds records that are part of a multiple items optional field in another table (B). Issue for me is, how do I code it so that the items deleted in the A table remove any associated flags in table B?

Fix the problem at the source by getting rid of the multi-value field and using your own 1-many relationship.

just worried I might get a sales promise that will cost a bundle.

I know Luke Chung, the owner of FMS personally. They are an excellent company to do business with and I have been using Total Access Analyzer and a couple of his other tools for years. I have not used the statistics package though.

 

[Acme]

I can't do that, I don't think. The idea is to let someone create a keyword that will call up reports for the records they select. So they create a keyword in table B and select the applicable table A records. If I follow you, you are suggesting I get rid of the applicable records field and replace it with, my own 1-many relationship. I don't understand how that would work. I could put the keywords on a 1-many Table B field, which I considered. It might actually make more sense to do it that way. But then they would have to create keywords on the fly and wouldn't be able to delete table A records because there would be associated records in table B. I don't think I want that. Good idea though.

 

[Pat Hartman]

The multi-value field is implemented behind the scenes as a separate table. The problem is that Access hides the many-side table and doesn't allow you to work with it directly. When you delete a record from the main table, Cascade Delete takes care of getting rid of the related records in the hidden multi-value field table.

When you have a 1-m relationship, you would specify Cascade Delete if you wanted to delete all m-side records when you deleted a 1-side record.