Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 2nd, 2014, 10:12 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2014
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Open Report Audit

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.
Reply With Quote
  #2 (permalink)  
Old July 2nd, 2014, 01:11 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default Easy

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.
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old July 2nd, 2014, 02:42 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2014
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Last edited by eMel; July 2nd, 2014 at 02:52 PM..
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Crystal Report from hyperlink aspBegineer83 Crystal Reports 0 November 24th, 2007 02:15 PM
open Access report from VB6 submit Pro VB 6 7 December 6th, 2005 04:04 PM
DoCmd.Open Report Bug - Please help! bridog39 Access VBA 2 June 29th, 2004 03:45 PM
Error to open Report codaman Pro VB 6 0 December 11th, 2003 12:05 PM
On click open a report vita Access 4 October 2nd, 2003 06:47 AM



All times are GMT -4. The time now is 11:02 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.