Getting round "Write Conflict" (1 Viewer)

Hi all,

Thanks in advance for any help.

I have a subform which contains the following details,

NumberId, Valid_From, Valid_To, In_Use

When the use changes the in_use item (yes/no) i want to write the current record to the table with the Valid from_from date entered.

Then i want to create a new record with the Valid-to set to current date. hence getting a history of changes to the record.

This i can achieve using

DoCmd.RunSQL "UPDATE Number_Details SET Number_Details.Valid_To = dateadd('d',-1,Date()) WHERE (((Number_Details.NumberDetails_Id)=" & Me.NumberDetails_Id & "))"

DoCmd.RunSQL "INSERT INTO Number_Details ( Number_Id, Valid_From, In_Use, CampaignDetails_Id ) SELECT " & Me.Number_Id & ", Date(), " & Me.In_Use & ", " & Me.CampaignDetails_Id

This works but comes up with a write conflict message, i am guessing due the fact the record is still showing in the form.

Is there a way of getting round this message

Thanks all

Chris

Chris,

Rather than running the Update query with the RunSQL command, use the following line to save the current record.


That should get rid of the write conflict.

Also, when auditing changes to a record, conventionally you would create a "snapshot" of the current record (a dup of the record that is about to be changed) in a separate table -- something called, say, TblHistory (or whatever suits you), running your code from the form's Before Update event to execute the archiving task:


Regards,
Tim