Solved Stopwatch function triggered on specific event (1 Viewer)

[cheekybuddha]

>> As you can see, they are all TEXT datatype <<
OK, I changed all the SQL (in functions GetVehicleStatusChanges() and ChangeStatus() and in query qryJourneys) to treat ID_VEHI and AUTO as strings.

I also changed the Datatypes pf the fields in table AUTO_VEHI to match.

I added a couple of functions in a new module called basSQLHelpers to simplify string and date formatting in SQL statements (SQLStr() and SQLDate()).

I renamed Module1 to basStatus to make it more descriptive of its contents.

>> I get an error when opening frmMain "Undefined function "Duration" in expression" <<
This function was defined in Module1, but I have now moved it in to its own module basDuration.

If you used the database I uploaded then I'm not sure why you had this problem. If you imported the objects into your own database, maybe you didn't copy the function over?

>> It would also be great if [...] there was a log file for each vehicle journeys by month <<
You will have the data recorded in table AUTO_VEHI, and an effective log in qryJourneys. You can either build queries on top of qryJourneys or new queries based on qryJourneys to show journeys by month. Ask if you need help with that.

Here's a new version of the db: 20200505_DM_Zastoji offline iz ETAT_VEHI.accdb

1. Extract the db
2. Open and re-link table ETAT_VEHI
3. Open VBA editor (Alt+F11)
4. Check every code module has Option Explicit declared at the top (along with Option Compare Database)
5. Compile - (Debug menu -> Compile).
5a. Correct any errors reported (or post them here)
5b. When there are no compilation errors, go to step 6.
6. Open form frmMain
7. Adjust timer interval if you wish
8. Start/Stop journeys for vehicles

 

[Smoky84]

If you used the database I uploaded then I'm not sure why you had this problem. If you imported the objects into your own database, maybe you didn't copy the function over?

I've used the db you uploaded after relinking the ETAT_VEHI table from my downloads folder.

I have followed your steps, compiler did not retrieve any errors

Class module Form_ETAT_VEHI was missing Option Explicit so I've added it, but unfortunately an error pops up

I've tried to delete Option Explicit but still getting an error.

You will have the data recorded in table AUTO_VEHI, and an effective log in qryJourneys. You can either build queries on top of qryJourneys or new queries based on qryJourneys to show journeys by month. Ask if you need help with that.

I will try to do it myself first, will get back to you if I get stuck

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.

What is the best way to change all names in this db, I was thinking first to change the tables names, then queries, and then VBA using the find/replace function, is this ok?
Hope it works after that #fingerscrossed

 

[cheekybuddha]

Oops! I uploaded the wrong db!

Use this one and repeat the same instructions.

 

[Smoky84]

Again same fault

 

[cheekybuddha]

OK, so the datatype of all fields in ETAT_VEHI that you re-linked are 'Text' (except for DATE_MODIF) ?

The SQL for qryJourneys is/should be:

SELECT
  (
    SELECT
      COUNT(*)
    FROM AUTO_VEHI t3
    WHERE t3.ID_VEHI = t.ID_VEHI
      AND t3.AUTO = '1'
      AND t3.DATE_REC <= t.Started
  ) AS Journey,
  t.ID_VEHI,
  t.Started,
  t.Stopped, 
  Duration(t.Started, Nz(t.Stopped, Now())) AS JourneyTime
FROM (
  SELECT 
    t1.ID_VEHI, 
    t1.DATE_REC AS Started, 
    (   
      SELECT TOP 1     
        t2.DATE_REC   
      FROM AUTO_VEHI t2   
      WHERE t2.AUTO = '0'   
        AND t2.ID_VEHI = t1.ID_VEHI   
        AND t2.DATE_REC > t1.DATE_REC
      ORDER BY      
        t2.DATE_REC DESC 
    ) AS Stopped
  FROM AUTO_VEHI AS t1
  WHERE t1.AUTO = '1'
  ORDER BY t1.DATE_REC
) t;

It's late here. If you can't get it working now we'll have to look at it in the morning.

d

 

[Smoky84]

OK, so the datatype of all fields in ETAT_VEHI that you re-linked are 'Text' (except for DATE_MODIF) ?

That was it, they were "Numbers" I changed it to text and no problems occurred. Sorry my bad!

I'll play with it a couple of days and try to change all the names that came with the system update and get back when I succeed (or fail miserably).

It's late here.

Yeah it's pretty late here also but this week I work night shift so Yippee ki-ay!!!

Thanks again,
and again
...
and again

 

[cheekybuddha]

I'll play with it a couple of days and try to change all the names that came with the system update and get back when I succeed (or fail miserably).

Hi, this is not an easy task! You have to find a lot of places to change the names!!!

Here is an update that uses the new table names. I hope it will save you some time!

If your system is still receiving changes when you get an update, maybe you can influence them to use proper datatypes for their database fields and also add PK and indexes?

Anyway, keep us posted with your progress.

d

 

[Smoky84]

Hi, this is not an easy task! You have to find a lot of places to change the names!!!

I've actually got it to work yesterday like 2 hours after our last chat, I succeeded in changing all the table names and field names in tables, queries and VBA, add an AUTOEXEC macro to run the frmMain at startup, removed the buttons "For testing purposes only" and ran it on the server for couple of hours for testing purposes. I just haven't got the time to let you know about the progress that was made, and guess what?

Woooohooo it works!!!

I've shut it down today in the morning and started it again an hour ago to simulate shutting down the db in an unexpected error.
After switching it on the journey time resumed at the last recorded time so actually the time that was db shut down is added in the journey time like the vehicle was driving, but that is not such a big problem. I'll try to do a query that calculates the time each vehicle was stopped, maybe even add this information into frmJourney

If your system is still receiving changes when you get an update, maybe you can influence them to use proper datatypes for their database fields and also add PK and indexes?

This was major update which happened because of the server change and switching to a newer version of the system, I do not believe it will happen again any time soon.

I need more data to define any problems with the db but so far it looks good.
Here's the example of the current last version of the db, ignore the error ODBC--connection to "CIMAT" failed when opening, this is the online version that is linked with the server.
I just noticed one bug while typing this, open qryJourney and select ID_ELV 2 from the drop down menu, there are duplicate data entry in the stopped section?!?

 

[Smoky84]

OK now I got enough data to define the problem.
When the journey starts (EN_AUTO changes status to 1) the db logs the change time into AUTO_ELV table and this is ok.
But when the journey stops the db also logs the change time into the table but overwrites all stopped times in frmJourney with the latest time the vehicle stopped.

this also changes all journey times in the form.
Also the journey time is formated in the way the second the time surpasses 24h it says 1day and 24hrs like in the example journey 5 in the picture, maybe it would be better just to express time in hh:mm:ss without the "day" expression

I noticed that times recorded in the AUTO_ELV table are correct, the problem occurs only in the form.

Best regards, Igor

 

[cheekybuddha]

Well spotted, Igor!!

There is a mistake in qryJourneys, in the sub-query for getting the Stopped value. Somehow I left in 'DESC' in an ORDER BY clause which needn't be there.

Change the SQL to:

SELECT
  (
    SELECT
      COUNT(*)
    FROM AUTO_ELV t3
    WHERE t3.ID_ELV = t.ID_ELV
      AND t3.EN_AUTO = '1'
      AND t3.DATE_REC <= t.Started
  ) AS Journey,
  t.ID_ELV,
  t.Started,
  t.Stopped,
  Duration(t.Started, Nz(t.Stopped, Now())) AS JourneyTime
FROM (
  SELECT
    t1.ID_ELV,
    t1.DATE_REC AS Started,
    (
      SELECT TOP 1
        t2.DATE_REC
      FROM AUTO_ELV t2
      WHERE t2.EN_AUTO = '0'
        AND t2.ID_ELV = t1.ID_ELV
        AND t2.DATE_REC > t1.DATE_REC
      ORDER BY
        t2.DATE_REC
    ) AS Stopped
  FROM AUTO_ELV AS t1
  WHERE t1.EN_AUTO = '1'
  ORDER BY
    t1.DATE_REC
) t;

I hope that fixes it!

 

[Smoky84]

Hey, sorry for the delay, weekend is reserved for my family (wife, kid and a dog )

Change the SQL to:

SELECT
(
SELECT
COUNT(*)
FROM AUTO_ELV t3
WHERE t3.ID_ELV = t.ID_ELV
AND t3.EN_AUTO = '1'
AND t3.DATE_REC <= t.Started
) AS Journey,
t.ID_ELV,
t.Started,
t.Stopped,
Duration(t.Started, Nz(t.Stopped, Now())) AS JourneyTime
FROM (
SELECT
t1.ID_ELV,
t1.DATE_REC AS Started,
(
SELECT TOP 1
t2.DATE_REC
FROM AUTO_ELV t2
WHERE t2.EN_AUTO = '0'
AND t2.ID_ELV = t1.ID_ELV
AND t2.DATE_REC > t1.DATE_REC
ORDER BY
t2.DATE_REC
) AS Stopped
FROM AUTO_ELV AS t1
WHERE t1.EN_AUTO = '1'
ORDER BY
t1.DATE_REC
) t;

Yep that did the trick!

I solved the problem with the date formatting that was containing "days" since it has more impact in presenting the time to the manager represented in hh:mm:ss
I just changed basDuration module to:

Function Duration(dtStart As Date, dtEnd As Date) As String
' Format duration between 2 DateTime's as: 1 hrs 2 mins 3 secs
  Dim lTotalSecs As Long
  Dim iHrs As Integer, iMins As Integer, iSecs As Integer

  lTotalSecs = DateDiff("s", dtStart, dtEnd)
  iHrs = lTotalSecs \ 3600
  iMins = (lTotalSecs Mod 3600) \ 60
  iSecs = lTotalSecs Mod 60

  Duration = IIf( _
              iHrs > 0, _
              iHrs & " hr" & IIf(iHrs > 1, "s", "") & " ", _
              "" _
             ) & _
             iMins & " min" & IIf(iMins <> 1, "s", "") & " " & _
             Format(iSecs, "00") & " sec" & IIf(iSecs <> 1, "s", "")

End Function

Now I'm trying to add another row in the frmJourney named "StoppedTime" that calculates how much time the vehicle was off, I was trying the reverse logic with the qryJourneys but did not get correct results, please help!

Dude I've got to say, you're a monster, the way you handle SQL like it's your native language, I bow to you master Buddha!!!

 

[cheekybuddha]

Now I'm trying to add another row in the frmJourney named "StoppedTime" that calculates how much time the vehicle was off

I feared you might want to do that!

Can you post a recent copy of the db with some data in table AUTO_ELV.

 

[Smoky84]

I feared you might want to do that!

Haha I'm slowly becoming your worst nightmare
Sorry about that!
Here is the latest version

 

[cheekybuddha]

OK, replace the SQL in qryJourney with:

SELECT
  t.ID_ELV,
  (
    SELECT
      COUNT(*)
    FROM AUTO_ELV t3
    WHERE t3.ID_ELV = t.ID_ELV
      AND t3.EN_AUTO = '1'
      AND t3.DATE_REC <= t.Started
  ) AS Journey,
  t.PrevStopped,
  IIf(IsDate(t.PrevStopped), Duration(t.PrevStopped, t.Started), NULL) AS TimeOff,
  t.Started,
  t.Stopped,
  Duration(t.Started, Nz(t.Stopped, Now())) AS JourneyTime
FROM (
  SELECT
    t1.ID_ELV,
    (
      SELECT TOP 1
        t3.DATE_REC
      FROM AUTO_ELV t3
      WHERE t3.EN_AUTO = '0'
        AND t3.ID_ELV = t1.ID_ELV
        AND t3.DATE_REC < t1.DATE_REC
      ORDER BY
        t3.DATE_REC DESC
    ) AS PrevStopped,
    t1.DATE_REC AS Started,
    (
      SELECT TOP 1
        t2.DATE_REC
      FROM AUTO_ELV t2
      WHERE t2.EN_AUTO = '0'
        AND t2.ID_ELV = t1.ID_ELV
        AND t2.DATE_REC > t1.DATE_REC
      ORDER BY
        t2.DATE_REC
    ) AS Stopped
  FROM AUTO_ELV AS t1
  WHERE t1.EN_AUTO = '1'
  ORDER BY
    t1.DATE_REC
) t
ORDER BY
  t.ID_ELV,
  t.Started
;

I hope that will do what you want!

 

[Smoky84]

I thank you from the bottom of my heart Sir, dinner invite in Croatia is still on!!!

 

[cheekybuddha]

You're welcome!

I hope you have checked it works first!!!

Thanks you for your kind invitation - I will be sure to give you a shout if ever I make it to Croatia, otherwise let me know if you are ever in London.

 

[Smoky84]

I hope you have checked it works first!!!

I have and it works like it should, nicely done!

otherwise let me know if you are ever in London

Will do, next time I visit London Ill give you a buzz.
In the meantime I'm planning to start to learn SQL since I will be using it more often.

Thank you again, you've been great!