update query (how to update record based into another table?)

[Guest]

Hi,
I would like to put a button on my form that will update records of TABLE1
matching the records of TABLE2.

- Both tables' fields are identical.
- Records to be updated on TABLE1 is only the ones that its primary keys
(EqpNo,date,CompCode) matching the ones in TABLE2.
- Fields to be updated are ETW, NOM, NOH&remark in TABLE1.
- Can not use EMPTY & APPEND method coz not all records from TABLE2 to be
used and also the new entry by maintenance clerk is on TABLE1.

FIELD TABLE1 TABLE2
EqpNo AT037 AT037
loc 10 10
date 06/26/06 06/26/06
smu 351 351
inspector 11 11
CompCode 1054 1054
backlog REPAIR PART REPAIR PART
warranty FALSE FALSE
renter FALSE FALSE
OEM TRUE TRUE
parts 3 3
ETW 09/25/06 09/30/06
NOM 1 2
NOH 1 3
completed FALSE FALSE
remark AIR FILTER BLOCKED REPLACE AIR FILTER

- I understand that my program need to compare the primary key of each
record in TABLE2 and compare it against TABLE1 records (from the 1st to the
last record) and update the one that matching the primary keys.

Could someone guide me in writing this program?
How to work with the FOR..NEXT or WHILE..WEND to work on each records?
Also I have no idea to write the UPDATE QUERY function in module (I used to
do this with the updatequery in query for a fixed criteria).

Any clue/help is really appreciated.

 

[Brendan Reynolds]

This is untested, but something along these lines should work ...

UPDATE Table1 SET Table1.ETW = 'NewEtwValue', Table1.NOM = 'NewNomValue',
Table1.NOH = 'NewNohValue', Table1.remark = 'NewRemarkValue' WHERE
Table1.EqpNo IN (SELECT Table2.EqpNo FROM Table2)

 

[Guest]

Hi Brendan,

Thx for the response, moreover to get me back into the track (i.e. there is
no need to go for ADO/recordset, a simple query will do).
A typical trap, forget to start from the basic

This one work perfectly as I want:

UPDATE tblBacklog INNER JOIN tblBacklogPlanXls ON (tblBacklog.EqpNo =
tblBacklogPlanXls.EqpNo) AND (tblBacklog.date = tblBacklogPlanXls.date) AND
(tblBacklog.CompCode = tblBacklogPlanXls.CompCode) SET tblBacklog.ETW =
[tblBacklogPlanXls].[ETW], tblBacklog.NOM = [tblBacklogPlanXls].[NOM],
tblBacklog.NOH = [tblBacklogPlanXls].[NOH], tblBacklog.remark =
[tblBacklogPlanXls].[remark];

--
Thank you,
patar

This is untested, but something along these lines should work ...

UPDATE Table1 SET Table1.ETW = 'NewEtwValue', Table1.NOM = 'NewNomValue',
Table1.NOH = 'NewNohValue', Table1.remark = 'NewRemarkValue' WHERE
Table1.EqpNo IN (SELECT Table2.EqpNo FROM Table2)