Report Open Message

[eMel]

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

vbaInet, His code gets me the same error. I removed the # because it showed in the results. I don't want that. Where is the missing single quote?

 

[vbaInet]

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

Use the link pbaldy gave you and show us the print out of your SQL variable.

Please follow this up on your original thread.

 

[eMel]

[

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
    Debug.Print "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT '" & Now() & "', '" & fOSUserName() & "', '" & LogEvent & "';"
DoCmd.RunSQL SQL
End Function

Immediate Window:
INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT '7/2/2014 10:21:48 AM', 'mebailey', ' Opened: Customers by Worker Report ';

And with pr2-eugin's 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
    Debug.Print "INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #" & Now() & "#,'" & fOSUserName() & "', '" & LogEvent & "';"
DoCmd.RunSQL SQL
End Function

Immediate:
INSERT INTO AuditTrail ( DateTime, UserName, FormName ) SELECT #7/2/2014 10:30:59 AM#,'mebailey', ' Opened: Customers by Worker Report ';

 

[pr2-eugin]

So it works now? I am not sure what the response was for?

 

[eMel]

The error I'm getting now is regarding: DoCmd.RunSQL SQL
Run-time error '3134':
Syntax error in INSERT INTO statement.

 

[vbaInet]

I don't know why I missed this all along. It's should be in this format:

INSERT INTO (Fields) VALUES (values);

You don't have a table you're inserting from so don't use SELECT. Replace SELECT with VALUES.

 

[pr2-eugin]

You don't have a table you're inserting from so don't use SELECT. Replace SELECT with VALUES.

I think that can be done that way too vbaInet. Although in this case it is merely values using the VALUES keyword is a much better option, however using SELECT should not cause a problem. That is my understanding.

 

[vbaInet]

Absolutely right! I completely forgot about that. I told you I've been out of the game for a long time

 

[eMel]

I tried that. Same problem. Would the structure of the table matter?
Autonumber | DateTime | UserName | Formname (Which I skipped) | Action | more fields

 

[vbaInet]

Well it just dawned on me, this is what happens when you use a reserved keyword as a field name. Your problem here is DateTime. Change it to something else or wrap it in square brackets.

INSERT INTO AuditTrail ([DateTime]... etc

If you don't change it then you will have to keep doing this everytime you reference that field either in a query and especially in code.

 

[eMel]

What's odd is that, I copied this code from another database. It worked fine. All I did was rearrange the fields.

I also had this as a suggestion to paste into the On Open cntrl. I get the same error. ???

  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

 

[vbaInet]

I don't understand this long string of code you've just pasted.

And did it work with what I mentioned in my last post?

 

[eMel]

It didn't work. The code is a suggestion that is getting the same error. After looking, I like it better.

 

[vbaInet]

The code it's in one line, I can't read it in that format.

Show me a Debug.Print of the last suggestion I made about the square brackets.

 

[eMel]

INSERT INTO EventLog ( [DateTime], UserName, Action ) VALUES '7/2/2014 3:24:51 PM' , mebailey, ' Opened: Customer Report ';

Fixed. Dim SQL As String should have been Dim SQL

I assume that I was trying to write a DateTime format as a string.

 

[vbaInet]

Please note you have written [Date]Time] instead of [DateTime].
And you've written the statement completely different i.e. using VALUES in place of SELECT. In this case you should enclose the values in parentheses i.e. ().

Copy and paste:

"INSERT INTO AuditTrail ([DateTime], UserName, FormName) VALUES(Now(), '" & fOSUserName() & "', '" & LogEvent & "');"