Converting update query in Access to SP

[DirectOne]

I have a question on how to update 2 tables, linked and update a field in each. In the FAQ there was an example of updating using link tables but that does not seem to work because I updating both tables.

I have a orderheader table and an orderline table i need to change one field in each based on the order number, which they are linked on.

Here is how I do it in Access:

UPDATE DISTINCTROW OEORDHDR_SQL INNER JOIN OEORDLIN_SQL ON OEORDHDR_SQL.ord_no = OEORDLIN_SQL.ord_no SET OEORDHDR_SQL.status = "4", OEORDLIN_SQL.picked_dt = Forms![BBB Pick Ticket]!txtCurrentDate
WHERE (((OEORDHDR_SQL.status)="1") AND ((OEORDLIN_SQL.picked_dt)>=0) AND ((OEORDHDR_SQL.shipping_dt)<=[Forms]![BBB Pick Ticket]![ShipCutoff]) AND ((OEORDHDR_SQL.ship_to_country)=[Forms]![BBB Pick Ticket]![RFTC]) AND ((OEORDLIN_SQL.qty_to_ship)>0) AND ((OEORDHDR_SQL.hold_fg) Is Null) AND ((OEORDHDR_SQL.cus_no)="000000200025") AND ((OEORDHDR_SQL.ord_type)="O"))

What is the best way to do this, Do I need to update statments?

 

[chaoma]

update TableToBeUpdate
set
TableToBeUpdate.field=MatchingTable.field
From TableToBeUpdate INNER JOIN MatchingTable
ON...
where....


I don't think you would update 2 tables. Update one table at a time.