Hello all!
I need some help with the problem I'm facing.
Let's just say for example I have a vehicle and when he is driving the status of the vehicle is "1" and when he is not driving status is "0".
I need the timer to start as soon the status changes to "0" and pause when status changes back to "1", and to resume again when status changes to "0"
Long story short I need to calculate the time vehicle was not driving on a monthly basis expressed in hh:mm:ss.
Also I would need an option to reset the timer, I was thinking maybe with a button running some script?
I am fairly new at Access and still learning, would appreciate some help from you guys!
Thanks in advance.
The table would have 2 full date/time fields,startTime,endTime.
VehID, DriverID,StartTime,EndTime
When you start the timer, the start field gets =NOW().
Same with EndTime.
The result elapsed is calculated in a query.
This can be done in a form, tho I don't think a driver would get into a car,with the access db, then start & stop a timer.
I guess this would be transcribed later.
I think a better strategy to would be to create a new table for VehicleStatus with the following fields:
VehicleStatusID, PK autonumber
VehicleID, Long, foreign key to your Vehicles table
Status,
Status, DateTime, Default Now()
Then, when you your vehicle status changes, you add a new record with the VehicleID and Stautus.
After you can query the table by VehicleID and calculate the sum of the time between status = 0 to the next 1.
The table would have 2 full date/time fields,startTime,endTime.
VehID, DriverID,StartTime,EndTime
When you start the timer, the start field gets =NOW().
Same with EndTime.
The result elapsed is calculated in a query.
This can be done in a form, tho I don't think a driver would get into a car,with the access db, then start & stop a timer.
I guess this would be transcribed later.
I was using analogy with the cars just as an example, I already have a table with the vehicle numbers and status that is changing automatically depending on real situation with the vehicles, it is a server based SQL database and cannot be changed.
I was thinking about using a local table as a log file of a server based database with the timestamp of a status change, my problem is how to initiate data entry in the local table only when vehicle status changes.
After that simple query calculates the SUM.
Appending a table is something I have done before but not 100% sure how to make it work in this case
I'm not changing status by hand, status is being changed automatically when a vehicle sends signal that he is not in automatic mode (status "0"), and again when it is in automatic mode (status "1")
Vehicle field is "ID_VEHI" and status field is "AUTO", SQL table name is "ETAT_VEHI"
If the SQL server table (ETAT_VEHI) is updated automatically by the vehicle, then how will you be notified when the status (AUTO) changes?
Either you would have to create an update trigger on the table (but you say you can't change the SQL server tables) or you will need to hook in to the app that the vehicle uses to send its status update.
The only other method I can think of would be to query the ETAT_VEHI table every minute to see which records where AUTO had changed. I don't think this is probably a very viable solution since it will place extra load on the server and you will possibly have a pretty unresponsive Access app.
I have created a form from ETAT_VEHI table and set it to auto-refresh (Me.requery) every 1000ms, the table itself is rather small and does not contain a lot of data so the impact on the server is negligible, we have measured it and it is about 3% more than regular usage.
However this also presents a problem, I created a trigger that logs vehicle status in the local table from the auto-refreshing form but the problem is that the trigger creates separate line every second (Me.requery every 1000ms) after the status update, I need only one line with the timestamp of the change in the moment the change occurs.
I am sending ETAT_VEHI table exported in .xlsx
There are number of fields that are not relevant for this case, but the problem is that any change that happens in any of the field in the table updates DATE_MODIFIED field, and only relevant information for me is the time change of the AUTO field
Then you need to load the table with current data. You could use a query like:
SQL:
INSERT INTO AUTO_VEHI
(ID_VEHI, AUTO, DATE_REC)
SELECT
ID_VEHI,
AUTO,
Now()
FROM ETAT_VEHI
;
Once you have that, then every minute you can query table ETAT_VEHI to return all records where DATE_MODIF is greater than the maximum DATE_REC in local table AUTO_VEHI.
With those records, you compare the AUTO value with the latest AUTO value for each vehicle in AUTO_VEHI. If it is different, then insert a new record with Now() for DATE_REC.
Create a function in a standard module:
Code:
Option Explicit
Option Compare Database
Function GetVehicleStatusChanges() As Boolean
Dim strSQL As String, dtLastUpdate As Date
dtLastUpdate = DMax("DATE_REC", "AUTO_VEHI")
strSQL = "SELECT ID_VEHI, AUTO, Now() FROM ETAT_VEHI WHERE DATE_MODIF > " & Format(dtLastUpdate, "\#yyyy\-mm\-dd hh:nn:ss\#") & ";"
With CurrentDb.OpenRecordset(strSQL)
If Not (.BOF And .EOF) Then
Do Until .EOF
If Nz(DLookup("AUTO", "AUTO_VEHI", "ID_VEHI = " & .Fields("ID_VEHI")), .Fields("AUTO") <> .Fields("AUTO") Then
strSQL = "INSERT INTO AUTO_VEHI (ID_VEHI, AUTO, DATE_REC) VALUES (" & _
.Fields("ID_VEHI") & ", " & .Fields("AUTO") & ", " & Format(Now, "\#yyyy\-mm\-dd hh:nn:ss\#") & ");"
CurrentDb.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End If
End With
GetVehicleStatusChanges = Err = 0
End Function
Then, in your form's timer event put:
Code:
Private Sub Form_Timer()
Call GetVehicleStatusChanges
End Sub
Notes:
1. This is complete air-code and has not been tested, so is likely not to work without tweaking! It might not even compile!
2. You are going to need the form to be open all the time if don't want to risk missing a status change.
3. Do you actually require one-minute granularity? You could run in to problems if you have millions of records and the function takes longer than a minute to run.
4. IMHO, this would be a pretty fragile solution. You would be much better off trying to get this data from the app which the vehicles use. Does it have logs you can query? What happens if your db stops running for any amount of time?
I am fully aware of this disadvantage but it's the only one I've got, this database will be working on a server alongside with the SQL which is ON 24/7
3. Do you actually require one-minute granularity? You could run in to problems if you have millions of records and the function takes longer than a minute to run.
4. IMHO, this would be a pretty fragile solution. You would be much better off trying to get this data from the app which the vehicles use. Does it have logs you can query? What happens if your db stops running for any amount of time?
Unfortunately this is a company policy, they won't let me temper with the direct code or the vehicle app because of the possibility of warranty loss.
If the db stops it is a not a big issue, I was planning on checking it daily, if anything the results will be even better (I won't tell anything if you don't)
Jokes aside, thank you man, your help is highly appreciated!
I'm sending ETAT_VEHI.zip with two files, ETAT_VEHI.accdb that simulates SQL server database and Zastoji offline iz ETAT_VEHI.accdb that was created following your steps
Note that I'm using date format dd.mm.yyyy hh:mm:ss, I've tried to change that but still getting an error
Can you take a look, maybe I'm doing something wrong?
' ...
Do Until .EOF
If Nz(DLookup("AUTO", "AUTO_VEHI", "ID_VEHI = " & .Fields("ID_VEHI")), .Fields("AUTO")) <> .Fields("AUTO") Then
' ...
I changed the datatype of 2 fields in table ETAT_VEHI
Code:
ID_VEHI -> Number (Long Integer) and I made it Primary Key
AUTO -> Number
I changed the TimerInterval in form ETAT_VEHI to 60000 (1 minute) - you don't need it to ping every second (1000)!!
I added a function to Module1 for simulating the vehicle app changing the vehicle's status:
Code:
Function ChangeStatus(id_vehi As String) As Integer
Dim strSQL As String
strSQL = "UPDATE ETAT_VEHI SET AUTO = IIf(AUTO = 1, 0, 1), DATE_MODIF = Now() WHERE ID_VEHI = " & id_vehi & ";"
CurrentDb.Execute strSQL, dbFailOnError
ChangeStatus = Nz(DLookup("AUTO", "ETAT_VEHI", "ID_VEHI = " & id_vehi), -1)
End Function
In the Immediate Window (Ctrl+G) you can just call the frunction passing the ID_VEHI of the vehicle. It will return the new status (AUTO):
Code:
?ChangeStatus(2)
1
Don't try and change the Date format you use. The format must be in ISO or US date format for SQL, so we convert it.
This is great, and it works like a charm, however the immediate function does not work.
I was thinking maybe it would be better if I have 2 separate tables, ETAT_VEHI_OFF for when the vehicle status changes to "0" and ETAT_VEHI_ON for when it changes back to "1" but I can't get it to work in the second case (changes back to "1")
I'm a total rookie in VBA and it will definitely take time until I start to understand the logic behind it.
One more thing I was thinking about, the logic is set in the way that it inserts a new row on status change only when DATE_MODIF <> Now() if I understand it correctly. But it is a possibillity that veichle changes it's status in the same moment when the form's timer is set to requery, what happens then?
Is there a way around it?
>> I was thinking maybe it would be better if I have 2 separate tables, ETAT_VEHI_OFF for when the vehicle status changes to "0" and ETAT_VEHI_ON for when it changes back to "1" <<
I don't see any advantage of this and I would advise against it.
>> But it is a possibillity that veichle changes it's status in the same moment when the form's timer is set to requery, what happens then? <<
It will change on the next time the timer fires.
I had a chance to play around with it yesterday afternoon. I made some changes to the logic in GetVehicleStatusChanges(). Using DLookup() was lazy and returned the wrong record (it returns the first match it finds, and there is no simple way to order the records to retrieve the latest).
I reworked for ETAT_VEHI to include a button for each vehicle to start and stop journeys for testing purposes. I also added a dropdown to set the timer interval - it defaults to 30 seconds.
I created a new for called frmJourneys which shows a list of the journeys for each vehicle. This is based on a query called qryJourneys which shows the start/stop times and durations of each journey from table AUTO_VEHI.
There is a bit of code in each of these forms calling functions in a module called basFormStuff which is mainly for cosmetic presentation purposes.
I created a new form called frmMain which is purely container for the two other forms, which are linked master/child.
I adapted (and renamed to qryInitialLoad) your query that loads the initial data from ETAT_VEHI to AUTO_VEHI. I set it to run each time the form opens so it will add any new vehicles that weren't there before.
So:
1. Download and extract attached file: 20200502_DM_Zastoji offline iz ETAT_VEHI.accdb.
2. Open it, and re-link ETAT_VEHI to your copy (or the copy in the zip file).
2a. REMEMBER, it expects ETAT_VEHI to have fields:
Code:
ID_VEHI: PK, Number (Long Integer), Required
AUTO: Number (Can be byte, or any Integer), Required, indexed - duplicates OK
DATE_MODIF: DateTime, Required, indexed - duplicates OK
(If you make these changes, it will warn a couple of times about data integrity - you can click OK without worrying. I have zipped up a copy with the changes already done)
2b. If the SQLServer table uses TEXT datatype for these fields, some code changes will be required. (I hope not!!)
3. Open frmMain.
4. Change Timer Interval (combobox) if you want. it will be set at 30 secs, but 15 secs works well for testing.
5. You should see list of vehicles from ETAT_VEHI.
6. Click on each one to see their journeys (there might not be any at first!!!) in bottom list.
7. Start/Stop a vehicle journey in top list. Wait for timer to fire and see it appear in bottom list.
I'm sure you will have questions, so come back when you have had a look.
Duuuude, this looks great.
But unfortunately I get an error when opening frmMain "Undefined function "Duration" in expression" so the timer isn't firing. I'm using Office 2010 at work so maybe the expression is different.
It would also be great if I had a reset button or maybe even better if there was a log file for each vehicle journeys by month, or both if this is not too much work.
One more thing, since we started typing there was a system upgrade which updated some field names as well as the table names.
I was planning to change the names myself but now it seems there is too much code, I can try and let you know the results.
)
)