Error on update

[glenmac]

I'm getting this error when I click on succesive controls on my form.
"The Data has been changed.
Another user edited this record and saved the changes before you attempted to save your changes. Re-Edit Record.
OK"
I've got a number of controls that update a record. When I click OK to the error Dialogue box the record is updated properly. There is only 1 user for the db so I'd like to eliminate this Dialogue box. I've tried doCmd.setwarnings False (which stops the dialogue box to confirm updates) but I still get the mentioned dialogue box. Can this info box be eliminated or is there a way to put my updates in a transaction. I'm a newbie with access I'm used to working with SQL server and ASP so all help would be much appreciated. Here's the code for the form;
Private Sub AddIng_Click()
Dim Var As Variant
Dim Num As Variant
For Each Num In Me!IngNmPrc.ItemsSelected()
If Len(Num) <> 0 Then
Var = CDbl(Var) + Me!IngNmPrc.Column(2, Num)
End If
Next
Me!Pprice.Value = (Me!Pprice.Value + Var)
'MsgBox Var


Dim Var1 As Variant
Dim Stng As Variant
For Each Stng In Me!IngNmPrc.ItemsSelected()
If Len(Stng) <> 0 Then
Var1 = Var1 & Me!IngNmPrc.Column(1, Stng) & &quot;, &quot;
End If
Next 'Tezt Code for adding stuff
Me!IngInfo.Value = Var1 'This where the values of the text box are shown
'MsgBox Var1

Exit_AddIng_Click:
Exit Sub

Err_AddIng_Click:
MsgBox Err.Description
Resume Exit_AddIng_Click

End Sub


Private Sub Form_Unload(Cancel As Integer)
DoCmd.Close acForm, &quot;FillindOrdersOldCustomer&quot;
DoCmd.Close acForm, &quot;CustomersForm&quot;
End Sub

Private Sub PrcChk_Click()
DoCmd.SetWarnings False
Select Case PrcChk
Case 1
Me!Pprice = CDbl(Me!SinglPrcSm)
DoCmd.RunSQL &quot;Update Orders SET Size=('Small Single')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Case 2
Me!Pprice = CDbl(Me!SnglPrcmed)
DoCmd.RunSQL &quot;Update Orders SET Size=('Medium Single')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Case 3
Me!Pprice = CDbl(Me!SnglPrcLrg)
DoCmd.RunSQL &quot;Update Orders SET Size=('Large Single')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Case 4
Me!Pprice = CDbl(Me!DblPrcSm)
DoCmd.RunSQL &quot;Update Orders SET Size=('Small Double')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Case 5
Me!Pprice = CDbl(Me!DblPrcM)
DoCmd.RunSQL &quot;Update Orders SET Size=('Medium Double')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Case 6
Me!Pprice = CDbl(Me!Price)
DoCmd.RunSQL &quot;Update Orders SET Size=('Large Double')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Case Else
Me!Pprice = &quot;No Price&quot;
End Select
DoCmd.SetWarnings True
End Sub
Private Sub Command66_Click()
On Error GoTo Err_Command66_Click
Me!Pprice.Value = CDbl(Me!Pprice.Value) * CDbl(Me!Quantity.Value)
Var = Me!Pprice.Value
Var1 = Me!IngInfo.Value
Var2 = Me!Quantity.Value
Var3 = Me!PrID.Value


DoCmd.SetWarnings False
DoCmd.RunSQL &quot;Update Orders SET Custom_Ingedients=('&quot; & Var1 & &quot;')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
DoCmd.RunSQL &quot;Update Orders SET Quantity=(&quot; & Var2 & &quot where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
DoCmd.RunSQL &quot;Update Orders SET Product_ID=(&quot; & Var3 & &quot where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
DoCmd.RunSQL &quot;Update Orders SET Price=(&quot; & Var & &quot where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
DoCmd.SetWarnings True
'Dim stDocName As String
'Pinting Command
'stDocName = &quot;FirstTry&quot;
'DoCmd.OpenReport stDocName, acNormal
DoCmd.Close
Exit_Command66_Click:
Exit Sub

Err_Command66_Click:
MsgBox Err.Description
Resume Exit_Command66_Click

End Sub


Private Sub Extras_Click()
DoCmd.SetWarnings False
Select Case Extras
Case 1
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!GTst.Value))
DoCmd.RunSQL &quot;Update Orders SET Garlic_Toast=('Garlic Toast')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Garlic Toast&quot; & &quot;, &quot
Case 2
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!GTstChz.Value))
DoCmd.RunSQL &quot;Update Orders SET Garlic_Toast=('Garlic Toast with Cheese')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Garlic Toast With Cheese&quot; & &quot;, &quot
Case 3
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!GBrStk.Value))
DoCmd.RunSQL &quot;Update Orders SET Garlic_Toast=('Garlic breadstick')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Garlic Breadstick&quot; & &quot;, &quot
Case 4
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!GBrStkChz.Value))
DoCmd.RunSQL &quot;Update Orders SET Garlic_Toast=('Garlic breadstick with cheese')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Garlic Breadstick With Cheese&quot; & &quot;, &quot
Case 5
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!CSal.Value))
DoCmd.RunSQL &quot;Update Orders SET Salad=('Ceaser salad')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Ceaser Salad&quot; & &quot;, &quot
Case 6
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!GrSal.Value))
DoCmd.RunSQL &quot;Update Orders SET Salad=('Greek salad')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Greek Salad&quot; & &quot;, &quot
Case 7
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!Coke.Value))
DoCmd.RunSQL &quot;Update Orders SET Pop=('Coke')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Coke&quot; & &quot;, &quot
Case 8
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!SevenUp.Value))
DoCmd.RunSQL &quot;Update Orders SET Pop=('7 Up')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;7 Up&quot; & &quot;, &quot
Case 9
Me!Pprice.Value = (Me!Pprice.Value + CDbl(Me!Orng.Value))
DoCmd.RunSQL &quot;Update Orders SET Pop=('Orange')where Order_ID = (SELECT MAX ([Order_ID]) FROM orders)&quot;
Me!Extra.Value = (Me!Extra.Value + &quot;Orange&quot; & &quot;, &quot
End Select
DoCmd.SetWarnings True


End Sub

Private Sub PuDel_Click()
DoCmd.SetWarnings False
O_ID = CDbl(DMax(&quot;Order_ID&quot;, &quot;Orders&quot)
If Me!PuDel.Value = 2 Then
StrSQl = &quot;Insert Into Shipping Values (&quot; & O_ID & &quot;,'Delivery','test')&quot;
ElseIf Me!PuDel.Value = 1 Then
StrSQl = &quot;Insert Into Shipping Values (&quot; & O_ID & &quot;,'Pick Up','test')&quot;
End If

DoCmd.RunSQL StrSQl
DoCmd.SetWarnings True
End Sub

Private Sub Quantity_Click()
Me!Pprice.Value = CDbl(Me!Pprice.Value) * CDbl(Me!Quantity.Value)
End Sub
Private Sub Form_load()
DoCmd.MoveSize 1500, 0, 10000, 10000
End Sub

 

[jrbarnett]

You get the message when in a multi user database, two or more users try to access the same record (or page of records) simultaneously.
Take a look at the form's RecordLocks property to see what the best option is for your situation, as it depends on how your application is used.

John

 

[judgehopkins]

I have gotten the same message and compacting and repairing the database works wonders on getting rid of that problem.

Also, while in design view, check under tools/security to see if you have inadvertently locked anything.

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[autocutter]

Just offhand it looks like there are several types of update methods going to the same record at more or less the same time which is a no-no in SQL.