Help my database keeps shutting down (1 Viewer)

[mounty76]

Hello everyone! I must have read most of your messages before I think!

I'm new to VBA and having some issues with some code I got :banghead:

I've got some code to check the name of a file on my harddrive, if this file name changes then it opens up a warning form giving 1 minute before access closes. I've done this as I'm about to start using the db that will have about 35 FE users working to 1 BE db, I have written a BAT file to change the name of this .ozx document so that it shuts everyone db down before it runs a backup and compact&repair on all the FE db.

Problem is even after the file name has changed back to what it should be the db still keeps running this code for some reason and the db keeps shutting down....I'm lost so any ideas would be greatly appreciated! My code I have is below:

Option Compare Database
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
End Sub
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("C:\Users\decap\Desktop\French Training Calander\New UK System\test.ozx")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "test.ozx" 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 = 2
        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 "frmAppShutDown"
        Forms!frmAppShutDown!txtWarning = "This application will be shut down in approximately " & intCountDownMinutes & " minute(s).  Please save all work."
        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

 

[isladogs]

hello and welcome to AWF

I use a slightly different approach.
Have s table in the BE called tblKickout or similar.
The table has 1 Boolean field Kickout and 1 record.

You need a hidden form when runs at start up and which monitors the field value on a timer. Say every 30 seconds.

Set the field True when you need to close the db and start the countdown procedure
Personally I would suggest a longer time e.g 5 minutes

This approach allows you to cancel shutdown by resetting the Boolean field to false

I realise this doesn't directly answer your question but I think its a better method.

 

[mounty76]

hi ridders

Thanks very much for you reply, this is an option but I really want to be able to have task scheduler run the BAT file automatically every night, can I change the field in the BE using a batch file? If not then this wouldn't help, thanks for the info though and I'll try it if no luck with this

 

[isladogs]

Yes you could run it automatically using Task Scheduler

For example, have a separate utility database AutoKickout.accdb which runs an update query to set the Kickout value = True then closes automatically.

Then run the utility using Task Scheduler at a specified time eachy night.

I also have another utility called UndoKickout.accdb which reverses the above

BTW does your code RECHECK the name of your file once the countdown has started?

 

[mounty76]

Ah maybe that's the way forward then, would you put the update query into the BE or have another db run an update query to the BE? If so how do you have one db run an update query to another db? Presumably you'd just put the update query on an autoexec macro then have the BAT file open it and close it? If this is possible then I think this might be a better way around it

 

[isladogs]

Use the external db to close your main db

The attached should explain what I mean

Hold the Shift key down so you can open it without running the code

 

[mounty76]

Ah I see so basically just have another FE db called 'autokickout' with an autoexec to change the field to True then all the other FE db have a hidden form that monitors the field value

 

[isladogs]

Yes that's basically it....

 

[mounty76]

HI Ridders, sorry to be a pain! I've set it up with the form to check and refresh the table field and ive done the update queries, I'm very new to vba so any chance you could help with some code to open a form when kickout field = true, then after 1 minute close access? the code I have for checking the field value is:


Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 30000
End Sub

Private Sub Form_Timer()
Me.kickout.Requery
Me.Refresh

End Sub


any help would be much appreciated! Thanks again

 

[isladogs]

HI Ridders, sorry to be a pain! I've set it up with the form to check and refresh the table field and ive done the update queries, I'm very new to vba so any chance you could help with some code to open a form when kickout field = true, then after 1 minute close access? the code I have for checking the field value is:

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 30000
End Sub

Private Sub Form_Timer()
Me.kickout.Requery
Me.Refresh

End Sub

I assume this is the hidden form and that you have bound the form to tblKickout. Also that you have a textbox on this form called Kickout.

Correct so far?

Next you need (or already have) a form to warn users that the program is about to close. I've called this frmLogoutTimer below
I still think 1 minute is a unreasonably short time if they are doing something complicated.
Also what if they have left their desk briefly or clicked on email or whatever? If so they may not see the message until Access has closed.
Just how unpopular do you want to be?

Surely you can wait 5 minutes?

Another thing I recommend is to send an email to all users warning them the db is about to close

Anyway modify the hidden form timer event like this

Private Sub Form_Timer()
Me.kickout.Requery ' probably not needed but you'll need to check
Me.Refresh 'definitely not needed - its hidden!

If Me.Kickout = True Then DoCmd.OpenForm "frmLogoutTimer"
End Sub

Now your timer is set to 30s (30000) and that's also going to be true for other users. However each users computer will trigger the event at their own time so allow at least 30s more for everyone to be kicked out

==========================
Now you need to code frmLogoutTimer:

In declarations section add this
Dim N As Integer 'number of seconds till closedown

Private Sub Form_Open(Cancel As Integer)

N= 300 '5 minutes (or whatever you choose as your countdown time)

End Sub

You also need a timer event in your logout form
Interval = 1000 (1 second)

Have this set to count down the time & when it reaches zero, Access will be closed

Private Sub Form_Timer()

N=N-1

If N=0 Then Application.Quit

End Sub

Hopefully that's covered everything.
You can add bells & whistles later ...

Oh yes - you also need to stop new users logging in whilst Kickout = True
The easiest way is to check it as the db loads then immediately display another form frmLocked to explain the situation & close the db after say 10 seconds

BTW - have you thought about how you can use the program whilst it is locked?

 

[mounty76]

Thanks very much for this, a great help, you were correct with your first statement and yes I do have a form to open as a warning. I don't think it'll be an issue using it whilst it's locked as it will only be the BE being backed up via BAT file. If I need to do any maintenance then I'll just email the users. Thanks again, I'll try this out in a minute. Cheers

 

[mounty76]

Hi Ridders, the first code worked to open the frmlogouttimer but the second code didn't work in that it didn't shut the db down?

Thanks for the heads up on locking the system for anyone trying to open it when the field value is true

 

[isladogs]

Hi

Attached is an example showing how I do it ...
I've removed all the code specific to my databases so you should be able to import it all into your db if you like it

When you open the db, form frmLogoutTimer appears
This would normally be hidden and running in the background
i.e. normally you would load this using autoexec macro

Tick the Kickout box then click Run Now to trigger the next step

Form frmLogoutStatus opens and starts counting down
- its deliberately very 'LOUD' so users can't miss it!
I've set it to count down from 5 mins but you can change that.
Users can click either:
a) Click Quit ... to do just that
b) OK to dismiss the form & carry on working
....BUT it reappears every minute & then more frequently in the final minute

Reopen the db with Kickout = True
Form frmLocked appears and counts down for 10 seconds then quits

To unlock the db again, either
a) open it using the Shift key then set kickout =false
b) use a separate UndoKickout.accdb to do this - similar to the example I gave you yesterday

Hope that helps

 

[mounty76]

Thanks very much! I've got it cracked now, thanks again for all your help. I have one last one for you!! I've made another form to kick out people that log in whilst the kickout value is true, I've put an If statement in the autoexec macro to check if kickout is true then open this form....But I cannot get the If condition to find the value? I've used [kickout].[kickout]=True (doesn't help i've used same name for table and field!), anyway this doesn't seem to work and is coming up with an expression error when I run it? any ideas?

Thanks again

 

[isladogs]

I included that part in my database I uploaded
Don't do it from autoexec - see how I did it

 

[mounty76]

Amazing, just what I was after! Thank you very very much for all your help....am ready to publish and go live now with this db, thanks again, much appreciated, have a good weekend!

 

[isladogs]

You're welcome.
Hopefully you can add any bells and whistles yourself


Sent from my iPhone using Tapatalk

 

[mounty76]

Hi Ridders,

Sorry I thought I sent my last post but it didn't send...dodgy internet signal on a ship! That worked fantastic! Thanks very much for your help, perfect solution!!! Have a good weekend, thanks again