Public Function fncLog() As Boolean
CurrentDb.Execute "insert into tblLog(QueryName, [Last Run]) " & _
"select '" & CurrentObjectName & "',#" & Now() & "#"
fncLog = True
End Function
Thanks so where will this function sit?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):
Code: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();
Ok thanks so i have created the Module and you say add this to my current queryon a Module.
See my VBA Beginner blog:-how would i call the fnclog
Not sure what you mean?cant run the function i think as the query is an append query
Sorry i mean i cant call the function from the query using Exp: [fncLog()]Not sure what you mean?
Where do you call the query from? In other words which event calls the query?i cant call the function from the query
yeah i have a button on a form that runs the queryWhere 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?
i have a button on a form that runs the query
Thanks worked by the name captured is the Form Name and not the 3 queries them self that got ran when the button was clickedThen you need to call the function ArnelGP provided from that command button.
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?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):
Code: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();
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?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.
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 errorthen use format() on the function:
Code: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
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