VBA / SQL to update a table

[fat controller]

I am trying to have a Before Update event update a field in a table, but I must confess I have never used VBA/SQL to update a table in this way. I have tried the follwing (trying to guess my way along), but I get an undeclared variable error which highlights the table name:

    Dim sUserName As String
    Dim RRID
    
    RRID = Me.txtTrackerID.Value
          
    sUserName = Environ$("username")
    
Update RouteRecordsRevTracker
Set LastWorkedOnBy = sUserName
WHERE RRTrackerID = RRID

Can anyone direct me as to where I am going wrong please?

 

[Minty]

You need to execute the sql string, something like

    Dim sUserName As String
    Dim RRID
    Dim sSql As String
    
    RRID = Me.txtTrackerID.Value
          
    sUserName = Environ$("username")
    
    sSql = "Update RouteRecordsRevTracker "
    sSql = sSql & "Set LastWorkedOnBy =  '" & sUserName & "' "
    sSql = sSql & "WHERE RRTrackerID = "& RRID & " ;"
 
    Debug.Print sSql              [COLOR="Green"]'Comment this out once you have it working[/COLOR]
    CurrentDb.Execute sSql

The above assumes that RRID is an Number, as you haven't specified in the Dim statement what it is.

 

[CJ_London]

try

currentdb.execute("Update RouteRecordsRevTracker Set LastWorkedOnBy = '" & sUserName & "' WHERE RRTrackerID = " & RRID,dbfailonerror)

 

[fat controller]

You need to execute the sql string, something like

    Dim sUserName As String
    Dim RRID
    Dim sSql As String
    
    RRID = Me.txtTrackerID.Value
          
    sUserName = Environ$("username")
    
    sSql = "Update RouteRecordsRevTracker "
    sSql = sSql & "Set LastWorkedOnBy =  '" & sUserName & "' "
    sSql = sSql & "WHERE RRTrackerID = "& RRID & " ;"
 
    Debug.Print sSql              [COLOR=Green]'Comment this out once you have it working[/COLOR]
    CurrentDb.Execute sSql

The above assumes that RRID is an Number, as you haven't specified in the Dim statement what it is.

Works like a charm! Thank you

try

currentdb.execute("Update RouteRecordsRevTracker Set LastWorkedOnBy = '" & sUserName & "' WHERE RRTrackerID = " & RRID,dbfailonerror)

This one came back with a Syntax Error, no idea why?

 

[CJ_London]

no ideas, it should produce exactly the same sql string as that suggested by Minty. Just condensed into one line of code rather than four

 

[Minty]

I get paid by the line... if only

I did it that way for clarity, when dealing with more elaborate query strings I find it easier to read in that sort of layout, just my preference.

 

[fat controller]

no ideas, it should produce exactly the same sql string as that suggested by Minty. Just condensed into one line of code rather than four

Figured out why - I had the code firing too early, which meant that RRID was null, hence the syntax error. All good now