Hello Experts,

I'm trying to copy some values from one rec to another within the ame table for repeat orders.

For example, how can i do something like:

  SET fld1 = fld1, fld2 = fld2, fld5 = fld5

  WHERE id of source rec = 123
  WHERE id of target rec = 456

As you see, i purposely omitted fld3 and fld4,, as i need those values unchanged.

How can i do this?
APD TorontoAsked:
Who is Participating?
conagramanConnect With a Mentor Commented:

Dim yourSQL As String ' this is a string to hold your SQL Update query
Dim var1 As String ' this is a string variable to hold the information you want to put into your record
Dim var2 As String ' this is a string variable to hold the information you want to put into your record you can make as many as needed

var1 = "yourtext"    '  this is where you add text to your variable
var2 = "yourtext2"     '  this is where you add text to your second variable

yourSQL = "UPDATE table SET tableFieldName = " & var1 & ",  secondtablename = " & var2 & " WHERE Id= 123 AND targetID = 456 ;"

DoCmd.SetWarnings False 'this keeps a popup from appearing asking you if you want to update the records
DoCmd.RunSQL yourSQL ' this runs the sql statement
'DoCmd.SetWarnings True 'this turns the popups back on < you want the popups on to warn you of problems just not this one
you are on the right track just use an sql update query

Dim yourSQL As String
Dim var1 As String
Dim var2 As String

var1 = "yourtext"
var2 = "yourtext2"

yourSQL = "UPDATE table SET tableFieldName = " & val1 & ",  secondtablename = " & val2 & " WHERE Id= 123 AND targetID = 456 ;"

'DoCmd.SetWarnings False
DoCmd.RunSQL yourSQL
'DoCmd.SetWarnings True
i just noticed i have var1 and val1  and var2 and val2   they should not be different
change the val to var or var to val : )
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

You can use this query:

UPDATE Tbl INNER JOIN (SELECT Tbl.Field1, Tbl.Field2, Tbl.Field5, 456 AS ID FROM Tbl WHERE (((Tbl.ID)=123))) As Qry1
 ON Tbl.ID = Qry1.ID SET Tbl.Field1 = [Qry1]![Field1], Tbl.Field2 = [Qry1]![Field2], Tbl.Field5 = [Qry1]![Field5]
WHERE (((Tbl.ID)=456));

Open in new window

update tbl a, tbl b
  SET a.fld1 = b.fld1, b.fld2 = a.fld2, a.fld5 = b.fld5
where a.id = 456 and b.id = 123
APD TorontoAuthor Commented:
als, can you explain your code?

also, everone, note that there is only 1 table in question.

i'm almost tempted to use 2 recordsets here
Can you post some sample data and what you expect it to be after the update?
>Accepted Solution:  Are you kidding?
dgmg? What gives?
i assumed he just wanted to know how to run an update query so i showed him how. What’s wrong with that?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.