update query confirmation dialog box missing?

[DataMiner]

I am trying to run an update query that I've run for years with no issues. This morning when I run it from code I get the dreaded "duplicate values" key violation error message. OK, so the first thing I normally do to try to debug is to run the query from the db query window and have the confirmation message tell me how many records are affected. Expecting the usual dialog boxes, something like: "YOu are about to update 2000 records" and then when I click OK, something like "50 records failed to update due to kv errors".
But now, when I run the update query from the db query window, I get NOTHING. No confirmation, and no errors. Where did my confirmation messages go?

In addition: I've checked for duplicate values in the incoming data. There are none. And the way I have my query structured, duplicate values that already exist in the table I'm updating should not matter.

Here's the query:

UPDATE AR_Export1 LEFT JOIN AttributeRepairs ON (AR_Export1.ID = AttributeRepairs.PanelID) AND (AR_Export1.ImageNo = AttributeRepairs.ImageNo) AND (AR_Export1.Assyno = AttributeRepairs.AssyNo) AND (AR_Export1.RepCode = AttributeRepairs.RepCode) AND (AR_Export1.RefDes = AttributeRepairs.RefDes) SET AttributeRepairs.PanelID = ar_export1.id, AttributeRepairs.ImageNo = "unknown", AttributeRepairs.AssyNo = "0", AttributeRepairs.RepCode = ar_export1.repcode, AttributeRepairs.RefDes = ar_export1.refdes, AttributeRepairs.Pqty = ar_export1.pqty, AttributeRepairs.PartNumber = AR_EXPORT1.PARTNO, AttributeRepairs.POO = [pcacellresp];

The PK in AttributeRepairs is PanelID-ImageNo-AssyNo-Repcode-Refdes

I have already tried repair/compact, and importing stuff into a new, blank db. No change.

What's going on? Why aren't I getting the query confirmation messages? What else could be causeing the KV error? Any ideas?

 

[boblarson]

What's going on? Why aren't I getting the query confirmation messages? What else could be causeing the KV error? Any ideas?

If you used

DoCmd.SetWarnings False

at any point, and then don't turn them back on, that will cause the behaviour of no messages.

Go to the Immediate Window in the VBA IDE and type in

DoCmd.SetWarnings True

and then hit your Return/Enter key. That should turn them back on.

That is also why using the SetWarnings can be so dangerous - as it eliminates a lot of things, including save warnings when working with objects. So, you should always turn them back on when using them and you should always have the

DoCmd.SetWarnings True

as the first line in an Error handler so you don't miss something if the code goes to the error handler before turning back on the warnings.

 

[DataMiner]

I did the docmd.setwarnings true. No change. Any other ideas?

 

[boblarson]

Could be corruption then. You might try importing everything (import, don't copy and paste) into a blank shell and then see if it works. Also, make sure to turn off Name Auto Corrupt (I mean Name Auto Correct).

 

[DataMiner]

I had already tried that. This morning when I came in, the problem had corrected itself. With no action on my part. Strange and troubling!

One note, when I was having this problem, I was working from home via something called "VPN Client" (if that means anything to anyone outside our company...?) and using a windows Remote Desktop Connection.

Later this week I'll try it again from home and see if the problem recurs.

 

[boblarson]

the problem had corrected itself. With no action on my part. Strange and troubling!

It is a bit disconcerting and makes you wonder - when will it happen again?