Update table one record at the time

  • Thread starter Thread starter Barb
  • Start date Start date
B

Barb

Hi
I'm looking for a code that will let me update values in
one table with values from different table. It has to
read ProductID, find the same in MainTable and then
update 4 different fields for that ProductID. Then read
next ProductId find it in Main Table and update 4 fields.

Can anyone help?

Thanks
Barb
 
There is more than one way to do this:

Dim Qd as Querydef
Set Qd = CurrentDB.CreateQueryDef(vbNullString)
' Start a loop here
Qd.SQL = "UPDATE TargetTable SET Field1, Field2, Field3, Field4 VALUES
(Val1, val2, val3, val4) WHERE RecordID = Criteria"
Qd.Execute

or

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset
with rst
' Start a loop here
..Open MyTable, CurrentProject.Connection
..FindFirst "RecordID = Criteria"
if Not .NoMatch then
.Fields(0) = Value1
...
.Update
endif
end with

It is air code - please check. But this is the idea.
Pavel
 
It has to
read ProductID, find the same in MainTable and then
update 4 different fields for that ProductID. Then read
next ProductId find it in Main Table and update 4 fields.
Click to expand...

This sounds like a Design Problem. Try reading up on relational design and
discover why the product details need to stay in the Products table, and
when a Main refers to a Product, the details are found there. To see the
Mains and the product details all in one plase, you create a query --
that's what queries are for.

Hope that helps


Tim F
 
Back
Top