Consulting

Results 1 to 6 of 6

Thread: Stop Users Modifying Database

  1. #1
    VBAX Newbie
    Joined
    May 2021
    Posts
    3
    Location

    Stop Users Modifying Database

    Quote Originally Posted by HowardKaplan View Post
    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.
    This is exactly what I have been looking for! I have two databases at separate locations but on the same network. They are not split because I have 20 users between the two locations approx 10 per database. They are mainly in the database one at a time inputting maintenance entries etc. I am constantly updating the database multiple times a day so splitting it or creating an "Executable" is not a practical option at this time. I need to know when someone attempts to enter design view because some people discovered they can add existing fields that I have removed. I have a Globals.Logging activity tracker that logs each user by login for every form that they access throughout the database (queries have been converted to forms as well). I need to know who, what, where, and when...

    I am currently playing with this code and I am close to what I need but I am missing something. When I attach to my activity log Global.Logging "Design View" I get the loop of the text "Design View" in place of the form name like I'd like but it repeats wheather its in Form View or Design View and it does sometime prevent me from re-accessing the form I am testing on.
    I added this to the On Load:

    Dim J As Long
    Do
    J = J + 1
    If ((J Mod 100) = 0) Then
    Globals.Logging "Design View"
    End If
    DoEvents
    Loop

    I know this is an older thread but any assistance you can provide would be greatly appreciated! I finally see a light at the end of the tunnel! I have been researching this for a long time before finding this piece of treasure...
    Please, I hope someone is still out there that can lend a hand.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It appears that Howard is not active on the forum as those 2 seem to be his only posts.
    Due to health issues I cannot provide my normal assistance.
    However I can offer some ideas for you to explore.
    One point that was missed is that when you select Design View it triggers the Form's "on Unload" event, which might be possible for you to run a Module from.
    The another point is that although the me.currentview may not work there are a whole host of things available in the Forms Collection.
    See
    https://docs.microsoft.com/en-us/pre...ectedfrom=MSDN
    So using me. may not work within the form but using the formal forms name may provide data to another form running in the background in invisible mode.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location

    Stop Users Modifying Database

    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

  4. #4
    VBAX Newbie
    Joined
    May 2021
    Posts
    3
    Location
    Quote Originally Posted by OBP View Post
    It appears that Howard is not active on the forum as those 2 seem to be his only posts.
    Due to health issues I cannot provide my normal assistance.
    However I can offer some ideas for you to explore.
    One point that was missed is that when you select Design View it triggers the Form's "on Unload" event, which might be possible for you to run a Module from.
    The another point is that although the me.currentview may not work there are a whole host of things available in the Forms Collection.
    See
    https://docs.microsoft.com/en-us/pre...ectedfrom=MSDN
    So using me. may not work within the form but using the formal forms name may provide data to another form running in the background in invisible mode.

    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.

    Thank you, I will explore this today! I appreciate your quick responses. I will definitely explore the Forms collection link you posted. I will play with the replacement of Me.

    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
    No, actually I wasn't calling from outside of the containing form yet. Was first attempting to understand the workings of the code, not sure I understand how to call the form when I tried it the first time. I then attempted to see if I could get any type of result in the Navigation form I am inspecting, and I did, which is very optimistic:

    Below code is in Maintenance Form that I'm not sure about which opens before the Navigation Form (Loop Code) that I am testing to see if is switched to Design View.
    Sub LoopTest()
    Form_NavigationF.BigLoop or Form!NavigationF.BigLoop
    End Sub

    I will try it properly was trying to get an understanding first but probably sabotaged myself doing so...
    And also, I don't need a repetitive loop per se just a one time notification of the event.

    If I get this working I will then write a procedure to send me an email in real-time when this occurs

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Good luck, sorry I couldn't be more help.

  6. #6
    VBAX Newbie
    Joined
    May 2021
    Posts
    3
    Location
    Quote Originally Posted by OBP View Post
    Good luck, sorry I couldn't be more help.
    No, your info is valuable thank you. I will let you know my final results when completed if I’m successful. I think many people will find this useful.

Posting Permissions

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