This works great. Just what I needed for a work database where the guys leave the database open when they go home and I can't work on it unless I unplug their computer. Thank you very much. Well worth a follow.
I have used your code however i cant see what i've done different. my code is exactly the same as yours however when I view the form the counter doesn't start and nothing happens. Timer interval set to 1000 and on timer set to event with the code. are there any behind the scenes settings or references need to be checked?
@Austin, I have implemented this feature in my DB But this feature only works if if my DB is active in the foreground In other words, is my DB minimized or I work in another e.g. Word or Excel windows this feature do not working. My users work with other tools and because it would be nice if it then especially the DB closes. Sorry everything else does not make sense. @Austin, do you have to a solution for this? Thanks
Very nice tutorial. Can you please help me if there is no ActiveForm selected, just like when click outside the form or click outside of the application, I want the timer is also ticking.
Tewan - Excellent video and concepts. I found your videos only a couple weeks ago and have learned a lot so far! My database is split into front-end/back-end. I'm assuming this would need to be in the front-end files. Is that correct? If that is so my Idea might not work - maybe you have another suggestion for this thought: Could this same type of function be used from an "admin" tool to force all users off the database? My thought would be to put a value in a table and have it look at that value. If the value for [status] = Normal no action to be taken - if the value for [status] = Shutdown then log off all users so maintenance could be performed. Table updates, Backup, anything requiring exclusive mode. You could even broadcast a message box telling them the database is being shut down in 15 Minutes etc..... You have provided me inspiration to try some things!! Thanks - Scott
Hi Austin, Thanks for the video, It works great on the Backend version, However when I publish it in a .accde format it does not work? Do you know how I can fix it or perhaps have a video for the front end as well? Many Thanks
great video BUT BE CAREFUL IF YOU HAVE ANY MACRO THAT FOCUS YOU ON AN ELEMENT - that macro will not work because every second the internal focus of system is diverted per this subroutine , I have a seach m,acro that goes to a field and give you a windows for search where you can type in what you are looking for and it stopped workign until I cahnged the time interwal to 60 000 ( 1 minutes) so my macro has one minutes to look for the field
when run, shows error message The expression On Timer you entered as the event property setting produced the following error: Sub o Function not defined This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.
yet another useful video... could you plz help me creating a module for datediff excluding only the holidays table... currently i have a module which excluded sunday an saturday which i do not want... i want to exclude only holiday table dates... thanks
I have another video at ua-cam.com/video/Na4kjr2EiLg/v-deo.html it will count only the working day that exclude the holiday and weekend. there is a code for excluding the holiday. you may modify a little bit from this video.
+austin72406 Thank you for your response, Unfortunately I am not good at vba... I have below code and my weekend is only Friday.. can you please help me with where I am going wrong.. I want only Fridays to be excluded,, plz helpPublic Function Weekdays(ByRef startDate As Date, _ ByRef endDate As Date _ ) As Integer ' Returns the number of weekdays in the period from startDate ' to endDate inclusive. Returns -1 if an error occurs. ' If your weekend days do not include Saturday and Sunday and ' do not total two per week in number, this function will ' require modification. On Error GoTo Weekdays_Error ' The number of weekend days per week. Const ncNumberOfWeekendDays As Integer = 1 ' The number of days inclusive. Dim varDays As Variant ' The number of weekend days. Dim varWeekendDays As Variant ' Temporary storage for datetime. Dim dtmX As Date ' If the end date is earlier, swap the dates. If endDate < startDate Then dtmX = startDate startDate = endDate endDate = dtmX End If ' Calculate the number of days inclusive (+ 1 is to add back startDate). varDays = DateDiff(Interval:="d", _ date1:=startDate, _ date2:=endDate) + 1 ' Calculate the number of weekend days. varWeekendDays = (DateDiff(Interval:="ww", _ date1:=startDate, _ date2:=endDate) _ * ncNumberOfWeekendDays) _ + IIf(DatePart(Interval:="w", _ Date:=startDate) = vbFriday, 1, 0) _ + IIf(DatePart(Interval:="w", _ Date:=startDate) = vbThursday, 1, 0) _
' Calculate the number of weekdays. Weekdays = (varDays - varWeekendDays) Weekdays_Exit: Exit Function Weekdays_Error: Weekdays = -1 MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Weekdays" Resume Weekdays_Exit End Function
VBA has weekday built-in function for you. you can just modify a little bit like mine: Public Function NewWeekdays(startDate As Date, strEndDate As Date) As Integer Dim intCount As Integer Dim strMydate As Date On Error GoTo errHandler intCount = 0 strMydate = startDate - 1 Do While strMydate < strEndDate strMydate = strMydate + 1 If Weekday(strMydate, vbMonday) < 6 Then intCount = intCount + 1 End If Loop NewWeekdays = intCount errHandler: Exit Function End Function then just call it and put the number of weekdays on the text box text6 Me.Text6 = NewWeekdays(Me.Text1, Me.Text3)
I have successfully placed this code in my dbase. However, when I use the msg box code the dbase stays open until someone presses the OK button on the message box. Can it have another timer for the msg box to displaythe message box for only 1 minute and then close without having to click ok on the displayed message box? Initial close time is set to 20 minutes and works fine without the msgbox.
I believe message box requires user input. Your best bet is a popup box. Add this to your code where you want to quit: Dim WshShell As Variant Dim y As Integer Set WshShell = CreateObject("WScript.Shell") y = WshShell.PopUp("The Database is going to close due to inactivity" & vbCrLf & vbCrLf & "Closing in.....", 1, "Good Bye") Set WshShell = Nothing
This works great. Just what I needed for a work database where the guys leave the database open when they go home and I can't work on it unless I unplug their computer. Thank you very much. Well worth a follow.
Fantastic! Clear and effective instructions. Thank you so much!!
Thanks. This works great! I change the code to run in seconds rather than minutes and set the timer to 300 seconds to close after 5 minutes to close.
Amazing video!!! Thank you very much. You have saved my life 🥳🥳🥳
U r really great teacher 😍
excellent tutorial thank you sir.......
This is exactly what i was looking for thank you!!!!!!!!
great explanation, code works in good mode, i had added some features, allowed user to cancel or resume the countdown
Works well, thank you !!!
Absolutely brilliant!
Thanks a lot for video.
Saved life! Thank you so so much.
This helps alot! Thank you! I do have a question, does this code work for a form with multiple layers? That is a form within a form
Thanks, great explanation
I have used your code however i cant see what i've done different. my code is exactly the same as yours however when I view the form the counter doesn't start and nothing happens. Timer interval set to 1000 and on timer set to event with the code. are there any behind the scenes settings or references need to be checked?
same for me, counter never starts, did you get any solution?
@Austin, I have implemented this feature in my DB But this feature only works if if my DB is active in the foreground In other words, is my DB minimized or I work in another e.g. Word or Excel windows this feature do not working. My users work with other tools and because it would be nice if it then especially the DB closes. Sorry everything else does not make sense. @Austin, do you have to a solution for this? Thanks
Hi sir can you please help to include also when open report still it will quit. Thank you sir Teawan. Appreciate a lot on this wonderful video.
Thank you very much,
Beauty speaking thanks
Very nice tutorial. Can you please help me if there is no ActiveForm selected, just like when click outside the form or click outside of the application, I want the timer is also ticking.
Tewan - Excellent video and concepts. I found your videos only a couple weeks ago and have learned a lot so far!
My database is split into front-end/back-end. I'm assuming this would need to be in the front-end files. Is that correct?
If that is so my Idea might not work - maybe you have another suggestion for this thought:
Could this same type of function be used from an "admin" tool to force all users off the database? My thought would be to put a value in a table and have it look at that value. If the value for [status] = Normal no action to be taken - if the value for [status] = Shutdown then log off all users so maintenance could be performed. Table updates, Backup, anything requiring exclusive mode. You could even broadcast a message box telling them the database is being shut down in 15 Minutes etc.....
You have provided me inspiration to try some things!! Thanks - Scott
yes, Scott. you can set up like you said.
Hi Austin, Thanks for the video, It works great on the Backend version, However when I publish it in a .accde format it does not work? Do you know how I can fix it or perhaps have a video for the front end as well? Many Thanks
Question,
This works perfectly, the only issue I have today is if anyone has a Report open the timer never starts. How can I fix that?
Hi Brian, I am having this same issue with a report being left open. Were you able to fix this? If so could you help me with this?
@@donnahamby1557 me too hope sir Tewan can help us. 🤗
Excellent
great video BUT BE CAREFUL IF YOU HAVE ANY MACRO THAT FOCUS YOU ON AN ELEMENT - that macro will not work because every second the internal focus of system is diverted per this subroutine , I have a seach m,acro that goes to a field and give you a windows for search where you can type in what you are looking for and it stopped workign until I cahnged the time interwal to 60 000 ( 1 minutes) so my macro has one minutes to look for the field
Thanks a lot sir.
Would using option explicit at the start of the code have caught that expiredminute vs expired minutes error??
currently when I run my program and i move the mouse, it doesn't reset then time, but closes in the same amount of minutes
Thanks
Thanks indeed
when run, shows error message
The expression On Timer you entered as the event property setting produced the following error: Sub o Function not defined
This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.
yet another useful video... could you plz help me creating a module for datediff excluding only the holidays table... currently i have a module which excluded sunday an saturday which i do not want... i want to exclude only holiday table dates... thanks
I have another video at ua-cam.com/video/Na4kjr2EiLg/v-deo.html
it will count only the working day that exclude the holiday and weekend. there is a code for excluding the holiday. you may modify a little bit from this video.
+austin72406 Thank you for your response, Unfortunately I am not good at vba... I have below code and my weekend is only Friday.. can you please help me with where I am going wrong.. I want only Fridays to be excluded,, plz helpPublic Function Weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error
' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 1
' The number of days inclusive.
Dim varDays As Variant
' The number of weekend days.
Dim varWeekendDays As Variant
' Temporary storage for datetime.
Dim dtmX As Date
' If the end date is earlier, swap the dates.
If endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If
' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1
' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbFriday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbThursday, 1, 0) _
' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)
Weekdays_Exit:
Exit Function
Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function
VBA has weekday built-in function for you. you can just modify a little bit like mine:
Public Function NewWeekdays(startDate As Date, strEndDate As Date) As Integer
Dim intCount As Integer
Dim strMydate As Date
On Error GoTo errHandler
intCount = 0
strMydate = startDate - 1
Do While strMydate < strEndDate
strMydate = strMydate + 1
If Weekday(strMydate, vbMonday) < 6 Then
intCount = intCount + 1
End If
Loop
NewWeekdays = intCount
errHandler:
Exit Function
End Function
then just call it and put the number of weekdays on the text box text6
Me.Text6 = NewWeekdays(Me.Text1, Me.Text3)
I have successfully placed this code in my dbase. However, when I use the msg box code the dbase stays open until someone presses the OK button on the message box. Can it have another timer for the msg box to displaythe message box for only 1 minute and then close without having to click ok on the displayed message box? Initial close time is set to 20 minutes and works fine without the msgbox.
I believe message box requires user input. Your best bet is a popup box. Add this to your code where you want to quit:
Dim WshShell As Variant
Dim y As Integer
Set WshShell = CreateObject("WScript.Shell")
y = WshShell.PopUp("The Database is going to close due to inactivity" & vbCrLf & vbCrLf & "Closing in.....", 1, "Good Bye")
Set WshShell = Nothing
autoexec not running when saved as accde
@Nana Sara