Update or CancelUpdate without AddNew or Edit

Eileen Murphy
Eileen Murphy used Ask the Experts™
Hi Experts. I haven't had this happen before -- not sure if I was just lucky or what... A client kept getting the above error (not consistently) when changing fields in a bound form.

After much hair pulling I got rid of it by eliminating default values in a sub form -- some fields had them hard coded in the form itself, and others were dynamic and had to be changed in code.

I know I've used them before and can't for the life of me figure out why this is happening...

Any idea? What was I doing wrong?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It sort of looks like the form's sort order may be out of whack so that a certain field they're filling and subsequently probably hitting ENTER or TAB afterwards is trying to move to the next record. However, required fields may not be entirely filled in, or fields that cause need for others to be filled in are while the related fields aren't...thus, it tries to move to a new record without having a completed old record. I suspect the code layer had more impact on this than the table-side layer.

You can test this by restoring all your old defaults at both levels and then switching the form to Cycle Current Record only. Then put a SAVE button on the page and see if it grumbles at you - if it does, it'll probably tell you which field it thinks it still needs.

Eileen MurphyIndependent Application Developer


Here's the forms OnCurrent event:

Private Sub Form_Current()

    If IsNull(Me.ORDER_DATE) Then
        Me.SALESYR = DatePart("yyyy", [ORDRDATE])
        DoCmd.RunCommand acCmdSaveRecord         <<<<< Added this -- didn't help
    End If
    Me.ContactKey.Requery <<< When Customer Selected, Contact
    If Me.Terms = 5 Then
        Me.SpecialTerms.Visible = True
        Me.SpecialTerms.Visible = False
    End If
    Forms!switchboard!OrderRecID = Me.OrderRecID
>>>>>Commented the below out to get rid of the error <<<<<

   ' If IsNull(Me.PONUM) = False Then
   '     Me.sfrmOrderDetails!PONUM.DefaultValue = "'" & Me.PONUM & "'"
   ' End If
   ' If IsNull(Me.ENGINEER) = False Then
   '     Me.sfrmOrderDetails!ENGINEER.DefaultValue = "'" & Me.ENGINEER & "'"
    End If
End Sub
Hmm - a few issues I can see. You don't want a save action on an OnCurrent event - it'll keep firing and re-firing. Ditto with a requery - move that to the After Update of whatever field has the Customer selection.

Next, don't set your DefaultValue in the code you commented out - set the ACTUAL value if/when you need to - however, how I'd actually go about this is on the form level, not with code...on the subform, which I assume "sfrmOrderDetails" is, on the field you want to autofill, set the Default Value field to the parent object. "=Forms!<frm parent name>!<parent's fieldname>"

Doing it this way says "Hey, we're cool - IF we're makin' a record and nobody fills me in, THIS is what I'm going to go use...if no record happens, hey, that's cool too, I won't save anything." In forcing a subform value before you've actually created the parent record and instigated the child record, you can cause a disconnect where data isn't there to be used in time for the child record.

Eileen MurphyIndependent Application Developer


Thanks Rachel - I need to requery the contact field OnCurrent because as they move through the records I need the records filtered by the Customer Field in each. I also requery it when they change the Customer.

I will put the defaults on the form as suggested -- I think I moved it here because they had a problem where the default value retained the value from the previous record on occasion, so I was trying to force it here...

I also didn't say that the error was occurring not just on new (yet unsaved) records, but on existing ones as well -- those that had sub-records already in the sub-form. Isn't that weird?

Thanks a lot for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial