Auto Logout of my Database

[TBC]

CBrighton - I dont the database to flash the warning but the database stayed open, what other step might I be missing?

 

[TBC]

I'm now able to get the flashing warning. could I be missing another step?

 

[TBC]

CBrighton,

Thanks again for all your help.
Everything is working except the last part. The form opens up and warns the users, but doesn’t automatically close

[/FONT][/COLOR][FONT=Times New Roman][SIZE=3] [/SIZE][/FONT][COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        ' Count down variable is true so warn[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        ' the user that the application will be shut down[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        ' in X number of minutes.  The number of minutes[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        ' will be 1 less than the initial value of the[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        ' intCountDownMinutes variable because the form timer[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        ' event is set to fire every 60 seconds[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        intCountDownMinutes = intCountDownMinutes - 1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Warningform:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        DoCmd.OpenForm "frmAppShutDownWarn"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Forms!frmAppShutDownWarn!txtWarning = "Please save all work and close the database ASAP."[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        If intCountDownMinutes < 1 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            ' Shut down Access if the countdown is zero,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            ' saving all work by default.[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Application.Quit acQuitSaveAll[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit_Form_Timer:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Exit Sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Err_Form_Timer:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Resume Next[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub

 

[TBC]

Thanks CBrighton I was able to get it to work by adding a quit maro on the timer

 

[CBrighton]

The final If statement has the quit:

If intCountDownMinutes < 1 Then
[COLOR=black][FONT=Verdana]            ' Shut down Access if the countdown is zero,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            ' saving all work by default.[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Application.Quit acQuitSaveAll[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        End If[/FONT][/COLOR]

Where did you add a new quit?

 

[MrsSpalla]

CBrighton,
I've used this in my database for an auto kick out. I have the switchboard running in the background, and I can get the warning to come up. However, the countdown doesn't seem to be working. It never changes from 3, and it never actually shuts the database down. Is there something I need to do to make actually shut down?

 

[CBrighton]

To confirm, you have used the below post as your basis for adding the shut-down?

VBA code for 2 forms, the switchboard (or any form which is always open and is opened immediately when the database is run) and a second for a form designed to display a warning message to the users.

Obviously you will also have to change the file path of the file which it is checking for.

The end result should be if the file path you use is not located (i.e. if there is not a file with that exact name in that folder) then the countdown should begin next time the timer event is fired for each user. At the ssme time it should display a messagebox and close the database if someone tries to open it while that file is missing / renamed.

Note that the code responsible for changing the countdown minutes is in the OnTimer on the switchboard, I've bolded the line of code which changes the integer value of the countdown.

Code is all on the switchboard (Timer interval 60000):

Private Sub Form_Open(Cancel As Integer)
    ' Set Count Down variable to false
    ' on the initial opening of the form.
    boolCountDown = False
    Dim strFileName As String
    strFileName = Dir("[URL="file://\\Fillgpet03a005\D_credrisk0001$\Staff"]\\Fillgpet03a005\D_credrisk0001$\Staff[/URL] Database\Archive\Enabled.db")
    If strFileName <> "Enabled.db" Then
        MsgBox "Database being updated, please try again later.
        Application.Quit acQuitSaveAll
    End If
End Sub
 
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("[URL="file://\\Fillgpet03a005\D_credrisk0001$\Staff"]\\Fillgpet03a005\D_credrisk0001$\Staff[/URL] Database\Archive\Enabled.db")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "Enabled.db" Then
            ' The check file is not found so
            ' set the count down variable to true and
            ' number of minutes until this session
            ' of Access will be shut down.
            boolCountDown = True
            intCountDownMinutes = 3
            GoTo Warningform
        End If
    Else
        ' Count down variable is true so warn
        ' the user that the application will be shut down
        ' in X number of minutes.  The number of minutes
        ' will be 1 less than the initial value of the
        ' intCountDownMinutes variable because the form timer
        ' event is set to fire every 60 seconds
[B]        intCountDownMinutes = intCountDownMinutes - 1[/B]
Warningform:
        DoCmd.OpenForm "frmAppShutDownWarn"
        Forms!frmAppShutDownWarn!txtWarning = "Due to database maintenance this application will automatically shut down in approximately " & intCountDownMinutes & " minute(s).  Please save all work and close the database ASAP."
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
            Application.Quit acQuitSaveAll
        End If
    End If
Exit_Form_Timer:
    Exit Sub
Err_Form_Timer:
    Resume Next
End Sub

The filepath & name will obviouslly need to be changed, you will also need a form called "frmAppShutDownWarn" with a textbox control called "txtWarning".

Personally I also have a timer event on the warning form with an interval of 800 (and a hidden textbox called "Text2"):

Private Sub Form_Timer()
If Text2 = "1" Then
    Me.Detail.BackColor = 65535
    Text2 = "2"
Else
    Me.Detail.BackColor = 255
    Text2 = "1"
End If
End Sub

This causes the warning message to flash between 2 bright colours to ensure it's noticed.



The downside of timer events is they only work when Access is ready. If it's displaying a messagebox or an inputbox, etc then the timer event will not fire and the database will remain open for that user (although the event will start it's 3 min countdown when Access becomes ready if the filename differs).

 

[MrsSpalla]

Yes, I have used the below post. I have the frmSwitchboard set with an autoexec macro to run and hide on database launch. I have the path changed to my correct location.

To test I shut the database down, changed my check file name, then tried to open the database. I got the warning stating that it was under maintenece and the database shut down. So that part is working.

I'm just stuck on the countdown actually counting down and kicking out.

It will flash the message, but it just keeps flashing and never exits.

I downloaded the file that you had posted with the "autoshutdownexample.mdb" to see how that worked. And I have the same issue in there, it just won't shut down.

 

[CBrighton]

All I can suggest is some trial & error debugging then.

My starter point would be to add some messageboxes into the code so that I can see which part of the code is being run and which If statements are being correctly applied.

They don't have to be complex, just something simple like

msgbox "If statement 1 true"

:edit:

Also, I have only tested this code in Access 2003, no idea if it works on older / newer versions.

FYI I only tweaked existing code to create this too, the original code was from here: http://support.microsoft.com/kb/304408

IT lacks some details like my attention grabbing warning form which flashes different colours to attract attention, but you may have more luck using that as the base of your code.

 

[MrsSpalla]

Beautiful! Thank you for the link. It had what I needed to make this run. I'm a super beginner at VBA and codes so, whew, this has been a project. Totally appreciate your help!

I kept your fancy flashy warning form, but here's what I changed the switchboard code to:

Option Explicit
Dim boolCountDown As Boolean
Dim intCountDownMinutes As Integer
Private Sub Form_Open(Cancel As Integer)
    ' Set Count Down variable to false
    ' on the initial opening of the form.
    boolCountDown = False
    Dim strFileName As String
    strFileName = Dir("C:\Documents and Settings\dspalla\Desktop\enabled.db")
    If strFileName <> "Enabled.db" Then
        MsgBox "Database being updated, please try again later."
        Application.Quit acQuitSaveAll
    End If
End Sub
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("C:\Documents and Settings\dspalla\Desktop\enabled.db")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "enabled.db" Then
            ' The check file is not found so
            ' set the count down variable to true and
            ' number of minutes until this session
            ' of Access will be shut down.
            boolCountDown = True
            intCountDownMinutes = 3
        End If
    Else
        ' Count down variable is true so warn
        ' the user that the application will be shut down
        ' in X number of minutes.  The number of minutes
        ' will be 1 less than the initial value of the
        ' intCountDownMinutes variable because the form timer
        ' event is set to fire every 60 seconds
        intCountDownMinutes = intCountDownMinutes - 1
        DoCmd.OpenForm "frmAppShutDownWarn"
        Forms!frmAppShutDownWarn!txtWarning = "This application will be shut down in approximately " & intCountDownMinutes & " minute(s).  Please save all work and exit immediately."
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
            Application.Quit acQuitSaveAll
        End If
    End If
Exit_Form_Timer:
    Exit Sub
Err_Form_Timer:
    Resume Next
End Sub