How to change a field in BeforeUpdate (runtime error 2115)

[rudivs]

I would like to do data validation in the BeforeUpdate procedure, but Access gives me a runtime error when I try to do this:

Runtime error 2115: The Macro of function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the date in the field.

An example of what I am trying to do is as follows:

Private Sub Option_BeforeUpdate(Cancel As Integer)
  If Option = A Then
    Answer=MsgBox("Don't you mean B?",vbYesNo)
    If Answer = vbYes Then
      Cancel = True
      Option = B
    End If
  End If
End Sub

The code breaks on Option=B. I have tried using Option.Value = B instead of Option = B, but it still gives the same error.

In plain english, what I'm trying to do is to check the value that a user supplies to a combo box, and under certain conditions, provide a message box to verify that the user has indeed made the correct selection. Based on the answer supplied by the user, the combo box should be changed to the correct value, or left as it is.

Does anyone know if this can be done?

[ADezii]

Setting Cancel = True will not allow the Form to be updated, and I don't think that this is what you're after. Depending on the User's Choice, either change the Entry or leave it along, then Save the Record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me![cboOption] = "A" Then
    Answer = MsgBox("Don't you mean B?", vbYesNo)
      If Answer = vbYes Then
        Me![cboOption] = "B"
      End If
  End If
End Sub

P.S. - Setting the Name of a Control to Option is not a good idea.

[rudivs]

Thanks for your reply ADezii,

I misunderstood how Cancel = True setting works, thank you for clarifying that. Your code works if I attach it to a form (as you have it), but then the validation is only done if I move to the next record. Is there some way to make it work with the combo box BeforeUpdate event so that it validates when I click in another control? (something like the following, adapted from your code, which still gives runtime error 2115):

Private Sub cboOption_BeforeUpdate(Cancel As Integer)
  If Me![cboOption] = "A" Then
    Answer = MsgBox("Don't you mean B?", vbYesNo)
      If Answer = vbYes Then
        Me![cboOption] = "B"
      End If
  End If
End Sub

[ADezii]

You may need to use the LostFocus() Event of the Combo Box in order to have the code work effectively:

Private Sub cboOption_LostFocus()
If Me![cboOption].Text = "A" Then
  Answer = MsgBox("Don't you mean B?", vbYesNo)
    If Answer = vbYes Then
      Me![cboOption].Text = "B"
    End If
  End If
End Sub

[rudivs]

I got it working by putting the validation the AfterUpdate rather than the BeforeUpdate event. But that wouldn't let me undo the changes if I added a cancel option to undo changes (Undo only seems to work in BeforeUpdate):

Private Sub cboOption_[B]Before[/B]Update(Cancel As Integer)
  If Me![cboOption] = "A" or Me![cboOption] = "B" Then
    Answer = MsgBox("Don't you mean C?", vbYesNoCancel)
    If Answer = vbYes Then
      Me![cboOption] = "C"
    ElseIf Answer = vbCancel Then
      Cancel = True
      Me![cboOption].Undo
    End If
  End If
End Sub

In other words, if the current option is A, and the user selects B, a YesNoCancel message box should come up. If the user then clicks on Yes, the option should be set to C, if the user clicks No, the option should be set to B, and if the user clicks Cancel, then the option should remain A.

The code above still gives error 2115. I did get it working using AfterUpdate and the OldValue property as follows:

Private Sub cboOption_[B]After[/B]Update()
  If Me![cboOption] = "A" or Me![cboOption] = "B" Then
    Answer = MsgBox("Don't you mean C?", vbYesNoCancel)
    If Answer = vbYes Then
      Me![cboOption] = "C"
    ElseIf Answer = vbCancel Then
      Me![cboOption] = Me![cboOption].OldValue
    End If
  End If
End Sub

I'm a bit hesitant to use OldValue rather than Undo, but I can't think how else to do it?

[Rolrox]

While this is an "old" nugget, some observations here as I've had to solve this recently.


For the event, rather than an "beforeupda te" select "change". Then I could reset the code.

[NeoPa]

Using the BeforeUpdate() event procedure is certainly the correct approach. When I've tried to use it, it works perfectly. I suspect where you're hitting problems is that after you cancel the update (or technichally slightly before even) you are attempting to change the value again. This doesn't make much sense logically. Do you want to cancel the update, or do you rather want to overwrite the selection of the operator to set it to a value of your choosing? Only one of these can be true. When you decide which it is then you are more than half way to resolving your problem. Of course we can still help if you need, but this must be the first step.

[Rolrox]

My experience was that setting "Cancel = True" doesn't reset the field to its earlier state; so instead, with the OnCurrent event I'm capturing the original value and in the OnChange, of the user wants to cancel, I'm resetting it.

[NeoPa]

That's interesting, as that's exactly what it's supposed to do (as well as to clear the .Dirty flag if it's the only item otherwise changed). Do you have a repeatable example of this strange behaviour?

[Rolrox]

This was just one of several incarnations that didn't work...

Private Sub idRealID_BeforeUpdate(Cancel As Integer)

Dim txMsg
Dim ct As Long

    ct = DLookup("count(*)", "customer", _
        "idCustomer<> " & Nz(Me.idCustomer, 0) & " AND idRealID='" & Me.idRealID & "'")
        
    txMsg = IIf(ct = 0, _
      "MUSTERININ KODUNU ""{1}"" DEYISMEK ISTEDIYINIZDEN EMINSINIZ?", _
      "MUSTERININ KODUNU ""{1}"" DEYISMEK ISTEDIYINIZDEN EMINSINIZ? YENI KOD {2} DIGER MUSTERI TEREFINDEN ISTIFADE OLUNUR!")
      
'TRUE re you sure you want to change the customer code from {1}.  The new code is used by {2} others customers."

    Cancel = MsgBox(Replace(Replace(txMsg, "{1}", Nz(m_idRealID, "")), "{2}", ct), vbOKCancel + vbCritical + vbDefaultButton2, "Change of Customer Code") = vbCancel
    
       
End Sub

[NeoPa]

For it to be considered repeatable, I'd need a database to try out.

Here are some basic points to consider when attaching databases :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[Rolrox]

This isn't a problem for me. As I wrote before, I found a solution (which I suggested here as it might help others).

[NeoPa]

No problem with that, but when the BeforeUpdate() event procedure is used properly there is no issue to worry about anyway.

See below an excerpt from the Help system about updating the control's value before completion of the BeforeUpdate()event procedure (including within the precedure itself of course) :

A run-time error will occur if you attempt to modify the data contained in the control that fired the BeforeUpdate event in the event's procedure.

[roddyBenson]

Believe it or not you it will all work nicely if you just add on error resume next at the top of the subroutine. If you change a bound form control (ie control.text for example) the form control will change but then because it is bound to the underlying database field the field cannot be changed in the before_update event no matter what value cancel is set to.

However, the on error resume next clears the 2115 error but the value of the form control is now set to the value you are trying to change it to in your code. Setting the cancel to true will then prevent the cursor exiting the control. Took me hours to work this one out so thought I would share. Not sure why Microsoft have to make it so difficult!

[NeoPa]

Hi Roddy. Thanks for posting.

I wonder though, have you considered the ramifications of using such an approach with regards to the maintainability of the code?

It's often possible to shoe-horn things into code that work, but are not very intuitive. One also needs to bear in mind that many problems people have working with restrictions are more down to their less than full appreciation of what's happening than to badly designed implementation from Microsoft.

Don't get me wrong. They exist too, but not every case where people scratch their heads wondering why it works a particular way is an example of that. I would contend this is one such instance. I believe it makes good sense for such code to be discouraged as it's normally a sign of limited understanding by the developer.

Of course, it's possible to get a messy loop going even when you place the update in the more logically appropriate place in the AfterUpdate event but MS can only do so much on that score.