Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9190

Re: Need to cancel the update to a bound form if edits fail (Access 2010)

Show all headers | View raw

On 25/03/2014 03:31:32, Ulrich Möller wrote:
> Am 25.03.2014 00:00, schrieb Phil:
>> On 24/03/2014 17:47:36, "Access Developer" wrote:
>>> "PW" <emailaddyinsig@ifIremember.com> wrote
>>>
>>>> I was doing that and the record was
>>>> getting saved w/o passing edits.
>>>> Put the Undo in the BeforeUpdate
>>>> event?
>>> There are several things that can cause the record to be updated, to wit,
>>> moving focus to a Subform control or an explicit save via keypress or code
>>> action, and moving off the current record to another record, or closing
>>> the form. You can't stop an Update in the After Update event because, of
>>> course, the update has already occurred. If you want to cancel, you must
>>> do so before the update occurs.
>>>
>>>> And isn't that just for new
>>>> records?
>>> No, Before Update event applies to all records being written. Before /
>>> After Insert applies only to new records.
>>>
>>>> What if the user blanks out a
>>>> required field in an existing record?
>>> As best I remember, if you defined the field as required in the table
>>> definition, the database engine will enforce that, and will not write a
>>> record without the field.
>>>
>>>> By the way, I saw many examples
>>>> from a search and they all do me.Undo,
>>>> and then Cancel=True.  Cancel=True
>>>> would not allow me to exit the form
>>>> using the X/Close button.  I don't even
>>>> know what it is supposed to do.
>>> Cancel = True does exactly what it says... it cancels writing / updating
>>> the record. I believe it does, by design, return you to the form. If
>>> there's still data in the form, and there's a problem that prevents the
>>> record being written, closing the form will not work.
>>>
>>> Larry Linson
>>> Microsoft Access MVP
>>> Co-Author, Microsoft Access Small Business Solutions, pub. by Wiley
>>>
>>>
>> This is a routine to check a post code has been entered
>>
>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>
>>      If CheckPostCode = False Then
>>          DoCmd.CancelEvent
>>      End If
>>
>> End Sub
>>
>> Function CheckPostCode() As Boolean
>>
>>      If Nz(PostCode) = "" Then
>> If If MsgBox("You have not entered the post code, Do you want to correct
>> this?", vbQuestion + vbYesNo) = vbNo Then             Me.Undo
>>              MsgBox "All changes have been discarded"
>>          Else
>>              PostCode.SetFocus
>>          End If
>>      Else
>>          CheckPostCode = True
>>      End If
>>
>> End Function
>>
>> It works OK, but for some reason if you close the form there is an error
>> message to say it can't save the record (which is unchanged anyway)
>>
>> Phil
>>
>> ---
>> This email is free from viruses and malware because avast! Antivirus protection is active.
>> http://www.avast.com
>>
> Hi Phil,
> 
> in the beforeupdate event you can suppress any updates of the database
> table with the cancel variable set to true or running the macro
> docmd.cancelevent but your current record of the form will still be
> dirty! Just make a undo on the edited form or control to discard all
> changes.  After that, you should be able to close your form, but ...
> If you are currently in a new record and have required fields in the
> table then it is still not possible to save the record, because of the
> validation rules. Try to check/debug the DataErr in the form error event
> to get an idea what is preventing you from closing the form. Typical
> error codes are 2169, 3022, 3314. You should always handle this codes in
> the form error event too.
> 
> Take a notice on the form properties "newrecord" and "dirty" in the
> online help.
> 
> If you use cancel in the beforeupdate event you also have to ensure that
> the form.dirty and form.newrecord properties are both false when you
> want to close the form otherwise you will trap in a classic loop.
> 
> Ulrich
> 
> 
> 

Spot on as ever, Ulrich

Added Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Select Case DataErr
    Case 2169
        Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
    End Select
    
End Sub

which cures the problem.
So that, with a bit of luck answers Paul's original question.

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Need to cancel the update to a bound form if edits fail (Access 2010) PW <emailaddyinsig@ifIremember.com> - 2014-03-22 15:30 -0600
  Re: Need to cancel the update to a bound form if edits fail (Access 2010) Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-03-24 07:53 -0700
    Re: Need to cancel the update to a bound form if edits fail (Access 2010) PW <emailaddyinsig@ifIremember.com> - 2014-03-24 10:22 -0600
      Re: Need to cancel the update to a bound form if edits fail (Access 2010) "Access Developer" <accdevel@gmail.com> - 2014-03-24 12:47 -0500
        Re: Need to cancel the update to a bound form if edits fail (Access 2010) "Phil" <phil@stantonfamily.co.uk> - 2014-03-24 23:00 +0000
          Re: Need to cancel the update to a bound form if edits fail (Access 2010) Ulrich Möller <knobbi38@usenet.arcornews.de> - 2014-03-25 04:31 +0100
            Re: Need to cancel the update to a bound form if edits fail (Access 2010) "Phil" <phil@stantonfamily.co.uk> - 2014-03-25 09:42 +0000
            Re: Need to cancel the update to a bound form if edits fail (Access 2010) PW <emailaddyinsig@ifIremember.com> - 2014-04-03 21:21 -0600
          Re: Need to cancel the update to a bound form if edits fail (Access 2010) "Access Developer" <accdevel@gmail.com> - 2014-03-25 11:50 -0500
            Re: Need to cancel the update to a bound form if edits fail (Access 2010) Ulrich Möller <knobbi38@usenet.arcornews.de> - 2014-03-25 20:23 +0100
              Re: Need to cancel the update to a bound form if edits fail (Access 2010) "Access Developer" <accdevel@gmail.com> - 2014-03-26 14:46 -0500
            Re: Need to cancel the update to a bound form if edits fail (Access 2010) "Phil" <phil@stantonfamily.co.uk> - 2014-03-26 20:12 +0000
    Re: Need to cancel the update to a bound form if edits fail (Access 2010) PW <emailaddyinsig@ifIremember.com> - 2014-03-24 10:57 -0600
      Re: Need to cancel the update to a bound form if edits fail (Access 2010) Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-03-24 10:21 -0700

csiph-web