Delete all contents of a table on timer

[chimp8471]

Evening

I have a table [tTempPartsIssued) which i use as a "Cart" so the user adds various records
however
i want to put a timer on it that if the user does not interact adding/removing items from the cart for approx 30 mins. the cart will empty

Help would be appreciated

[ranman256]

if the form that has the table recs in it.
set the timer on the form for say 30 secs: 30000

Everytime a mouse is moved or something clicks, reset the timer
then the timer hits the limit , run the delete query.
but set any mouse click or data update to reset: mlTicks = 0

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()

mlTicks = mlTicks +me.timerInterval
if mlTicks >= mlTickLimit then
 
  docmd.openquery "qdDeleteTmpTbl"
   me.requery
endif
end sub

reset the ticks if mouse clicks . or other events.

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

[Micron]

You have a form bound to this table? Then Timer event on the form to run every 60*30*1000 (Interval = 1,800,000) and in that event check if there has been any activity. However, you need 1 or more events to detect any activity and set a flag that uses Time or Now() to get a time stamp for that activity. Hard to say what those events might be without knowing anything about your form - assuming you have one. Perhaps form AfterUpdate would serve to set/reset that activity timestamp.

[chimp8471]

Hi,

been playing with this.. not getting too far.

However it got me thinking.

As the user maybe doing other things this could confuse things...

How about (over to you all with ya Genius work then)

So i like the timestamp idea

I add a time stamp to the table the last entry into the table, is it possible to check the current time against last time stamp in the table and if there is a 30 minute gap it runs the delete query

Is that even possible please?

[Micron]

I'd say it depends on how you want it to work. Ranman256 has good ideas about how to detect any user action on the form, but what do you want? If I as a user simply do a mouse click on the form somewhere is that enough? Or do you want a more focused action such as adding/removing an item or adjusting the count of an item? Ranmans's idea would be simpler. Not sure I see the need for a table. If you open a form and are adding items via that form, the form code would contain the "timestamp" of when it is opened. Each time something as simple as a mouse click would reset the form timer variable. The Timer event code would run x seconds after the form opens and check the value of that variable. It might be only 2 minutes old, so do nothing. If it is > x minutes between the Timer event (which compares the variable to Now() ) then do what? I think it's quite doable. You just need to flesh out the details so that the design can accommodate them.

[chimp8471]

thanks for the input...

The reason i want to clear the table is i am using it as a shopping cart, i store the list then append the final list to proper table once the user has finished.

however

i have users who load things into their cart then go home leaving the items in the basket.. I therefore am wanting it to empty/delete all items in the temptable after 30 mins of last entry that was entered.

hope that makes sense

[Micron]

Then you have your answer in post 2?

[Minty]

Assuming it is a backend table, why not make it a local table?
That way if the user comes back to it after lunch "their" cart is still intact.

Other users won't be affected.

[chimp8471]

Sorry about all the lack of clarity, and i don't know if what i am trying to achieve is possible.

The problem is that if another user wants to create a new cart , they have to empty the old one.

I understand that i could just have a button to clear it out... but i was hoping to automate something.

The temp table is local..

The issue see with the 30 mins no activity option is that a user could be doing something else (using google, word etc) so i assume the click timer would be reset everytime the mouse is moved or clicked.

I found this which is a similar thing (i believe) but it would need to look at last time in table and compare it to current time, No idea how to modify it though.

'5:45 AM is 0.2395833 and 6:15 AM is 0.2604167

Dim currTime As Single
currTime = Time

If currTime > 0.2395833 And currTime < 0.2604167 Then
    'Run your Macro
Else
    'Do Nothing End If 

where it says

If currTime > 0.2395833 And currTime < 0.2604167 Then

i am looking for

if Current time - Temptable.time (last logged time)>30mins then run macro/deletequery

Assuming it is a backend table, why not make it a local table?
That way if the user comes back to it after lunch "their" cart is still intact.

Other users won't be affected.

[Micron]

i assume the click timer would be reset everytime the mouse is moved or clicked.

No, it is relative to the form or control to which the event pertains (is attached/linked to/associated with). Has nothing to do with anything outside of Access. Sorry, but I for one think you have a solution which you obviously didn't try so AFAIC, if you get another one (even based on that new code) there's no guarantee you'll try that either. Why not try what you've been given?

[Minty]

The temp table is local..

In that case your stipulation that another user might start the process is redundant.
The other user will be in another copy of the front end database (I hope) and the temp table is local to them, they won't have a conflict as they are writing to a different local version of your temp table.

And if you really want to do this, as @Micron has stated, there is a perfectly good solution in post 2 which you appear to have ignored.

[chimp8471]

Sorry i feel you getting frustrated...I get this is all obvious to you. as i said i am new to this,

so wanted to make sure i was clear...

I did actually try it before but i couldn't get it to work, obviously putting code in wrong places.

I didn't understand it was form specific, now i am clear i can try it again.

once again thanks

[chimp8471]

as @Micron has stated, there is a perfectly good solution in post 2 which you appear to have ignored.

Not ignored, just don't understand!

Feeling like i am irritating people on here which is not my intention. I just want to learn but understand what i am doing

[Minty]

No problem, but remember we can't see what you have and haven't tried.
We often see a bit of a "scatter gun " approach to finding a solution with new users, when a slow and steady process of elimination works a lot better.

If you post up the complete code you tried that didn't work, with an explanation of what it did (or didn't do!) when it failed, and what line of code was highlighted when you pressed debug we can then probably assist further.

[Welshgasman]

Not ignored, just don't understand!

Feeling like i am irritating people on here which is not my intention. I just want to learn but understand what i am doing

If it is a local table, then surely you just need a button to Clear Cart, else leave what is in the cart, in case the user comes back.?
Only problem I can see with that, is if the system checks the availability of something before adding to the cart, then it could have been put in someone else's cart within that 30 minutes.?
In which case a simple Delete query would run (the same one that is run when you press Clear cart?)