Error 3020 - Update or Cancelupdate...

[AndreasAuer]

Hi

my problem is this:
I'm using a Form with a subform to add/change records.
I have a button which opens a form, where I can update some values for all records of the subform with one click. It takes the values of the 1st record and applies these values to the other records using docmd.runsql("update...&quot.
If I close the update-form and want to change a value or click on the main form, the error "Update or Cancelupdate without Addnew or Edit" appears.
What can I do?

Hope someone could help me

 

[danvlas]

Are you sure you don't use recordsets? This is usually a problem when you reach a point of code where you say:

rst.Update

via a route that does not have
rst.AddNew

or

rst.Edit

Update queries do not return such error AFAIK.



Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[AndreasAuer]

This is the code:

Private Sub cmd_SetStandards_Click()
'set Standardvalues
yesno = MsgBox("Are you sure, you want to set these standards for all positions?", vbYesNo + vbQuestion)
If yesno = vbYes Then
Forms!Auftrag.Fensterposition_Unterformular.edit
tables!fensterposition.edit
If chk_System.Value = True Then
If x_System = False Then
syst = 0
Else
syst = 1
End If
DoCmd.RunSQL ("update fensterposition set s730=" & syst & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Farbe.Value = True Then
DoCmd.RunSQL ("update fensterposition set farbe='" & x_Farbe & "' where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Montage.Value = True Then
DoCmd.RunSQL ("update fensterposition set montage=" & x_Montage & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Nachlass.Value = True Then
DoCmd.RunSQL ("update fensterposition set nachlass=" & x_Nachlass & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Glas.Value = True Then
DoCmd.RunSQL ("update fensterposition set glas1=" & x_Glas1 & ", glas2=" & x_Glas2 & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
'Update prices for all positions...
Set rs = Forms!Auftrag.Fensterposition_Unterformular.Form.RecordsetClone
rs.findfirst "[auftrag]= " & x_Aufnr & " and [Position] = " & 2
pos1 = rs.Fields(1)
posnr = 9999
For i = 3 To 999
If pos1 = posnr Then
Exit For
End If
Forms!Auftrag.Fensterposition_Unterformular.Form.Bookmark = rs.Bookmark
rs.findfirst "[auftrag]= " & x_Aufnr & " and [Position] = " & i
posnr = rs.Fields(1)
Next i
Set rs = Nothing
DoCmd.Close acForm, "Standardvalues", acSaveYes
Else
DoCmd.Close
End If
End Sub

 

[danvlas]

Private Sub cmd_SetStandards_Click()
'set Standardvalues

'Could get rid of the YesNo variable
yesno = MsgBox("Are you sure, you want to set these standards for all positions?", vbYesNo + vbQuestion)
If MsgBox("Are you sure, you want to set these standards for all positions?", vbYesNo + vbQuestion)= vbYes Then
Edit on the form????
Forms!Auftrag.Fensterposition_Unterformular.edit
AFAIK there is no Tables collection
tables!fensterposition.edit
If chk_System Then
If x_System = False Then
syst = 0
Else
syst = 1
End If
DoCmd.RunSQL ("update fensterposition set s730=" & syst & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Farbe Then
DoCmd.RunSQL ("update fensterposition set farbe='" & x_Farbe & "' where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Montage Then
DoCmd.RunSQL ("update fensterposition set montage=" & x_Montage & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Nachlass Then
DoCmd.RunSQL ("update fensterposition set nachlass=" & x_Nachlass & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
If chk_Glas Then
DoCmd.RunSQL ("update fensterposition set glas1=" & x_Glas1 & ", glas2=" & x_Glas2 & " where auftrag=" & x_Aufnr & " and position > 1&quot
End If
'Update prices for all positions...
Set rs = Forms!Auftrag.Fensterposition_Unterformular.Form.RecordsetClone

The following block is totally ambiguous to me:

'===============From here===========================
rs.findfirst "[auftrag]= " & x_Aufnr & " and [Position] = " & 2 'What is 2???

pos1 = rs.Fields(1) you get a value from the second field in the recordset

posnr = 9999 [/b]you assign 9999 to posnr variable[/b]

For i = 3 To 999 you start a loop
If pos1 = posnr Then if the value of the second field is 9999 then you abort the loop
Exit For
End If
Forms!Auftrag.Fensterposition_Unterformular.Form.Bookmark = rs.Bookmark You try to set the focus to a record.The problem is that the record is the same...My opinion is that you should set the bookmark after the next statement (findfirst...)
rs.findfirst "[auftrag]= " & x_Aufnr & " and [Position] = " & i
posnr = rs.Fields(1)
Next i
until here, you have not done anything with rs except finding records and moving around
Set rs = Nothing
DoCmd.Close acForm, "Standardvalues"
[/b]you don't need: [/b], acSaveYes
Else
DoCmd.Close
End If
End Sub

Could you clarify what you need to accomplish? (My German is so short as to say Gut! Einz! Halt! Ja! Nein! , so I don't quite imagine what those variables/field names represent)

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[AndreasAuer]

This code should do this:
1st: Update selected Standard-values of all records of one order except the first record, where these standards are saved. The button for setting the standards is only visible if the first record is shown.

2nd: loop through all records. if one record is loaded in the form, some prices are automatically calculated and saved. I need this to calculate a complete price for the order.

I inserted the two ".edit" you marked just for testing because of the error message. I forgot to erase them.