Get real time value of a textbox from a front end database of each users (1 Viewer)

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Sep 12, 2006
Messages
15,613
rather than user timers, you could just have a "refresh" button on the dashboard to recalculate, at the discretion of the user. It's only ever a snapshot anyway.
 

Mackbear

Registered User.
Local time
Today, 08:02
Joined
Apr 2, 2019
Messages
168
As stated in post 7:

Save a record when starting activity. When activity is changed, 'close out' open record then create a new record.

The 'current' activity is record where 'closed' field Is Null.


Calculate elapsed time with DateDiff() function using the logged in start date/time and Now().

Have a form bound to log table. RecordSource filtered to retrieve only records WHERE closed Is Null. Elapsed time calculation in query or textbox.

How do I close out the existing "open record"? If I understand, I would add a 'Closed' field on the table. How do I input something to make it not null, that it should still be inline with the record I saved? I only know how to add new records through vba but not to update an existing record's field. Please please guide me step by step thanks so much
 
Last edited:

June7

AWF VIP
Local time
Today, 05:02
Joined
Mar 9, 2014
Messages
5,423
Depends. If form is already open to the record where Closed field Is Null then simply:

Me!Closed = Now()

If form is UNBOUND then run UPDATE action. Need to know the ID of user. Where are you holding the UserID so it can be referenced?

CurrentDb.Execute "UPDATE Log SET Closed = Now() WHERE Closed Is Null AND UserID=" & userID input here somehow
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
OK, in the strictest sense of the word, you are going to have trouble with true "real time" updates. The closest I would get is you have to have a table in the back end with a record for every user you want to track. This record would contain "current activity" and "user ID" and "time started." The user updates this once with each "pick up" of a new activity, perhaps done via a simple UPDATE query.

Then your status board can compute the elapsed time since the recorded start time and make that your "real time" display. But it is not a good idea to continually ping on the central BE server with a bunch of timers... particularly when they aren't necessary. Your system that does the monitoring can do ALL of the elapsed computations in a single routine or single query that looks at each start time vs. Now() and computes the time.

If this is a domain-based network, then the domain controller will probably also provide a time synchronization function unless you have Network Time Protocol implemented (also a possibility). So computing the elapsed time knowing the start time is actually a pretty simple bunch of computations.
 

Mackbear

Registered User.
Local time
Today, 08:02
Joined
Apr 2, 2019
Messages
168
As stated in post 7:

Save a record when starting activity. When activity is changed, 'close out' open record then create a new record.

The 'current' activity is record where 'closed' field Is Null.


Calculate elapsed time with DateDiff() function using the logged in start date/time and Now().

Have a form bound to log table. RecordSource filtered to retrieve only records WHERE closed Is Null. Elapsed time calculation in query or textbox.

Hello, thanks for all the insights, I'm trying stuff out, and the datediff() calculation does not seem to work. I have DateDiff(s, Me.Starttime, Now()) coded in the textbox triggered by form load. It is it calculating. What could be wrong?

Thank you very much!
 

June7

AWF VIP
Local time
Today, 05:02
Joined
Mar 9, 2014
Messages
5,423
The interval unit must be within quote marks.

DateDiff("s", Me.Starttime, Now())
 

Mackbear

Registered User.
Local time
Today, 08:02
Joined
Apr 2, 2019
Messages
168
The interval unit must be within quote marks.

DateDiff("s", Me.Starttime, Now())



IT works now great! How do I make it look like it is counting every second?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
IT works now great! How do I make it look like it is counting every second?

You will hate yourself for doing this, but make the form have a timer equal to 1 second (1000 msec, since the timer unit is msec). High-frequency timers can make a system really sluggish. Do yourself a favor. Estimate how long it takes for a person to actually select a new task. Make your timer no more often than that interval, because don't forget you need to requery the user/task/time table once per timer tick. The more you drag across the network, the more it will resemble a "notwork."

If you do stuff that frequently, your IT department (if you have one) will hate you forever, worse than Gollum hated Bilbo Baggins.
 

Mackbear

Registered User.
Local time
Today, 08:02
Joined
Apr 2, 2019
Messages
168
You will hate yourself for doing this, but make the form have a timer equal to 1 second (1000 msec, since the timer unit is msec). High-frequency timers can make a system really sluggish. Do yourself a favor. Estimate how long it takes for a person to actually select a new task. Make your timer no more often than that interval, because don't forget you need to requery the user/task/time table once per timer tick. The more you drag across the network, the more it will resemble a "notwork."

If you do stuff that frequently, your IT department (if you have one) will hate you forever, worse than Gollum hated Bilbo Baggins.


Hahaha that is so much hate :D...I think what I need is just like a stopwatch just to show how much time passed, I'm thinking not really a timer on the form...
 

Mackbear

Registered User.
Local time
Today, 08:02
Joined
Apr 2, 2019
Messages
168
Thank you everyone for helping me out on this, I was busy building the stuff for a while and got it figured out and it is up and running. I really appreciate it! = )
 

Mackbear

Registered User.
Local time
Today, 08:02
Joined
Apr 2, 2019
Messages
168
Thank you everyone for helping me out on this, I was busy building the stuff for a while and got it figured out and it is up and running. I really appreciate it! :):):)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:02
Joined
Oct 29, 2018
Messages
21,357
Thank you everyone for helping me out on this, I was busy building the stuff for a while and got it figured out and it is up and running. I really appreciate it! :):):)
Hi. Congratulations! Good luck with your project.
 

Users who are viewing this thread

Top Bottom