Consulting

Results 1 to 13 of 13

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

  1. #1
    VBAX Regular
    Joined
    Oct 2010
    Posts
    56
    Location

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

    I am trying to add VBA code to a particular form so that the code detects when form's mode is changed from Form View to Design View by a user and then does something special when this happens. Using Access 2007.

    The Me.CurrentView property is supposed to return a "0" if a form is in Design View and a "1" if it is in Form View. I tried the below code but the Me.CurrentView always returns a "1" when the form is changed from Form View to Design View. I tried the code with various form events (as listed under the Events tab on the Forms's properties page) but the Me.CurrentView always returns a "1". I suspect this is because all of the events under the Events tab occur prior to the form changing from Form View to Design view.

    Any thoughts on an alternate way to do this? I know I can disable Design View altogether via various Access Options (e.g disable full menus, disable shortcut menus, etc.) but this is not what I want to do


    [VBA]Private Sub Form_Unload(Cancel As Integer)
    Dim intView As Integer
    intView = Me.CurrentView
    If intView = 0 Then
    'DO SOMETHING SPECIAL
    Else
    'DO NOTHING
    End If
    End Sub[/VBA]

  2. #2
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    I don't believe you can trigger an event to check if it is in design view since when it is in design mode it isn't loaded.

    You don't have to disable shortcuts and full menus you can just disable design view when building the form and then convert to mde.
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  3. #3
    Quote Originally Posted by BrianMH
    I don't believe you can trigger an event to check if it is in design view since when it is in design mode it isn't loaded.
    The form must be loaded to enter design view.Using CurrentProject.AllForms("MyFormName").IsLoaded will return true when the form is in design view.

    Quote Originally Posted by BrianMH
    You don't have to disable shortcuts and full menus you can just disable design view when building the form and then convert to mde.
    Creating an MDE or ACCDE disables design view on forms, reports, and modules automatically.

    Note: If you are using Access in Runtime mode, design view is automatically disabled for all objects, even with an mdb/accdb.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Quote Originally Posted by hkeiner
    Any thoughts on an alternate way to do this? I know I can disable Design View altogether via various Access Options (e.g disable full menus, disable shortcut menus, etc.) but this is not what I want to do
    It would really help is you could give a more detail expliation of what you want to do.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    VBAX Regular
    Joined
    Oct 2010
    Posts
    56
    Location
    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...
    Last edited by hkeiner; 06-03-2013 at 11:49 AM.

  6. #6
    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.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    VBAX Regular
    Joined
    Oct 2010
    Posts
    56
    Location
    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.
    Last edited by hkeiner; 06-03-2013 at 01:14 PM.

  8. #8
    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.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Quote Originally Posted by hkeiner View Post
    The Me.CurrentView property is supposed to return a "0" if a form is in Design View and a "1" if it is in Form View.
    That's generally true, but I discovered an interesting subtlety about Me.CurrentView. If "Me" refers to a subform, and if the parent form is switched from form to design view, then Me.CurrentView still returns "1", but Parent.CurrentView returns "0". Apparently Access will override the way a subform is displayed without changing the corresponding property. This turns out to be important in some circumstances, though it's apparently not relevant to the situation that led to the posting of this thread.

  10. #10
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    You can run code relating to another form that's open in design view but you can't run code from a form in design view (it's code module is unloaded.)
    So with regards to design view, me.CurrentView doesn't really make any sense.


    And regarding the necro'd OP.


    HiTechCoach hit the nail halfway in. Two databases, one protected and used as an add-in within the other which is exactly how the Access Wizards work.

  11. #11
    Quote Originally Posted by jonh View Post
    You can run code relating to another form that's open in design view but you can't run code from a form in design view (it's code module is unloaded.)
    So with regards to design view, me.CurrentView doesn't really make any sense.
    It is possible to run code that is part of a form's module while the form is in Design View or not even open, but the code has to be launched from somewhere else. It's easy to create a demo that shows this. Form1 needs only the following simple code:
    Public Sub BigLoop()
    Dim J As Long
       Do
          J = J + 1
          If ((J Mod 100) = 0) Then
             Debug.Print J, Me.CurrentView
          End If
          DoEvents
       Loop
    End Sub
    When this loop runs, it displays ever-increasing numbers in the Immediate window, each number followed by the value of CurrentView. In addition, there needs to be a non-form module with this code:
    Sub LoopTest()
       Form_Form1.BigLoop
    End Sub
    If this LoopTest routine is run directly from the VBA editor, while Form1 is open in Design View, you can see output scrolling through the Immediate window, and each line of output ends with "0" for design mode. If the same experiment is tried while Form1 is open in Form View or is not even open at all, each line ends with "1". The code in Form1 can run without Form1's being open in Form View.

    Where this becomes important is if a form has a loop running for which the termination condition cannot happen if the form or its Parent is switched back to Design View, because the termination condition depends on a button press. The loop may continue to run indefinitely, and that can cause problems during development, though perhaps not after deployment. I tried to create a tiny model demonstration of such a circumstance, with Form1 as a subform of another form, but the attempt to fetch the CurrentView property caused a VBA error trap whenever the Parent's view was changed. However, I've seen the code continue to loop without error in a larger project; I'm not sure what accidental subtlety of the larger project I'm missing in the tiny model.

  12. #12
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    You are still calling the public procedure from outside of the containing form. ?
    And switching to design view should fire the close and unload events. I haven't tested it but I would expect that to cancel any loop (of course a loop will lock the form unless you've used doevents and that would be a design rather than a user issue).

    I'm not seeing a real world problem here. I'd be interested if you do have an example.

  13. #13
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    This old thread is now closed

    Two new posts have been moved to: http://www.vbaexpress.com/forum/show...215#post409215
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •