Closing a Dynamically Changed Report

[EddieN1]

I am using Access 2007 in a multi-user mode. I have some reports that are modified dynamically based on user options. When the Print Preview closes, it prompts with a "Save/Cancel Changes?" dialog box. I can hide this box with a docmd.setwarnings = false when I open the report followed with a docmd.setwarnings = true when the report closes. However, the default answer to the hidden dialog box is "Save". If there is more than one user in the database (and there almost always is) then I get a "You don't have exclusive access to the database" dialog box. I would like to either (a) default the hidden "Save/Cancel" dialog box to "Cancel" or (b) hide the "exclusive access" dialog box. Any help would be appreciated.



Thanks, Eddie

[boblarson]

Wait a minute -

1. What design changes are being made to the report when running?

2. If being used for multiple users (and I would include just all of the time in there too) this needs to be SPLIT where the backend (tables only) is on the network file server and then a COPY of the frontend is on EACH user's machine, period.

3. We'll deal with the dialogs in a moment after finding out more about what design changes are occurring which are causing the dialogs to appear.

[EddieN1]

Thanks for the quick reply...

1. The dynamic changes to the report involves making certain columns visible or not visible based on the user's selections when requesting the report.

2. I do have the database split between a front-end and back-end (tables) database, but I don't have the front-end copied to the user's PC... it is on the same network drive as the back-end.

I have about 20 users with about 5 in the database at any one time. I am still making changes to the database forms and reports so it isn't practical to have each user copy the front end to his/her computer 2-3 times per week. Do you have a technique I could use when a user logs into Access to determine if the front end has changed, and, if so, copy it to the user's PC?

Thanks again, Eddie

[boblarson]

Thanks for the quick reply...

1. The dynamic changes to the report involves making certain columns visible or not visible based on the user's selections when requesting the report.

How are you making those visible or invisible? Are you having it go to design view first or what?

2. I do have the database split between a front-end and back-end (tables) database, but I don't have the front-end copied to the user's PC... it is on the same network drive as the back-end.

Not a good thing at all. Move the copy of the frontend to each user's machine. It will

1. Help avoid collisions with other users.
2. Help avoid corruption (if there is a network disruption, no matter how slight, while a user is in the frontend, it can irrecoverably corrupt it and then everyone can't do their work, whereas if a frontend on a user's machine corrupts, at least everyone else can still do their work while you replace the frontend for the affected user. Time loss is kept to a very bare minimum. And corruption with multiple users working with a shared frontend over a network is highly susceptible to corruption. It usually isn't a matter of IF it will happen but only WHEN it will happen.

I have about 20 users with about 5 in the database at any one time. I am still making changes to the database forms and reports so it isn't practical to have each user copy the front end to his/her computer 2-3 times per week. Do you have a technique I could use when a user logs into Access to determine if the front end has changed, and, if so, copy it to the user's PC?

Funny you should ask. I had the same problem but with over 100 users when I worked at a large healthcare company several years ago. So I created a tool which will enable Auto Updating for any frontend. How it ends up after enabling the auto updating is that your user opens the database, it checks the version number against the version number in the backend table, and then if it doesn't match it notifies the user that their frontend is out of date, it closes itself, deletes the old version, copies the new version from a master location on the file server, and then automatically reopens for the user. The enabling tool is located on this page (and works with any version of Access from 2000 and up):
http://www.btabdevelopment.com/ts/freetools

[EddieN1]

Bob,

Thanks for all your help. I took your advice and your front end version checker tool and everything works great now.

Thanks again, Eddie

[boblarson]

Glad to hear!