VBA SQL Update with variables

Ivan Howard

Registered User.
Local time
Today, 18:36
Joined
May 12, 2009
Messages
11
Hi All,

Please could someone kindly help me with this issue. I'm sure it is something simple, but I don't know what I'm doing wrong.

All I want to do is update a table with the Windows Username of the person importing a spreadsheet.

I have imported the spreadsheet successfuly into a temporary table and now want to update it. The code just doesnt work.

Code:
Public Function UpdateDateAndBy()
    Dim SQL As String
    
    Dim WINUSERNAME As String
    
    WINUSERNAME = Environ("UserName")
    
    SQL = "UPDATE tblMainData SET ImportedBy=" & WINUSERNAME
    
    DoCmd.RunSQL SQL
End Function

Any help is greatly appreciated.

Thanks.
Ivan
 
The value of the variable must be bounded by single quotes (apostrophes) for a string. The pound sign (#) is used to delimit date/time values

Code:
Public Function UpdateDateAndBy()
    Dim SQL As String
    
    Dim WINUSERNAME As String
    
    WINUSERNAME = Environ("UserName")
    
    SQL = "UPDATE tblMainData SET ImportedBy=[COLOR="Red"]'[/COLOR]" & WINUSERNAME & "[COLOR="Red"]'[/COLOR]"
    
    DoCmd.RunSQL SQL
End Function
 
Ivan,

I have made a simple test with my table and your code.
I have adjusted to make it work.
I did use my own table name.
Here is code (Acc2003)

Public Function UpdateDateAndBy()
Dim SQL As String
Dim db As DAO.Database
Dim WINUSERNAME As String
Set db = CurrentDb
On Error GoTo UpdateDateAndBy_Error

WINUSERNAME = Environ("UserName")

SQL = "UPDATE Invoicetest SET ImportedBy='" & WINUSERNAME & "';"

'DoCmd.RunSQL SQL
db.Execute SQL, dbFailOnError
On Error GoTo 0
Exit Function

UpdateDateAndBy_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure UpdateDateAndBy of Module AWF_Related"
End Function
 
You're welcome; good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom