Insert environment username into SQL in a MS Access form

Question

I have a MS Access form with an edit button.

Before it edits the record, it runs this code to backup to a changelog table:

DoCmd.RunSQL "INSERT INTO PlanningChangeLog SELECT * FROM Planning " & _
        "WHERE Bestelbon = " & Me.txtSearch & ""

I have created two new columns in this changelog table called Timestamp which uses the =Now() function, and UserAccount where I would like the SQL code to insert the environment username.

But I can't make sense of how to write this statement.

Any help would be appreciated.

Thanks

EDIT: This sql code seems to work, except it won't recognise my user variable. It always gives me a prompt to manually type it in.

Private Sub CommandEdit_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim user As String

user = Environ("username")

'eerst een check of het zoekveld niet leeg is
If Nz(Me.txtSearch.Value, "") = "" Then
    MsgBox "Geef een bestelbon in !!", vbExclamation
    Exit Sub
Else

'maakt een backup van de originele record
'DoCmd.RunSQL "INSERT INTO PlanningChangeLog SELECT * FROM Planning " & _
    '"WHERE Bestelbon = " & Me.txtSearch & ""
    
'DoCmd.RunSQL "INSERT INTO PlanningChangeLog SELECT [ID], Now() as [TimeStampEdit], user as [UserAccount], [Datum], [Bestelbon], [Transporteur], [Productnaam], [Tank] FROM Planning " & _
    '"WHERE Bestelbon = " & Me.txtSearch & ""
    
DoCmd.RunSQL "INSERT INTO PlanningChangeLog(" & _
    "ID, TimeStampEdit, UserAccount, Datum, Bestelbon, Transporteur, Productnaam, Tank) " & _
    "SELECT ID, Now() as TimeStampEdit, user as UserAccount, Datum, " & _
    "Bestelbon, Transporteur, Productnaam, Tank FROM Planning " & _
    "WHERE Bestelbon = " & Me.txtSearch & ""

Any suggestions on how to have it recognise the variable?

Try something like ..."INSERT INTO PlanningChangeLog SELECT p.*, null, '" & Environ("USERNAME") & "' FROM Planning p " &... — Tim Williams
– Tim Williams, Commented Aug 18 at 19:58
I'm sorry but I don't really understand that statement. Where does it specify that the environ(username) has to be inserted into the "UserAccount" field? — Don Quixote
– Don Quixote, Commented Aug 18 at 20:07
The values will be inserted in the order of the fields in the PlanningChangeLog table, and I'm assuming your new fields are the last two in that table. Note your original statement also has no explicit destination fields defined.... — Tim Williams
– Tim Williams, Commented Aug 18 at 20:09
It's best practice to always name the columns you are inserting into - then you never have this issue — Dale K
– Dale K, Commented Aug 18 at 21:08
You would normally use parameters to pass the values to the insert too - cos that statement is vulnerable to SQL injection. But maybe you can't use parameters in access. Also what happens if the user cancels out of the edit? Does it matter that you have a change tracking record? — Dale K
– Dale K, Commented Aug 18 at 21:12

taller · Accepted Answer · 2025-08-19 16:51:11Z

Instead of struggling with the string syntax and the correct order of the fields in an SQL statement, I would use ADODB.

My suggestion would be

    '' Constants for additional field names in the log table
    Const fldTIMESTAMP = "TimeStamp"
    Const fldUSERACCOUNT = "UserAccount"

    '' Create a connection to the current database
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection

    '' Test Data
    Dim BestelBon As Long
    BestelBon = 3  ' Set the order number to filter records

    ' Open source recordset from Planning table
    Dim rsSource As ADODB.Recordset
    Set rsSource = New ADODB.Recordset
    rsSource.Open "SELECT * FROM Planning WHERE BestelBon = " & BestelBon, conn, adOpenForwardOnly, adLockReadOnly

    If Not rsSource.EOF Then  ' Check if the source recordset is not empty
        ' Open target recordset for PlanningChangeLog table
        Dim rsTarget As ADODB.Recordset
        Set rsTarget = New ADODB.Recordset
        '' Assumption: there is only one hit
        rsTarget.Open "SELECT * FROM PlanningChangeLog", conn, adOpenKeyset, adLockOptimistic

        '' Add a new record to the target table
        rsTarget.AddNew
        Dim vFld As Variant
        For Each vFld In rsSource.Fields
            ' Copy fields from source to target, excluding TimeStamp and UserAccount
            If vFld.Name <> fldTIMESTAMP And vFld.Name <> fldUSERACCOUNT Then
                rsTarget.Fields(vFld.Name) = vFld.Value  ' Copy field value
            End If
        Next vFld

        '' Add TimeStamp and UserAccount to the new record
        Dim userName As String
        userName = Environ("USERNAME")  ' Get the current username
        rsTarget.Fields(fldTIMESTAMP) = Now  ' Set current timestamp
        rsTarget.Fields(fldUSERACCOUNT) = userName  ' Set the username

        rsTarget.Update  ' Save the new record to the target table
    End If

    '' Clean up resources
    If Not rsTarget Is Nothing Then rsTarget.Close
    rsSource.Close
    Set rsSource = Nothing
    Set rsTarget = Nothing
    Set conn = Nothing
End Sub

One commenter noted that it is better to use parameters. You'll need to update the code that defines the source recordset. Replace

    ' Open source recordset from Planning table
    Dim rsSource As ADODB.Recordset
    Set rsSource = New ADODB.Recordset
    rsSource.Open "SELECT * FROM Planning WHERE BestelBon = " & BestelBon, conn, adOpenForwardOnly, adLockReadOnly

with the following code

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    
    ' Define the SQL query with parameters
    cmd.CommandText = "SELECT * FROM Planning WHERE BestelBon = ?"
    cmd.CommandType = adCmdText
    cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , BestelBon)
    ' Execute the command and get the recordset
    Dim rsSource As ADODB.Recordset
    Set rsSource = cmd.Execute()

ValNik · Accepted Answer · 2025-08-20 19:23:10Z

0

See example with DAO QueryDef with parameters.
Query parameters may be defined in query as

"PARAMETERS param1 date;param2 varchar(255);
 SELECT ....
"

Doc reference

Public Function testInsert() As String
Dim sqlExpr As String
Dim qdf As QueryDef
Dim res As String
Dim prmUserAccount As Parameter
Dim prmSearch As Parameter
    sqlExpr = "PARAMETERS pUserAccount varchar(20), pSearch varchar(20); " _
        & " INSERT INTO PlanningChangeLog " _
        & " (field1, ChangeTimeStamp, UserAccount, field2) " _
        & " SELECT p.field1, Now() as ChangeTimestamp, pUserAccount as UserAccount" _
        & ", p.field2 " _
        & " FROM Planning p " _
        & " WHERE p.Bestelbon =pSearch "
    Set qdf = CurrentDb.CreateQueryDef("TestQDF", sqlExpr)
    Set prmUserAccount = qdf.Parameters!pUserAccount
    Set prmSearch = qdf.Parameters!pSearch
    prmUserAccount = Environ("UserName")
    ' or directly
    ' qdf.Parameters!pUserAccount=Environ("UserName")
    prmSearch = "event2"  ' Me.txtSearch
    qdf.Execute
    res = CStr(qdf.RecordsAffected)
    qdf.Close
    CurrentDb.QueryDefs.Delete ("TestQDF")
    testInsert=res
End Function

I replaced the "timestamp" column name with "ChangeTimeStamp" because "timestamp" is a reserved word in Access SQL.

You can create "static" query in Access query designer and use them in VBA

PARAMETERS pUserAccount varchar(20), pSearch varchar(20);  
INSERT INTO PlanningChangeLog  (field1, ChangeTimeStamp, UserAccount, field2)  
SELECT p.field1, Now() as ChangeTimestamp, pUserAccount as UserAccount, p.field2  
FROM Planning p  WHERE p.Bestelbon =pSearch

like

Set qdf = CurrentDb.QueryDefs("TestQDF")
    Set prmUserAccount = qdf.Parameters!pUserAccount
    Set prmSearch = qdf.Parameters!pSearch
    prmUserAccount = Environ("UserName")
    prmSearch = "event2"  ' Me.txtSearch
    qdf.Execute

edited Aug 20 at 19:23

answered Aug 19 at 15:04

ValNik

7,8381 gold badge8 silver badges18 bronze badges

6 Comments

Don Quixote Aug 19 at 17:35

I'll check it out when i get Home, thx

HansUp Aug 20 at 19:08

I don't understand why qdf.ReturnsRecords is useful here. Is RecordsAffected not suitable?

ValNik Aug 20 at 19:21

"qdf.ReturnsRecords" or "qdf.RecordsAffected" - just only for example. Yes, "qdf.RecordsAffected" is more suitable.

HansUp Aug 20 at 19:13

Why even mention the ChangeTimeStamp column in the insert statement when they want it to receive its default value?

ValNik Aug 20 at 19:33

Mybe, =Now() is default value for this column. But OP not mentioned about default value. OP include this column in query example. Whether or not to include this column in the insertion request is his choice. It doesn't matter for the example. However, the timestamp column in the INSERT INTO table (...,timestamp,...) statement will cause a syntax error in SQL

ValNik Aug 20 at 19:39

HunsUp, thanks for the comments and questions. They are useful.

Dale K · Accepted Answer · 2025-08-23 20:16:50Z

0

Okay, I finally managed the SQL syntax:

DoCmd.RunSQL "INSERT INTO PlanningChangeLog(" & _
        "ID, TimeStampEdit, UserAccount, Datum, Bestelbon, Transporteur, Productnaam, Tank) " & _
        "SELECT ID, Now() as TimeStampEdit, '" & user & "' as UserAccount, Datum, " & _
        "Bestelbon, Transporteur, Productnaam, Tank FROM Planning " & _
        "WHERE Bestelbon = " & Me.txtSearch & ""

This copies the record to a changelog table, and inserts a timestamp and user account field after the index field.

edited Aug 23 at 20:16

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Aug 21 at 7:24

Don Quixote

331 silver badge3 bronze badges

Collectives™ on Stack Overflow

Insert environment username into SQL in a MS Access form

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related