Setting a time duration 2

[ZOR]

On a form opening, there is code to run a query. Unfortunately it takes a while to run. How can I put in a reference to only run the code if the form was last opened longer than 5 hours ago?


On form open code
Me.R1 = "Loading Files, Please Wait"
Me.R1.Visible = True
DoEvents
GetNames
DoCmd.Beep
Me.R1 = "< Please Select a Report

 

[MikeC14081972]

Why not run the query if it was last run over 5 hours ago.

Have a table updated with a timestamp each time the query runs and check this before allowing the query to run.

 

[ZOR]

Thanks. I was thinking of having a public variable to hold the time the user first opens the form, and the nect time the form is opened if the variable is empty or the value of the time is 5 hours outside of the time held the query gets run.

TME=Now

How do I compare the time now to whats in TME? How does one subtract to 2 to get trigger time. Many thanks

 

[PHV]

Have a look at the DateDiff function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeC14081972]

With the table example I have a table called tbl_TimeStamp with one field called TimeStamp (Original I Know) which would be updated everytime the query is run. I would then have the following code

Dim db      As DAO.Database
Dim rs      As DAO.Recordset
Dim strSQL  As String

strSQL = "SELECT Max(tbl_TimeStamp.TimeStamp) AS MaxOfTimeStamp " _
       & "FROM tbl_TimeStamp"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

If DateDiff("n", rs.Fields(0), Now) > 300 Then
   
     ' Run your query

End If

It calculates the time in minutes between when the query was last run and the current time. If it is greater then 5 hours (300 minutes) the query will run.

 

[MikeC14081972]

Or using your method use

DateDiff ("n",TME,Now)

to get minutes

Substitue n for h to get hours (although you will only get whole hours)

So I would go with minutes and look for an answer equal to or over 300

 

[ZOR]

Thanks both, I will go for DateDiff. Have good weekends