How to save and record a button single click event's time to access field

Question

Develop PDMS via access. For production tracer ability, I need to record the each button click event's time, and save it to field. I use the function Now().

Answer 1

You'll have to create a public function and call it as part of each button's click event.

In a standard module you could do something along the lines of

'Req'd for fOSUserName() below
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
                                        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function BtnClickTracer(ByVal frmName As String, ByVal ctlName As String) As Boolean
    Dim db                    As DAO.Database
    Dim sSQL                  As String

    On Error GoTo Error_Handler

    Set db = CurrentDb
    sSQL = "INSERT INTO tbl_ClickTracking (FrmName, CtlName, ClickDate, Username) " & vbCrLf & _
           "SELECT '" & frmName & "' AS Expr4, '" & ctlName & "' AS Expr3, Now() AS Expr1, fosusername() AS Expr2;"
    db.Execute sSQL, dbFailOnError
    If db.RecordsAffected <> 0 Then
        BtnClickTracer = True
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: BtnClickTracer" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fOSUserName() As String
    ' http://www.theaccessweb.com/api/api0008.htm
    ' Returns the network login name
    Dim lngLen                As Long
    Dim lngX                  As Long
    Dim strUserName           As String
    
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function
'******************** Code End **************************

and then in each button's click event you'd call it

    Call BtnClickTracer(Me.Name, Screen.ActiveControl.Name)

---

Daniel Pineault, 2010-2018 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Thanks your reply.

We develop the PDMS via access. For production tracer ability, I add function now() to record the Command button click event's time. On related query there a field "print time" to save the value of now(). But now just save the last print time from now(). I want to save all value from function now() if click this button many times.

Sorry I cant add a photo attachment. 

https://social.msdn.microsoft.com/Forums/getfile/1356074

https://social.msdn.microsoft.com/Forums/getfile/1356075

Answer 3

Thanks your reply.

We develop the PDMS via access. For production tracer ability, I add function now() to record the Command button click event's time. On related query there a field "print time" to save the value of now(). But now just save the last print time from now(). I want to save all value from function now() if click this button many times.

Sorry I cant add a photo attachment. 

https://social.msdn.microsoft.com/Forums/getfile/1356074

https://social.msdn.microsoft.com/Forums/getfile/1356075

Answer 4

Hi,

Can't see your download file.

Daniel had given you a sample code. The code needs to modify for your own use.

You can still do it without the sample function. It will requires you to key-in the same (almost) code in each and every Buttons on your Form.

To use a single function for the button click, you'll need a Function with a input value (the button number).

From what you're asking, it seems you're doing an audit checks.

To call the function, use the button OnClick Event

Private Sub MyButtonNo1()

MyFunctionName("ButtonNo1")

End Sub

Create a VBA Module

Function MyFunctionName(MyString As String)

CurrentDB.Execute "INSERT INTO MyTableName (ClickTime, WhichButtonClick) VALUES (Now(), MyString)"

End Function

Hope the code sample will give you an idea.