How to use VBA to detect change from Form View to Design View

[hkeiner]

It would really help is you could give a more detail expliation of what you want to do

I am developing a database that will be used by my audit staff to prepare both standardized and non-standardized work papers (forms). A form designated as "standardized" should not be changed/customized by the user, while any form NOT designated as "standardized" can be changed/customized by the user as needed. Each user will start an audit with a fresh copy of the database with no records and then enter data and/or customize some of the forms as appropriate.

More specifically, my desired GUI is have a pop up (message box) appear when a "standardized" form is changed to layout view telling the user that they should not change/customize it and then perhaps close the form automatically. Any forms that are not designated as "standardized" can be changed by the user (in layout view) without any message or other action by the VBA code. I thought that having the VBA code attached only to the "standardized" forms would be a way to distinguish and control the forms that should not be changed/customized.

Further, the method I implement does not have to be totally secure. It is just a way to reduce the likelihood that users make inadvertent or improper non-compliant changes to the "standardized" forms while still allowing them to change/customize all other forms in the same database.

Thanks for your interest in my project...

[HiTechCoach]

Thank you for the additional information. That really helps.

I use three options to hanlde this:

1) The option I prefer to use is to have two apps (front ends). One that is compiled into and mde/accde to prevent modifications. The other one is just for customized stuff. It only included things the user can modify without any restrictions. Both front ends are linked to the same data (back end). Very simple to implement.

2) If your database is in the JET (.mdb) format then you could use Work Group Security to do what you want. It allows you to assign permissions to objects. Works well but can be very complex to implement if you are new to Window/Active Directory style security.


3) If two apps are not possible then I have used a custom form that looks like the Database Window. It will only allow a user to enter into design mode on objects where they have design permissions. They do not have direct access to the database window/navigation pane. This is not as user-proof as the previous two options. It will work withe an ACE (.accdb) database format.

Let me know which option you want to try. I can post some additional information about that method.

[hkeiner]

3) If two apps are not possible then I have used a custom form that looks like the Database Window. It will only allow a user to enter into design mode on objects where they have design permissions. They do not have direct access to the database window/navigation pane. This is not as user-proof as the previous two options. It will work withe an ACE (.accdb) database format.

We use Access 2007 and the database format is ACCDB. Option 3 seems the best choice for me as I prefer to have everything contained within one database file (as a single solution) rather than split things up between multiple files (front ends/back end). Your option 3 states that "I have used a custom form that looks like the Database Window. It will only allow a user to enter into design mode on objects where they have design permissions." Could you explain how you implement this? Sounds like a solution that would work for me.

More specifically, my desired GUI is have a pop up (message box) appear when a "standardized" form is changed to layout view telling the user that they should not change/customize it and then perhaps close the form automatically. Any forms that are not designated as "standardized" can be changed by the user (in layout view) without any message or other action by the VBA code. I thought that having the VBA code attached only to the "standardized" forms would be a way to distinguish and control the forms that should not be changed/customized.

Is the desired GUI I describe above definitely not doable? I ask so that I can stop spending time trying to figure this out if it is definitely not possible.

[HiTechCoach]

Is the desired GUI I describe above definitely not doable? I ask so that I can stop spending time trying to figure this out if it is definitely not possible.

I am not sure if it is even doable. Even if it is, it will be very easy to get around. As long as users have the ability to get to the navigation pane in an .accdb they can do anything they want. You have now way to control the user once in the navigation pane.

The only reliable way to lock down forms and reports in a .acccdb is to compile the database into a .accde. That is why I use two front ends. It takes less than 15 minutes to set it up. All other options take hours or possibly days to implement.

We use Access 2007 and the database format is ACCDB. Option 3 seems the best choice for me as I prefer to have everything contained within one database file (as a single solution) rather than split things up between multiple files (front ends/back end). Your option 3 states that "I have used a custom form that looks like the Database Window. It will only allow a user to enter into design mode on objects where they have design permissions." Could you explain how you implement this? Sounds like a solution that would work for me.

To get you started see: http://www.accessjunkie.com/Pages/faq_13.aspx

You must also disable the navigation pane otherwise the user have full control.