UDATE QUERY PROBLEM

[Advisedwolf]

Hi,

I have a record status field, the options within this combo box are: Open, Closed, Rejected, Re-Opened

If a record is Re-Opened, then I have an update query that updates the appropriate status in two other tables.
The macro running this query is actioned upon the "On Close" property of the form itself.

The problem I have, is that this is updating ALL records, and I need it to ONLY update the record status of the particular record that was just set to Re-Opened.

I.e. Record 1 in table1 is re-opened,
therefor, record 1 in table2 and record 1 in table 3 is updated.

How can this be achieved?

Kind Regards

Gavin

 

[PHV]

No WHERE clause in your UPDATE query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Advisedwolf]

good point,

Just tried this and it doesn't seem to work
Here is a copy of the SQL statement of the update query.

UPDATE [TBL - FINANCE QUERY LOG] LEFT JOIN [TBL - PURCHASING LOG] ON [TBL - FINANCE QUERY LOG].[INVOICE NUMBER] = [TBL - PURCHASING LOG].[INVOICE NUMBER] SET [TBL - PURCHASING LOG].[STATUS OF RETURN IN PURCHASING] = [TBL - FINANCE QUERY LOG]![OVERALL QUERY STATUS], [TBL - PURCHASING LOG].[COMPLETED BY BUYER] = [TBL - FINANCE QUERY LOG]![OVERALL QUERY STATUS]
WHERE ((("where [overall query status]")="RE-OPENED" And ("where [overall query status]")="RE-OPENED"));


Hopefully the formatting hasn't been lost too much.

 

[PHV]

You may try this:
UPDATE [TBL - PURCHASING LOG] INNER JOIN [TBL - FINANCE QUERY LOG] ON [TBL - PURCHASING LOG].[INVOICE NUMBER] = [TBL - FINANCE QUERY LOG].[INVOICE NUMBER] SET [TBL - PURCHASING LOG].[STATUS OF RETURN IN PURCHASING] = [TBL - FINANCE QUERY LOG].[OVERALL QUERY STATUS], [TBL - PURCHASING LOG].[COMPLETED BY BUYER] = [TBL - FINANCE QUERY LOG].[OVERALL QUERY STATUS]
WHERE [TBL - FINANCE QUERY LOG].[OVERALL QUERY STATUS]="RE-OPENED";


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Advisedwolf]

Nice One!
seems to work! Thankyou for your assistance,

Gavin

 

[Advisedwolf]

Sorry, one more favour to ask.

Can you look at this code for me, its a similar scenario.

If the record gets rejected in the purchasing log, then the status for that record in the finance log needs to be changed to "Rejected" there too..

UPDATE [TBL - FINANCE QUERY LOG] LEFT JOIN [TBL - PURCHASING LOG] ON [TBL - FINANCE QUERY LOG].[INVOICE NUMBER] = [TBL - PURCHASING LOG].[INVOICE NUMBER] SET [TBL - FINANCE QUERY LOG].[OVERALL QUERY STATUS] = [TBL - PURCHASING LOG]![STATUS OF RETURN IN PURCHASING];
WHERE ((([TBL - PURCHASING LOG].[STATUS OF RETURN IN PURCHASING])="REJECTED"));


Kind Regards

Gavin

 

[PHV]

You really don't see how to amend my previous post for this similar scenario ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Advisedwolf]

i did try, but got a bundle of missing code errors. this is my attempt.... but get syntax errors.

UPDATE [TBL - FINANCE QUERY LOG] INNER JOIN [TBL - PURCHASING LOG] ON [TBL - FINANCE QUERY LOG].[INVOICE NUMBER]= [TBL - PURCHASING LOG].[INVOICE NUMBER] SET [TBL - FINANCE QUERY LOG].[OVERALL QUERY STATUS] = [TBL - PURCHASING LOG].[STATUS OF RETURN IN PURCHASING]
WHERE [TBL - PURCHASING LOG].[STATUS OF RETURN IN PURCHASING]="REJECTED";

ta