Report Open Message (1 Viewer)

[eMel]

I'm trying to get a record written to a table showing when a table opens. I want to see Date/Time, UserName, "***Report has been opened." I'm having issue here:
Public Function LogEvt()
Dim SQL As String
SQL = "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #" & Now() & "#, fOSUserName(), '" & LogEvent & "';"
DoCmd.RunSQL SQL
End Function

I was getting errors with INSERT INTO. DoCmdRunSQL is highlighted in yellow, so I assume there is still an issue. SuggestionS?

 

[KenHigg]

Looks like you need to resolve the fOSUsername()

SQL = "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #" & Now() & "#,'" & fOSUserName() &'", '" & LogEvent & "';"

???

 

[eMel]

Didn't work.

 

[vbaInet]

What exactly is the error message?

 

[eMel]

DoCmd.RunSQL SQL is highlighted in yellow. Runtime 3134.

 

[vbaInet]

Ok. Is that the exact full error message you're seeing?

 

[eMel]

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

 

[vbaInet]

Print your SQL statement to the Immediate Window using Debug.Print and see the result (or paste it here).

To get to the Immediate Window go the VBA code editor > click Debug menu > click Immediate Window. That's where you need to look after running your code:

Debug.Print "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #" & Now() & "#,'" & fOSUserName() &'", '" & LogEvent & "';"

 

[pr2-eugin]

This is what Ken's code should have been.

SQL = "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #"  & Now() & "#,'" & fOSUserName() & "', '" & LogEvent  & "';"

The single quote after the fOSUserName is the trouble maker. Edit and see if it works. If not as vbaInet has requested, show the generated SQL.

 

[vbaInet]

Good spot there sir! I've been looking at my screen for too long today

 

[eMel]

Thank you for that one. That line looks ok. Now, I'm doing debug.print
on DoCmd.RunSQL; SQL. .RunSQL is highlighted in blue

 

[vbaInet]

You can't print a method (or command). You print only text.

 

[eMel]

http://www.access-programmers.co.uk/forums/showthread.php?t=266335

My desktop won't let me reply, so I'm having to do this ????? I'll reply with my phone.

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

Run-time error '3134':
Syntax error in INSERT INTO statement.
The RunSQL is highlighted in yellow.

 

[eMel]

Here's my full 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

DoCmd RunSQL SQL is highlighted in yellow , with the 3134 error.

 

[pbaldy]

Re: http://www.access-programmers.co.uk/forums/showthread.php?t=266335

This may help:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand you only have # on one side of the date/time value.

 

[vbaInet]

I thought you said it was working? If you want to see the contents of the string SQL, Debug.Print the SQL variable.

 

[vbaInet]

Re: http://www.access-programmers.co.uk/forums/showthread.php?t=266335

Previous thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=266335

pr2-eugin spotted the problem in your SQL string (which was a missing single quote) and proposed a solution, but you have now removed the # and not included the missing single quote. Copy and paste what pr2-eugin and test it.

 

[eMel]

Why do I get ads?

 

[vbaInet]

If you're a paid member you don't get ads (depending on your level of membership) plus you get some other added benefits.

Can we see what you say is the proper output?

Just replace that line of code with what pr2-eugin gave you. He fixed the syntax problem but your new code doesn't reflect any of that.

 

[pr2-eugin]

Any reason why you have missed # tag at the end of Now()? If you need to learn how to use Immediate window, this is how : http://www.baldyweb.com/ImmediateWindow.htm