error 3197 another user changing data

[XV1957]

Hi there, I have been receiving error 3197 lately "stopping the db engine because another user attempts to change the same data".
There being only one user, the reason must be bad programming.

Here is the situation:
I have a main form and subform which displays the Purchase Order Header (main form) and Purchase Order Lines (Subform) (see attachment)
One command button causes all prices on my puchase lines to be updated.
Another button does a whole bunch of transformations on the Order Header and lines. For example, when I book receipts on a Purchase order:
- I create a new Header record with another order number, in fact another prefix followed by a number (say RE(ceipt)9999 instead of PO9999) (the key to all my files are autonumbers)
- I create new Order Lines with the corresponding references to the Header
- I then update the old Header record and set a PurchOpen field to closed. It is here that my program crashed, even though this code to set the the PurchOpen field on the old record has been there for ages. So the change must be caused by recent changes to the code I made, and I cannot figure out where to look. I had just updated all the line prices (Update Prices command)

I can only see two possible reasons:
1. I somehow update the same record in the same procedure and sin against an unknown rule here
2. I Changed data with one procedure on the form, and then the same data with another procedure on the same form.

Any ideas where to start looking?

 

[CJ_London]

possibly you have another copy of the db open

don't understand your form, but if it is displaying one of the records in edit mode that is to be updated this can cause a conflict

the change must be caused by recent changes to the code I made, and I cannot figure out where to look

maybe, but without seeing your code it is impossible to comment

Suggest you revert to your original code and recheck the logic of your new code

 

[XV1957]

Hi CJ_LOndon,
thank you for your reply.
I am not sure I understand your words: "if it is displaying the records in edit mode, this can cause a conflict".
The form is bound to an underlying Table, and so is the subform. So all records can be updated in the form.
Do you mean that I could have changed something in the form, and pressed a command button that starts another procedure, while the first change has not been completed? This is why I start the procedure that is linked to that command button with a docmd.runcommand acCmdSaveRecord instruction.
I find it strange that I could run into this error within a single procedure. I will go and check my code, and test, test, test, ... but as of now, I am a bit at a loss. The compiler does issue any messages.

 

[CJ_London]

The compiler does issue any messages.

First, disable any error handling you have so error messages are not ignored

Second, are you familiar with break points, debug.print and the immediate window and stepping through the code? If yes - use those techniques, if no, read on

To set a breakpoint, click on the first line of code of your sub or function (i.e. after the declarations) (or any line if you know everything is 'good' up to that point and hit the F9 button, The line will be highlighted maroon with a maroon dot to the left.

Then run your sub by doing whatever you do on the form, The code will start to run and will pause at the first breakpoint it encounters, opening the vba window (if not already open) and highlighting the row in yellow.

Whilst the code is active (even if paused), providing you have declared variables properly you can hover over any variable and see its current value.

Or you can go to the immediate window (ctrl-G to open) and type in a ?followed by the name of a control, variable enter a formula etc to see its current value

or you can enter in the code debug.print followed by the name of a control, variable enter a formula etc which will print the result to the immediate window

To move to the next line of code, hit the F8 button or to continue the code to the next breakpoint or finish, hit F5

 

[XV1957]

CJ_London,
thank you so much for your help.
I was vaguely aware of break points, but have not used them up to now. I relied on the somewhat cryptic messages of the debugger of find my mistakes, but this time I got stuck.
Thanks again for helping us neophytes along.