Write conflict

[ylp]

Hi,
I have a subform with two textboxes(data bound),let's say txtA and txtB, and one combobox with 2 different values( let's say 5,6).

For both the combo and txtA,I have AfterUpdate events, Combo_AfterUpdate event does calculation, like txtA*combo ( is user slect 5,then txtA*5,otherwise txtA *6),save data into database,but txtA's_AfterUpdate event is only to call combo_AfterUpdate event. And displays result in txtB .

It works fine until I close the form, got error:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
You are then given the following options: Save Record, Copy to Clipboard, or Drop Changes.

Thanks for help!

 

[SOS]

Sounds like you have a bound field that is updating and then you are running code to update the same source. You either need to not do that or issue a save of the record before doing the other update.

 

[ylp]

Thanks so much!
Before I have the txtA_afterUpdate, No error. but if user update txtA value, the calculation result does not change, that's why I add this event there.

I tried:
RunCommand acCmdSaveRecord in the txtA_afterUpdate event before call Combo_afterUpdate, but got the error twice,one is after txtA updated, another is when closing the form.

Any ideas, thanks!

 

[SOS]

What code do you have in the txtA_AfterUpdate event?

 

[ylp]

just one line:

Call Me.combo_AfterUpdate

 

[SOS]

Okay, so what code is in the combo After Update event?

 

[ylp]

' set the total of totalFullDrums,GallonsPart and gallonsDayTank.
'Me.sumGallons = totalFullDrum + Me.GallonsPart + Me.GallonsDayTank
varSumGallons = totalFullDrum + varGallonsPart + varGallonsDayTank
'Set the lbs
varAcidBegin = varSumGallons * 14
'Display the updated values
Me.txtTotalFullDrums = totalFullDrum

Me.GallonsPart = varGallonsPart
Me.sumGallons = varSumGallons
Me.txtAcidBegin = varAcidBegin

strSQL = "UPDATE tblSulfuricAcidInv SET totalFullDrums_i = " & totalFullDrum & "," & "totalSumGallons_i = " & varSumGallons _
& "," & "totalAcid_i = " & varAcidBegin _
& " WHERE RecordID = " & Me.RecordID & " ;"

If (varSumGallons <> 0) Then
CurrentDb().Execute strSQL, dbFailOnError
End If

 

[SOS]

Well, this would be the part that is causing the problem:

...truncated for brevity
 
Me.GallonsPart = varGallonsPart
Me.sumGallons = varSumGallons
Me.txtAcidBegin = varAcidBegin

strSQL = "UPDATE tblSulfuricAcidInv SET totalFullDrums_i = " & totalFullDrum & "," & "totalSumGallons_i = " & varSumGallons _
& "," & "totalAcid_i = " & varAcidBegin _
& " WHERE RecordID = " & Me.RecordID & " ;"

If (varSumGallons <> 0) Then
CurrentDb().Execute strSQL, dbFailOnError

You are assigning values to the form and then trying to update the table by executing this query. Why are you doing both?

 

[ylp]

Actually, assigning to the form is because these values are based on the calculation, and then write to DB. do you mean I cannot data bound these values?

Also tried:

If Me.Dirty = True Then Me.Dirty = False

Still got the error?

Any other ideas? Thanks

 

[ylp]

Thanks very much!

You are right, assigning values to the form and then trying to update the table by executing the query caused the problem. Me.requery solved the problem.

Thanks, this forum is great!!

 

[SOS]

Glad you got it sorted.