Solved Log time and date a query was last run? (1 Viewer)

[Number11]

It it possible to log when a query was last run and if so how would i go about doing this

 

[arnelgp]

supposed that you want to Log it on tblLog:

fields:
QueryName (short string)
Last Run (Date/time)

create a function that will return a boolean (true):

Public Function fncLog() As Boolean
    CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
        "select '" & CurrentObjectName & "',#" & Now() & "#"
    fncLog = True
End Function

you need to modify the current query to add a criteria:

SELECT * FROM yourTable WHERE fncLog();

 

[Number11]

supposed that you want to Log it on tblLog:

fields:
QueryName (short string)
Last Run (Date/time)

create a function that will return a boolean (true):

Public Function fncLog() As Boolean
    CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
        "select '" & CurrentObjectName & "',#" & Now() & "#"
    fncLog = True
End Function

you need to modify the current query to add a criteria:

SELECT * FROM yourTable WHERE fncLog();

Thanks so where will this function sit?

 

[arnelgp]

on a Module.

 

[Number11]

on a Module.

Ok thanks so i have created the Module and you say add this to my current query

"SELECT * FROM yourTable WHERE fncLog();"

So the query is updating a table of data so how would i call the fnclog?

 

[Uncle Gizmo]

how would i call the fnclog

See my VBA Beginner blog:-

VBA Beginner – Nifty Access

www.niftyaccess.com

There are video instructions on how to create a command button and also demonstrats how the command button has an onclick event which I assume is where you would want to call your query from.

 

[Number11]

cant run the function i think as the query is an append query

 

[Uncle Gizmo]

cant run the function i think as the query is an append query

Not sure what you mean?

 

[Number11]

Not sure what you mean?

Sorry i mean i cant call the function from the query using Exp: [fncLog()]

 

[Uncle Gizmo]

i cant call the function from the query

Where do you call the query from? In other words which event calls the query?

Do you press a button? do you load a form? Does it run when MS Access opens?

 

[Number11]

Where do you call the query from? In other words which event calls the query?

Do you press a button? do you load a form? Does it run when MS Access opens?

yeah i have a button on a form that runs the query

 

[Uncle Gizmo]

i have a button on a form that runs the query

Then you need to call the function ArnelGP provided from that command button.

 

[Number11]

Then you need to call the function ArnelGP provided from that command button.

Thanks worked by the name captured is the Form Name and not the 3 queries them self that got ran when the button was clicked

 

[Number11]

so i change the code in the fucntion to set the query name and it all works now

 

[Number11]

supposed that you want to Log it on tblLog:

fields:
QueryName (short string)
Last Run (Date/time)

create a function that will return a boolean (true):

Public Function fncLog() As Boolean
    CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
        "select '" & CurrentObjectName & "',#" & Now() & "#"
    fncLog = True
End Function

you need to modify the current query to add a criteria:

SELECT * FROM yourTable WHERE fncLog();

this was working fine, but now we are in november its recording the date formating as US, i have the table set with the format as "dd/mm/yyyy hh:mm"- but the date and time today is show as "11/02/2020 08:31" ??? do i need to change the code?

 

[arnelgp]

date Display depends on your Regional Date setting.
if you set the correct date format on your Regional Setting, there is no need to use Format
on your Form/table.

 

[Number11]

date Display depends on your Regional Date setting.
if you set the correct date format on your Regional Setting, there is no need to use Format
on your Form/table.

my time setting haven't changed set as UK if i enter date in table its correct but using the code puts the date as US?

 

[arnelgp]

then use format() on the function:

Public Function fncLog() As Boolean
    CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
        "select '" & CurrentObjectName & "'," & Format(Now(), "\#mm\/dd\/yyyy\# hh:nn:ss")
    fncLog = True
End Function

 

[Number11]

then use format() on the function:

Public Function fncLog() As Boolean
    CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
        "select '" & CurrentObjectName & "'," & Format(Now(), "\#mm\/dd\/yyyy\# hh:nn:ss")
    fncLog = True
End Function

Ok, did that changed the format to UK and getting this error

 

[arnelgp]

Public Function fncLog() As Boolean
    CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
        "select '" & CurrentObjectName & "'," & Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#")
    fncLog = True
End Function