Delete all contents of a table on timer

[chimp8471]

So i have my form with the items in (Cart)

and i have put the following in (Set timer to 3000 - shorter interval only for my test)

Option Compare Database

Private miTimerMins As Integer
Private mlTicks As Long   'elapsed non activity ticks
Private mlTickLimit As Long


Sub Form_Load()
  '30 mins
miTimerMin = 30   'or set in a tConfig tbl as default  =Dlookup("[TimerMins]","tConfig")
Me.TimerInterval = 3000  'default form timer = 30 secs.  (aka 30000)
  'the limit of inactivity
mlTickLimit = (miTimerMin * 60 * 1000)    'minutes *  sec/hr * millisecs (aka ticks)
End Sub


Private Sub Form_Timer()


mlTicks = mlTicks + Me.TimerInterval
If mlTicks >= mlTickLimit Then
 
  DoCmd.OpenQuery "qdDeleteTmpTbl"
   Me.Requery
End If
End Sub


Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
mlTicks = 0


End Sub

But it's doing nothing, anything obvious ? please help



My form looks like this

[chimp8471]

OK so i think it maybe working.... Maybe! well it's doing something

i noticed i had a few text lines in there ...

I had this bit in it...reset the ticks if mouse clicks . or other events.

I have removed that and not i get the message saying it's going to run the delete query....

Can i turn that message off when it runs? so the query just runs?

[Micron]

Post the code you wrote and I'll provide a way (maybe 2) of doing that.
Please post code within code tags (# on posting toolbar).

EDIT -Sorry, looks like you posted already. I'll look at that.
#2 - if you see this before I'm finished, you have spelling errors(s)?

Private miTimerMins As Integer
...
miTimerMin = 30

This escaped notice because you don't have Option Explicit at the top of the module code. More on that later.

[chimp8471]

Many thanks

Option Compare Database

Private miTimerMins As Integer
Private mlTicks As Long   'elapsed non activity ticks
Private mlTickLimit As Long


Sub Form_Load()
  '30 mins
miTimerMin = 30   'or set in a tConfig tbl as default  =Dlookup("[TimerMins]","tConfig")
Me.TimerInterval = 30000  'default form timer = 30 secs.  (aka 30000)
  'the limit of inactivity
mlTickLimit = (miTimerMin * 60 * 1000)    'minutes *  sec/hr * millisecs (aka ticks)
End Sub


Private Sub Form_Timer()
Code:
mlTicks = mlTicks + Me.TimerInterval
If mlTicks >= mlTickLimit Then
 
  DoCmd.OpenQuery "qdDeleteTmpTbl"
   Me.Requery
End If
End Sub


Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
mlTicks = 0
End Sub

[Micron]

OK, I don't have the query so used a message. However, I had to alter the values for a test form in a testing db because anything larger than what I have here produced Error 6, which is "overflow". I know what causes it but I'm stumped as to why. There is no way that 30*60*1000 should raise that error when the variable is a Long. It won't even accept 3*60*1000 (180,000). FYI, max +ve for Long is 2,147,483,647. The main take-away should be the error handlers; especially for the load event, otherwise timer event will continue to fire after an error.

So put the values back to what you had and see what you get. This uses an error handler to ensure warnings get turned back on. I would also have a handler in the open event in case something errs on the values used. Values that result in a timer event of 30 seconds seems too short to me and a 30 minute window seems too long. A user could "hold" the last item for 30 minutes. How many others could have added that item, only to have it become available again half an hour later. IMO, no one should be able to hold an item that long unless perhaps you have hundreds of everything, or maybe if you allow others to complete an order for uncommitted items.
NOTE: I am also having issues with the test form firing mousedown events on form detail section - it is supposed to AFAIK.
https://docs.microsoft.com/en-us/off...form.mousedown

Due to that and the overflow issue, I'm going to move this to a new test db. If anyone can see why for either of these issues that would be nice. In the meantime, it's almost impossible for me to test to the parameters required.

Option Compare Database
Option Explicit '<should be at the top of all modules. Search how to turn on for new modules.

Private miTimerMins As Integer
Private mlTicks As Long   'elapsed non activity ticks
Private mlTickLimit As Long

Private Sub Form_Load()
  '30 mins
On Error GoTo errHandler

miTimerMins = 10   'or set in a tConfig tbl as default  =Dlookup("[TimerMins]","tConfig")
Me.TimerInterval = 30000  'you had 3000, which is 3 seconds
  'the limit of inactivity
mlTickLimit = (miTimerMins * 60 * 10) 'minutes *  sec/hr * millisecs (aka ticks)

Exit Sub

exitHere:
Me.TimerInterval = 0
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

Private Sub Form_Timer()
On Error GoTo errHandler

mlTicks = mlTicks + Me.TimerInterval
If mlTicks >= mlTickLimit Then
  DoCmd.SetWarnings False
  ''DoCmd.OpenQuery "qdDeleteTmpTbl"
  MsgBox "query ran"
  'Me.Requery
End If

exitHere:
DoCmd.SetWarnings True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
mlTicks = 0
End Sub

EDIT - mouse event issue is likely because putting it on the form doesn't work for the detail section. Forgot about that. It points out that mousedown will have to apply to every control - or just the form + its sections, depending on what is required. Still going to test in a new db because of the overflow error.

[Micron]

I believe I have the answer re: overflow. Anyone want to take a stab at it? Or am I the only one having an issue, perhaps due to Access version?
Sorry to Ranman256, but I can't see how the original code could work given what the solution seems to be - at least not in Access 2016.