I've been spending the whole day on this issue with no luck so hopefully someone can help me out!
I thought it'd be a relatively simple thing: basically I have a large form, with many date fields to be entered in a subform. To make data entry a lot faster, I'm trying to make it so users only have to enter the day. By default, the month/year should be set to a "main" date they enter in a textbox at the top of the form.
e.g. if they enter "Jun-12-2010" in the main textbox, I'd like for them to only have to enter "15" in the subform textbox, and then when they jump to another field the form automatically changes it to "Jun-15-2010". (It's actually a bit more complicated than that, as it has times as well and there are some more calculations involved, but for the sake of simplicity this is the basics of it.)
I've already written a function "replaceDate" which takes in the "15" and outputs "Jun-15-2010". Unfortunately, "15" is not a valid date. Therefore, if I enter it in the textbox and then move away, access throws a "The value you entered isn't valid for this field" error. It looks like this occurs immediately, before any of the "BeforeUpdate", "LostFocus", "Exit" etc. events occur.
As a result I attempted to put my code in the "form error" event. Here's what it looks like:
This would work great if not for one problem: apparently when the error is thrown, the form is already in the process of "updating" this field. Thus, it won't allow me to change the value of the field. Essentially it looks like the same problem which makes it impossible to change the field value in the BeforeUpdate event:Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2113 Then
Me.ActiveControl.Value = replaceDate(Me.ActiveControl.Text)
End If
End Sub
Any ideas of what might be a workaround for this issue? (e.g. is there maybe an event I don't know about that fires after the form error event is complete?)Run-time error "2115". The macro or function set to the Before Update or Validation property for this field is preventing the application from saving the data in the field.