INSERT INTO - two items, one row

[golfbum71]

Hello All,

I just came across this post:
http://www.mrexcel.com/forum/showthread.php?t=287465
And I've been able to add it in to my database -- Thank you very much SydneyGeek!

I noticed that there was a difference between Environ("username") and CurrentUser() -- namely that one is the actual computer user and the other is the logon they are using to get into the database.

So--in order to verify that users aren't farming out their user names, I'd like to add to the code.

I've created a CurrentUser column in the tblUserLog table...so I need to modify this:

    Dim sUser As String
    Dim sSQL As String
 
DoCmd.SetWarnings False
    sUser = Environ("username")
    sSQL = "INSERT INTO tblUserLog ( UserID )" _
        & "SELECT '" & sUser & "' AS [User];"
    CurrentDb.Execute sSQL
    'DoCmd.RunSQL sSQL
 
DoCmd.SetWarnings True

to something like

    Dim sUser As String
    Dim sSQL As String
    Dim curUser As String
DoCmd.SetWarnings False
    sUser = Environ("username") 'Name of person from system
    curUser = CurrentUser() 'name used to logon
    sSQL = "INSERT INTO tblUserLog ( UserID, curUser )" _ '<---This is where I have no clue
        & "SELECT '" & sUser & "' AS [User];"  '<-- I don't know how this
        ' works--there's no [user] I'm aware of in the table or in the form
        ' so I'm unsure how it works...
    CurrentDb.Execute sSQL
    'DoCmd.RunSQL sSQL

If I missed something like it on the board, I apologize and look forward to any links.

Thank you all for any help...

Cheers,
Geoff

 

[wongm003]

You could use...

    Dim sUser As String
    Dim sSQL As String
    Dim curUser As String

    DoCmd.SetWarnings False

    sUser = Environ("username") 'Name of person from system
    curUser = CurrentUser() 'name used to logon

    'INSERT INTO tblName ( Col1, Col2, etc... )
    'VALUE ( Val1, Val2, etc... );

    sSQL = _
      "INSERT INTO tblUserLog ( UserID, curUser ) " & _ 
      "VALUES ( '" & sUser & "', '" & curUser & "');"

    DoCmd.RunSQL sSQL

 

[golfbum71]

Thank you very much! It works like a charm. I was getting hung up trying to understand what the SELECT statement was doing... appreciate the help.

I'm new to SQL and from what I've read the syntax has to be exact for it to work...while I'm familiar with Excel VBA, I knew trying to figure this out on my own would have taken hours. I appreciate the help and hope I can help you or others in the future.

Cheers,
Geoff