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

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

SELECT * FROM yourTable WHERE fncLog();

Reactions: Uncle Gizmo

Number11

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610

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

..forever waiting. waiting for jellybean!
Local time Tomorrow, 04:12 Joined May 7, 2009 Messages 19,441 on a Module.

Reactions: Number11

Number11

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610 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?

Last edited: Oct 18, 2020

Uncle Gizmo

Nifty Access Guy
Staff member Local time Today, 21:12 Joined Jul 9, 2003 Messages 16,678 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

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610

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

Uncle Gizmo

Nifty Access Guy
Staff member Local time Today, 21:12 Joined Jul 9, 2003 Messages 16,678 cant run the function i think as the query is an append query Not sure what you mean?

Number11

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610 Not sure what you mean? Sorry i mean i cant call the function from the query using Exp: [fncLog()]

Uncle Gizmo

Nifty Access Guy
Staff member Local time Today, 21:12 Joined Jul 9, 2003 Messages 16,678 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

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610

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

Nifty Access Guy
Staff member Local time Today, 21:12 Joined Jul 9, 2003 Messages 16,678 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

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610 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

Last edited: Oct 18, 2020

Number11

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610

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

Number11

Member
Local time Today, 21:12 Joined Jan 29, 2020 Messages 610

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?