I'm trying to record user logons 3

[Quehay]

I'm trying to record user logons with code run in the splash form. I could do it with a recordset but want to figure out why the following isn't working:

CODE:

[tt]

Dim strUserName As String
Dim strSQL As String

strUserName = Trim(apiUserName)

strSQL = "INSERT INTO tbl_Logons(UserName) " & _
"SELECT " & Chr(39) & strUserName & Chr(39) & " AS [UserName];"



txtUser = "Current User: " & strUserName

DoCmd.RunSQL strSQL
[/tt]

ERROR:

*Note the difference in the debug value of the strSQL variable between the check value while stepping and the error description returned--there's an extra quote on the left side of the variable.

I've tried with both CHR(34) and CHR(39)

Taking the strSQL from a breakpoint and running it from the query window works fine.

THANKS!

[tt]

?strsql
INSERT INTO tbl_Logons(UserName) SELECT "doejohn " AS [UserName];

?err.Number
3075

?err.Description
Syntax error in string in query expression '"doejohn'.

[/tt]

 

[bsupport]

Try placing this part, Chr(39) & strUserName & Chr(39) of your select statement into a string variable first then use the string variable in your sql statement:

Dim strVar as string
strVar = "chr(39) & strUserName & chr(39)"

strSQL = "INSERT INTO tbl_Logons(UserName) " _
& "SELECT " & strVar & " AS [UserName];"

Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[Quehay]

bsupport,

Thanks! It works this way. Is this a bug or a progammer deficit (mine <g>) that it doesn't work the other way?

 

[Quehay]

Follow up...

The problem was in the API call, which I wasn't using when I tested bsupport's remedy. The following code to retrieve the NT logon name always leaves one extra null character at the right.

[tt]
Private Declare Function apiGetUserName Lib &quot;advapi32.dll&quot; Alias _
&quot;GetUserNameA&quot; (ByVal lpBuffer As String, nSize As Long) As Long

'Holds current Site Key for use in separate child forms
Public g_lngSiteKey As Long

Public Function apiUserName() As String
'Returns the computername
On Error GoTo Exit_apiUserName

Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 255
strCompName = String$(lngLen, 0)
lngX = apiGetUserName(strCompName, lngLen)
If lngX <> 0 Then
apiUserName = Left(strCompName, lngLen)
Else
apiUserName = &quot;Not logged in&quot;
End If

Exit_apiUserName:
Exit Function

End Function[/tt]

Here's the patch that I used in the form routine:

[tt]
'Get rid of extra null character from api that cause SQL error
strUserName = Left(strUserName, Len(strUserName) - 1)[/tt]

 

[bsupport]

Glad it worked for you and that you figured out another way.
Happy Holidays! Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[tomschafer]

To shorten-up your code you can also use the environ function (reads environment variables) instead of the API call...

environ(&quot;username&quot

 

[Quehay]

Pezamystik,

Thanks for the post--this is totally new to me...does this get an Access security logon name or an NT logon name? (I use NT for user authentication rather than activated Access Security)

 

[tomschafer]

Actually it's a function that reads Windows Environment Variables. The example I posted would return the NT User Name.