Manual completion of date field

[stebrun]

Hello everybody
I have a problem with a textbox bound to a date field and wonder, if somebody can help:


In my form, I have a textbox (txtDate) bound to the field ActionDate in tblActions. I would like to do the following:

1. If the user enters a valid date, nothing happens (= the value is saved to the field)
2. if the user enters an incomplete date, then complete it according to a rule, which I have specified in a function. If the rule can complete the date, it will do so and save the value, if not a user defined error messages appears and sends the user back to the textbox.

My idea was to check the textbox with the "BeforeUpdate" event and then to modify the content if needed. However, there are two problems:

1. if the user enters an incomplete date (for example "01/") and then leaves the field (for example by pressing the tab-key) the MsAccess validation is executed first and creates a message ("The value you entered isn't valid for this field"). This prevents "BeforeUpdate" even to happen, so that I cannot complete the date.
2. If the user enters a date, that is almost complete (for example 01/01/) then access automatically completes the date with the current year (--> 01/01/2017). This is again done before the "BeforeUpdate" so that I cannot avoid it. I have of course turned off the "autocomplete" property on the textbox but that seems not to help.

Is there any possibility to temporarily turn off the validation of Access related to the datatype of the field? Or do you have suggestions, how I could achieve what I want in a different way. I was also experimenting with "onDirty" or "onChange", the problem there is that the event is triggered starting from the first number entered in the field, which is not what I want.


Thanks in advance for your suggestions!

Stephan

[ranman256]

is it too exhausting for the user to enter 8 characters?

[orange]

Check out this function to validate a Date value
When you say complete the user's input to make the required date--- I think you need to layout some conditions to confirm the requirement
and describe them to readers.

[stebrun]

Dear Orange

Thanks for your reply!
My problem is not with the validation rule but that the automatic MsAccess validation is triggered before I can even start my validation.

So, I would need to find a way to run my custom validation before access checks if the entered text is compatible with the datatype of the field, to which the textbox is bound...


Any idea, how to do that?


thanks in advance!

Stephan



P.S:
Just to reply to your request to describe the validation (but as I said: this is not my problem).

• a) if the entered day (D_user) is before the day of today (D_now), then create date: D_user/Month/Year
• b) if entered day (D_user) is after the day of today (D_now), then create date: D_user/Month-1/Year
• c) (sub-rule to b): if month = January and rule b = true then create date: D_user/12/Year-1

[stebrun]

@ Ranman256:

Well, not really

But users know this function from another software and have requested for it. Customer is always right...

[Missinglinq]

You can prevent the native error message by trapping it, like this:

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

If DataErr = 2113 Then 'Data entered does not match datatype of Control Source
  Response = MsgBox("This Field Must Be Entered as a Valid Date!", vbExclamation, "Not a Valid Date!")
  Response = acDataErrContinue
End If

End Sub

This simply prevents the native error message and pops up one of your own. Don't really get the validation you're trying to do, but you should be able to work from here and do what you need...using Me.ActiveControl to refer to the Control that caused the error.

But the simplest way to do this would be to only let the users enter dates using the native DatePicker...doesn't get much easier than that!

Linq ;0)>

[stebrun]

Hi Missinglinq

Thanks a lot, thats exactly what I needed!

[Missinglinq]

Glad we could help!

Linq ;0)>