I don't know how FoxAudit works, but it looks like it also keeps track of INSERTed records AND that the before value would be shown as blanks, zero and NULL in such cases. Correct?

The user clicks "New Line" button and the code puts a new key value into a MEMVAR and then performs a INSERT-SQL using the field/value format with the MEMVAR having the key value included in it.
- Is this when FoxAudit captures the change info?... or does it only do so at TABLEUPDATE()?
- Are you checking the return value from TABLEUPDATE()?

You say "But, when this bugs crops up, even the Primary Key is changed to a new key. Nowhere in the system do I have a replace that replaces the primary key.", and later you say "By all reasoning, this can't be happening, but it is. An insert command is overwriting an existing record.".
So when the situation is observed afterwards you end up with:
1) The involved "New Line"-related record having a primary key value HIGHER than originally obtained and stored in the MEMVAR, the balance of the data exactly in accordance with the data pertinent to the "New Line" click, but all of this happening to an existing record belonging to some OTHER header.
2) Some other header record having lost its Line record, becoming 'orphaned'.

You also say that there is no pattern as regards the identification or placement of the record that GOT OVERWRITTEN.
That's really unfortunate because a scenario might be wildly plausible if it the overwritten record was always a very recently written one itself. It would involve the header count being wrong by at least 1.

You also say that "This effectively orphans the header record (parent with no children).".
Is it possible that this is the only condition that you can detect - could there be other cases when a record is overwritten but it is not the sole record attached to a header and so no 'orphaning' happens? In other words, might there be other headers out there with Line records that have suddenly been taken away from them BUT they still have Line records so are not 'orphaned'?

I guess it is legitimate to also include FoxAudit itself as a suspect in this problem.
You say there is no place where a REPLACE on the key value is done. Have you included all STORED PROCEDURES in your confirmation?

I've left the old stuff in here for easier reference.



>No, when the bug happens, every field from an already existing record gets overwritten with new data.
>
>
>>OK, I see.
>>
>>So all values except the keys are legit for the originally intended record?
>>
>>Is the unexpected key ALWAYS HIGHER than the original intended key?
>>
>>I surmise by your INSERT sample below that you are calling the Nextkey function yourself, not using the "Default Value" of the .DBF (.DBC) to do that, correct?
>>
>>Something that came up a while back that may be worth trying... inserting 2 dummy fields, the first Integer and the second Char(10) was reported to have been very helpful in reducing 'lost data' in one case. Might be worth a shot.
>>
>>good luck
>>
>>
>>>I think your understanding is a little wrong. Yes, I am doing an insert, but here is what happens. The table in questions has the following fields:
>>>
>>>ADL_KEY: Primary key for the table.
>>>ADL_ADH_KEY: Foreign key to the order header table.
>>>Other fields as needed, such as description, amount, etc.
>>>
>>>Before the bug happens, ADL_KEY would be 1, and ADL_ADH_KEY would be 1. After the bug happens ADL_KEY would be 2, and ADL_ADH_KEY would be 2.
>>>
>>>This effectively orphans the header record (parent with no children).
>>>
>>>The insert that causes this would look something like this:
>>>
>>>INSERT INTO ADLINES (ADL_KEY, ADL_ADH_KEY, DESCRIPTION, AMOUNT) VALUES (lnNextNum, ADHEADER.ADH_KEY, ADCOST.DESCRIPTION, ADCOST.AMOUNT)
>>>
>>>All other tables (adheader and adcost) are positioned on the correct records.
>>>
>>>
>>>0. Yes, the table is in a .DBC.
>>>1. Yes, the primary key is an integer.
>>>2. Yes, the primary key is the first field in the record.
>>>3. No, the insert is not inside a transaction.
>>>4. Yes, the table is buffered with Optimistic Table Buffering.
>>>5. The insert format is fields/values.
>>>6. Yes, when this bug happens the primary key always gets changed.
>>>7. No, there is no relationship that is apparent between the overwritten key and the key that does the overwriting.
>>>8. Not sure. The hardware people try to keep that stuff secret.
>>>9. VFP 6 - Newest SP.
>>>
>>>Thanks
>>>
>>>
>>>>>Forgot to mention that part. I have rebuilt the indexes. Still same thing. This bug only happens about once a month.
>>>>>
>>>>>>>Ok, this is a weird one. I have an order entry system that has a Order Header Table and a Line Items Table. The user can start an order and then add multiple line items. This part works fine. Occasionally, however, adding a new line overwrites an existing line (always from a different order). When the user clicks the "New Line" button, the code finds the next available key and then performs a SQL Insert command to insert the new line. But, when this bugs crops up, even the Primary Key is changed to a new key. Nowhere in the system do I have a replace that replaces the primary key.
>>>>
>>>>If I understand correctly a SQL Insert is done, with a value in the Primary Key field already to add a new line to that Order (of the primary key) and a line does get written BUT it has a different Primary key than intended and so becomes associated with another order. Is that correct? And all information in the new record EXCEPT the Primary Key is exactly as intended?
>>>>
>>>>0. Is the table in question in a .DBC?
>>>>1. Is the primary key a Integer field?
>>>>2. Is the Primary key the first field in the record?
>>>>3. Is the INSERT-SQL under a TRANSACTION?
>>>>4. Is the table buffered and if so, which style?
>>>>5. What format is the INSERT-SQL (fields/values or FROM ARRAY|MEMVAR|NAME or a sub-SELECT?
>>>> - If FROM..., is there any chance that the primary key is being changed by something inadvertently?
>>>>6. Can you be certain that this never happens within a set of new lines too (i.e. not the primary key mucked up but maybe the line key)
>>>>7. Is there any relationship between the intended key and the actual written key, like maybe it's always nn away in value?
>>>>8. What OS/SP for the workstation and NOS?
>>>>9. What version/SP of VFP?
>>>>
>>>>Sounds like a bad bug for sure.
>>>>
>>>>
>>>>>>>
>>>>>>>I know that an old record is getting overwritten because I am using TakeNote's FoxAudit, and it shows when it happened and who did it, the value it was before and the value it changed to.
>>>>>>>
>>>>>>>By all reasoning, this can't be happening, but it is. An insert command is overwriting an existing record. Does anyone have any suggestions on this???
>>>>>>
>>>>>>Might be a corrupted index. Rebuild if possible (i.e. DELETE TAG ALL, etc.) (may not be easy if you have RI set up to use indices) rather than REINDEX.