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

  • 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().

    Monday, November 5, 2018 2:55 AM

All replies

  • 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

    Monday, November 5, 2018 10:57 AM
  • 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

    Tuesday, November 6, 2018 12:58 PM
  • 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

    Wednesday, November 7, 2018 12:46 AM
  • 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.

    Wednesday, November 7, 2018 3:19 AM