none
Error 2107 (breaks validation rules) when no rules defined?

    Question

  • Hi,

    I press a button on my subform that runs this piece of code:

        Me.Parent.RecordSource = "SELECT * FROM ChgCodeDetails WHERE Tid = " & Me.Tid

        Me.Parent.Requery

    As expected this means Access hands control to the SubForms Before Update procedure that does some cross field validation:

        'If Apply Max Charge is chosen a max chg must be entered

        If Me.ApplyMaxChg Then

            If IsNull(Me.MaxChg) Then

                UsrMsg (1002)

                Me.MaxChg.SetFocus

                Cancel = True

                Exit Sub

            End If

        End If

    In the case that the validation fails the user sees message 1002 and the update is cancelled.

    Here’s where my understanding fails (if it hasn’t already). I had thought that this would raise error 2501 (operation cancelled) that would be trapped by the procedure for the button on my subform so in this case I’d just throw it away. The error handler for the button on my subform is like this:

    Err:

        MsgBox "Expandbtn Error handler"

        Select Case Err.Number

            Case 2501      'Cmd Cancelled

                Resume Next

            Case 2046

                UsrMsg (4) 'Delete operation is not available

            Case Else

                DisplayUnexpectedErr Err.Number, Err.Description

                Resume CleanUp

                Resume

        End Select

    BUT what actually happens is error 2107 is raised (failed validation rule) even though neither my subform nor the underlying table has any validation rules defined for the field in question. OK so I can just throw away 2107 as well but why is it raised in the first place? Surely I’ve cancelled the command to update a record so the error raised should be 2501? Also if I did have a field validation rule how does Access decide which error to raise? Yes I’m confused (and worried that I still don't understand Access) so any help or advise would be much appreciated.

    Friday, October 11, 2013 9:07 AM

Answers

  • Sorry for being so late getting back, been out of touch.

    If a field's Required property is No, then you should not get an error when you set it to Null (unless there is a Validation Rule that won't permit it).

    Getting the call stack is not easily done, but stepping through the code using breakpoints and run or using single stepping can be very useful.  You can also set Watch points on a variable to see when/where it is changed.

    There isn't an error stack, each error is handled one way or another before another line of code is executed.  This is one reason why just about every VBA procedure should have error handling code.

    Using a control's or form's BeforeUpdate event's Cancel argument just prevents the update from happening.  OTOH, if you use the Cancel argument in a form's Open event, you will get a 2501.

    I'm not sure I followed "update event was caused by a piece of code", but it is important to know that when you use code to modify a control's value, no update events are triggered.   Control update events only happen when a user changes a control's value.  OTOH, a form's Update events are triggered whenever anything that causes a different record to become current (or when a form with a dirty record is closed).

    • Marked as answer by John MartinUK Tuesday, October 22, 2013 11:00 AM
    Monday, October 21, 2013 11:34 PM

All replies

  • Hi John,

    I din't find the function of "UsrMsg" in Functions. Is it a custom funtion?

    I think a common way to understood the code is debug it by step by setp, then you will see which line of code cause the error 2107.

    And what's the version of Access? A validation rules could be created for a field or a record or a field or a control on a form in Access 2010. Please check there is no validation rules.(Restrict data input by using a validation rule)

    Monday, October 14, 2013 2:33 PM
  • Hi,

    Thanks for the reply. Yes UsrMsg is a custom function I use so that messages can be held in a table and the text adjusted (but not the Error number) by an administractor at the customer site.

    Regarding which line causes the error. Really it's the SELECT that kicks everything off because this means Access tries to update the current record before moving to the new one. The Error is finally trapped by the Error handling part of the sub that has the SELECT and in fact the code doesn't proceed further than the SELECT. However, the Before Update event is fired of course and when the validation fails in there I inform the user and CANCEL the update. So I thought I was cancelling the SELECT (in a way) and I'd get error 2501 not 2107. I'll recheck for any rules as I did play around with MACROS when I first started A2010 though I sincerely hope I didn't do so in my development database.

    In practical terms I just throw away 2107 and 2501. BUT I'm worried I can be so wrong in my understanding somewhere.

    Also I did come across an instance where calling Docmd(acSaveRecord) raised a different error to the one raised when pressing the save key on the Ribbon.

    Thanks again - not an urgent problem but I hate not understanding what's going on!

    Wednesday, October 16, 2013 2:00 PM
  • Hi again,

    I tried compact and repair - no change

    I looked for any Macros - there are none

    Then I tried creating the Error state (i.e. one that will fail the validation in the Form Before UPdate code).

    1. If I save using the save on the ribbon I just receive my own message

    2. If I let the save take place automatically (Due the the SELECT trying to move to another record) the err.number is 2107 - failed validation rule.

    So when I set cancel to True in Form_Before Update it really does look as if Access is raising Error 2107?

    Any thoughts would be useful. Thanks.

     

    Wednesday, October 16, 2013 2:26 PM
  • I don't know the specifics of what you have going on, but setting Cancel to True will not cause a 2501 message.

    OTOH, there are other properties besides the ValidationRule property that can cause a validation rule error.  A null value in a field (non Text with AllowZeroLength set to No) that has its Required property set to Yes is one example that causes an error (not sure of the err number).

    Maybe setting the parent form's record source is changing the situation to something that causes the error?

    Have you placed a breakpoint in the BeforeUpdate event and single stepped from there to pin down where the error actually occurs?

    Side note:  Setting a form's RecordSource property automatically requeries the form, so adding a Requery line is slow and redundant.

    Wednesday, October 16, 2013 8:03 PM
  • Hi Marshall,

    I am actually blanking (setting to null) a currency field though it's required property (in the underlying table) is No. I'm 'solving' this particular problem by throwing away the error if it's 2107. 

    However, it seems that my understanding of Access is wrong. I'd be grateful if you could point me to the rules for what Access does when cancel is set to true in the before update event. More particularly, as in this case, when the update event was caused by a piece of code. How does my code know that the update has been cancelled?

    Even more generally, and I hope you don't mind my asking, Is there a way of seeing what's going on inside Access; I'm imagining being able to view the event stack and error stack given Access uses such things? Knowing how Access was 'thinking' would reallly help.

    Thanks ever so much. J.

         


    • Edited by John MartinUK Thursday, October 17, 2013 8:43 AM typo
    Thursday, October 17, 2013 8:40 AM
  • Sorry for being so late getting back, been out of touch.

    If a field's Required property is No, then you should not get an error when you set it to Null (unless there is a Validation Rule that won't permit it).

    Getting the call stack is not easily done, but stepping through the code using breakpoints and run or using single stepping can be very useful.  You can also set Watch points on a variable to see when/where it is changed.

    There isn't an error stack, each error is handled one way or another before another line of code is executed.  This is one reason why just about every VBA procedure should have error handling code.

    Using a control's or form's BeforeUpdate event's Cancel argument just prevents the update from happening.  OTOH, if you use the Cancel argument in a form's Open event, you will get a 2501.

    I'm not sure I followed "update event was caused by a piece of code", but it is important to know that when you use code to modify a control's value, no update events are triggered.   Control update events only happen when a user changes a control's value.  OTOH, a form's Update events are triggered whenever anything that causes a different record to become current (or when a form with a dirty record is closed).

    • Marked as answer by John MartinUK Tuesday, October 22, 2013 11:00 AM
    Monday, October 21, 2013 11:34 PM
  • Thanks ever so much you've been really helpful. FYI the piece of code that 'caused' the update event did change the current record so it was this that really caused the update event - thanks for making that clear to me. Also the distinction between cancel in the form's open event (raising error 2501) and Before Update (just stopping the update) is good information to have. Thanks I think my question has been well and truely answered!

    Tuesday, October 22, 2013 11:00 AM