update last record

[basilyos]

hello guys,


i want to run an update query to update the last record in a table from multiple table

N.B: each table from where i want to bring data contains just one field and one record


this my code

UPDATE tbl_Sanctions SET tbl_Sanctions.Code01_01Year = [tbl_01Years_Code01].[txt_Text], tbl_Sanctions.Code01_05Year = [tbl_05Years_Code01].[txt_Text], tbl_Sanctions.Code01_10Year = [tbl_10Years_Code01].[txt_Text]
  WHERE (((tbl_Sanctions.ID)=DMax([ID],[tbl_Sanctions])));

any help?




i tried this way

Dim obj As New DataObject
        Dim lastID As Long
        lastID = DMax("ID", "tbl_sanctions")
        obj.SetText lastID
        obj.PutInClipboard

but how can i put the lastID as criteria in the update query

 

[June7]

Did you try building and running query object to perform that update? If it works then it can be translated into VBA code.

 

[GinaWhipp]

Actually you can put the variable in the UPDATE query if you SQL behind the Form...

    Dim strSQL As String
    
    strSQL = "UPDATE tbl_Sanctions " & _
                "SET tbl_Sanctions.Code01_01Year = [tbl_01Years_Code01].[txt_Text], tbl_Sanctions.Code01_05Year = [tbl_05Years_Code01].[txt_Text], tbl_Sanctions.Code01_10Year = [tbl_10Years_Code01].[txt_Text] " & _
                    "WHERE (((tbl_Sanctions.ID)=" & lastID & ")))"
              CurrentDb.Execute strSQL, dbFailOnError