Open Report Audit

eMel · July 2nd, 2014, 10:12 AM

I'm trying to get a message written to a table when a user opens a report.

Code:

Option Compare Database

Public LogEvent As String

Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim sName
Dim lngLen As Long, lngX As Long
Dim strUserName As String
Dim userID As String
    strUserName = String$(49, 0)
    lngLen = 50
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If

End Function
'******************** Code End **************************

Public Function LogEvt()
Dim SQL As String
    SQL = "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #" & Now() & ", fOSUserName(), '" & LogEvent & "';"
DoCmd.RunSQL SQL
End Function

I get: Run-time error'3134'
Syntax error in INSERT INTO statement.

When I click the debug button, DoCmd.RunSQL SQL part is highlighted in yellow. Suggestions, please? Thanks.

mmcdonal · July 2nd, 2014, 01:11 PM

First, change the audit table to put a default value in the DateTime field so you don't have to capture that. Use Now() for the date and time. Then try this on the On Open event of the report:

Code:

 
Dim sSQL As String
Dim sUser As String
Dim sForm As String
 
sUser = (Environ$("Username"))
sForm = Me.Report.Name

    sSQL = "INSERT INTO AuditTrail (UserName, FormName) VALUES ('" & sUser & "', '" & sForm & "')"
  
DoCmd.RunSQL SQL

I hope that helps.

eMel · July 2nd, 2014, 02:42 PM

Run-time error '3129':

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

I get the proper data in Immediate window but DoCMD.RunSQL SQL is highlighted in yellow.
?????

I copied your code directly into the On Open control.