none
Runtime error 2115 RRS feed

  • Question

  • This is Mike - Thank you so much for any help you can offer!

    We have users in Manila so the latency is a real issue.  It may be causing the issue as I cannot replicate on my laptop.  The user selects from a drop down in a subform and the runtime error 2115 comes up.  "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the data in the field."

    I have error trapping that tells me where the code is breaking.  The strange thing is the code is in the AfterUpdate.  Nothing is in the BeforeUpdate and it does not have any ValidationRule set either.

    The parent form loads the subform's Record Source using an SQL SELECT.  The subform is unbound but each control has a field name from the table as its Control Source.  The control that errors out is named "RESULT'.  The drop down has two options "PASS" and "FAIL".  When the user selects from the drop down two things occur:  1) The "RESULT" Control Source is updated by using If Me.Dirty = True then Me.Dirty = False.  2) Then updates the "RESULT DATE" Control Source using a SQL UPDATE.

    CODE BELOW:

        On Error GoTo ErrorHandler

         'Apply change to table
        If Me.Dirty = True Then Me.Dirty = False
       
       'Update record
        strSQL = "UPDATE T_QC_RESULTS SET " & _
                 "RESULT_DATE = Now() " & _
                 "WHERE RID = " & Me.DTL_RID & ";"
        cConn.Execute strSQL
        
        'Show change
        DoEvents
        Me.Refresh

    ExitHere:
        Me.Parent.LBL_MSG1.Visible = False
        Me.Parent.Offset.SetFocus
        Exit Sub

    ErrorHandler:
        'Log Error
        Call gfnLogError(Err.Number, Err.Description, ErrObj, ErrProc, ErrDtl)
        Resume ExitHere

    Friday, April 27, 2018 4:12 PM

Answers

  • Hello mdthomas,

    I'm glad to hear that your issue is resolved and I would suggest you mark helpful reply to close the thread.

    For the reason, to be honestly, I do not have much idea too. According to my experience, we usually use DoEvents in a loop and I also got a similar thread which provide the idea. So I recommend you this solution. I would suggest you check the thread for more information.

    Error 2115: Macro/function set to BeforeUpdate/ValidationRule is preventing {pgm} from saving data in the field

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mdthomas Friday, May 4, 2018 4:32 PM
    Friday, May 4, 2018 2:44 AM

All replies

  • Hello mdthomas,

    >>The control that errors out is named "RESULT'.  

    What's the type of the control? Where is the code? Is it in the control's click event? After_update event? Or something else?

    >>'Show change
    >>DoEvents
    >>Me.Refresh

    Have you tried to remove the DoEvents from your code for testing? What's the result?

    Best Regards,

    Terry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, April 28, 2018 3:56 AM
  • Hi Mike,

    Your statement is incorrect:

    If Me.Dirty = True Then Me.Dirty = False

    The property Dirty is a Form property. When the Form property Dirty = Dirty, then it is Dirty. What you are doing here is actualy say to the form: 'no, that is not true, you are False...

    Another fact is, that as long a new record is not saved, the data is not saved in the table. When it is not a new record but an existing record, the changed data is not saved in the table as long as the Form property Dirty is False. 


    Saturday, April 28, 2018 11:27 AM
  • Hi Terry!  Thanks for the assistance!

    The control is a Combo Box and the code is in the AfterUpdate.  I'm trying your suggestion to drop the DoEvents.  I'll have to wait for Manila to test so that will be tonight.  I'll get back and let you know.

    Thanks again!

    Mike

    Monday, April 30, 2018 1:25 PM
  • Peter - I think you misunderstand the line:

    If Me.Dirty = True Then Me.Dirty = False

    That exact line is used to force a record to be saved. It is extensively used by developers for that very purpose. It can even be shortened to this:

    If Me.Dirty Then Me.Dirty = False


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Monday, April 30, 2018 4:58 PM
  • OK Bill, I know, I know, you are right. But as a developer you should always be sure that the input is correct which should be done in the event BeforeUpdate of the Form:

    Public Sub Form_BeforeUpdate(Cancel As Integer)
        If IsNull(Me.YourField) Then
            Msgbox "Pleas enter the field YourField"
            Cancel = True
            Exit Sub
        End If

    End Sub

    Than, before to start another procedure (if needed like in Mikes case) you can can invoke then event Form_BeforeUpdate by this:

    Sub X

        Dim Cancel As Integer
        Form_BeforeUpdate Cancel
        If Cancel = False Then
            Now you can execute the procedure.
        End If

    End Sub

    You only have to invoke Form_BeforeUpdate if procedure X has to executed when a user is editing the record.

    Monday, April 30, 2018 6:43 PM
  • I see where you're coming from, Peter.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, April 30, 2018 7:25 PM
  • Hi Bill,

    Do you mean you've seen my website? Let me show some examples how I deal with this:

    http://www.officeprograms.nl/support/basiscomponenten.htm > header Controle bij invoer (Check at input).

    I also report errors, see examples at:

    http://www.officeprograms.nl/support/voorbeelden.htm > header Controle rapportages (Control reports)

    My 'teacher' is always Microsoft at the root. We developers need to understand events, methods, properties etc. If we take the 'teacher' serious, we can do many beautiful things and we will enjoy our work (and hobby). Microsoft provides the tools, but we are responsable to make professional applications.

    Monday, April 30, 2018 8:46 PM
  • Hi Bill, it's great to see you're on this forum.  Are you still on MS Access Professionals on Google?

    Anyway, I appreciate your reply to Peter.  I certainly understand and use data validations all the time.  But in this case, I don't really need it because the user is just selecting either PASS or FAIL.  And if they want to change it they just use the drop down again to do so.

    Thanks again Bill and it's great knowing you're still out there in the DEV world.

    QUESTION:

    Do you have any other ideas as to why the user is getting the 2115 error when they select from the RESULT drop down?  There isn't any code in the BeforeUpdate or Validation Rules for this control.  The code is in the AfterUpdate.

    Mike


    • Edited by mdthomas Tuesday, May 1, 2018 7:33 PM
    Tuesday, May 1, 2018 7:29 PM
  • Hello mdthomas,

    What's the result of removing "DoEvents"? Which line you got the error? Could you see the data changed in the source table?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 2, 2018 6:41 AM
  • Hi Terry - thanks for checking in.  I'm still waiting for Manila to test.  I can't replicate the conditions so they have to test for me and let me know.  I'll let you know as soon as I do.  Thanks again!
    Wednesday, May 2, 2018 2:05 PM
  • Terry -

    Manila tested last night and said they didn't get the error.  So I just removed the DoEvents as you suggested.  Can you explain (for my education) why that would have thrown the 2115 error in the AfterUpdate?

    Thanks again so much!

    Mike

    Thursday, May 3, 2018 2:36 PM
  • Hello mdthomas,

    I'm glad to hear that your issue is resolved and I would suggest you mark helpful reply to close the thread.

    For the reason, to be honestly, I do not have much idea too. According to my experience, we usually use DoEvents in a loop and I also got a similar thread which provide the idea. So I recommend you this solution. I would suggest you check the thread for more information.

    Error 2115: Macro/function set to BeforeUpdate/ValidationRule is preventing {pgm} from saving data in the field

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mdthomas Friday, May 4, 2018 4:32 PM
    Friday, May 4, 2018 2:44 AM