'Data has been changed' error

[katoc]

Hello.

I'm sure there is a simple solution but I'm out of ideas.

I have a form which feeds from a ODBD-linked table (oracle 920 driver). One of the fields is a memo-type 'Comments' field where the user can enter whatever they wish. I want to add a 'timestamp' like Now() to the end of it when they're finished typing, so I placed the following code in the 'beforeUpdate' even for this field.

Private Sub Issue_Comments_BeforeUpdate(Cancel As Integer)
    Issue_Comments = Issue_Comments & Chr(13) & Chr(10) &  Now() & Chr(13) & Chr(10)
End Sub

However, when I go to the next record and come back to the previous record and try to edit the comments field again it gives me the error:

'Data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.'

Any ideas? I tried using the saverecord command with no results.

 

[Remou]

Is Issue_Comments the name of the field as well as the name of the control? If so, try renaming the control to, say, txtIssue_Comments and change the code to reflect this.

 

[katoc]

Hmm. I tried renaming the control and made the necessary changes in the code but I'm still seeing the same error.

 

[Remou]

What happens if you specifically save the record with Me.Dirty=False in the After Up date event?

 

[katoc]

I placed the code: me.dirty = false in the Form_AfterUpdate event with no results. Did you mean to place it in the control's afterUpdate event?

 

[Remou]

Either should have worked, as far as I recall. Just to double-check, is everything working ok when the above line is commented out?

 

[maxhugen]

Purely guessing, but could it have something to do with some Oracle setting, eg caching, or 'when-to-commit' ?

What happens if you open the table itself and try the same changes?

Max Hugen
Australia

 

[katoc]

It's a SQL Server table. I previously stated it was Oracle. I was mistaken.

I tried entering the data in SQL Server with no errors. The field is of type 'text'. Does that make a difference? Even if I don't insert a timestamp programmatically, it still gives me that error when I click on the next record and return to the original.

 

[Remou]

What happens if you create an autoform from the linked table, that is, a form with all the fields and no code, can you edit the data?

 

[katoc]

I've created a form that only uses fields from this linked table. No fancy filtering. I can edit any other field (dates, strings, title, etc...), but this memo/text field is the one causing problems. I can enter data into it and next 'next record'. If I come back to it and try to edit the data in the memo/text field or even add another sentence or word it says 'data has been changed, etc...' Once I hit 'ok', it allows me to continue entering data.

It must be how the record is committed. I just don't know where to intercept it and let it know that it has already been committed.

 

[Remou]

Could this be the problem:

http://support.microsoft.com/kb/302492

 

[katoc]

Yes, most definitely!!

Adding the me.requery (since I'm not using subforms) fixed the issue. So this means even if the sql table has been updated, the form in MS Access needs to be updated as well and the me.requery does the job.

I used the bookmark property to bring the focus back to the correct record since me.requery brings the form to the very first record which could get annoying after a while.

Thanks!!