My first thought was to create a validation rule for the field in the table, which would be:
Like “####” Or Like “####-####”
But I wanted to stop the OnClick event and return the user to the date field on the form while being certain that nothing was saved to the table (I use unbound controls on my forms).
Adding the validation rule to the date text box would accomplish this, but I need to add “Is Null” to the rule or Access would not allow a folder with no date, of which we have many:
Like "####" Or Like "####-####" Or Is Null
The one thing I did not like about this was that if the date in the text box was invalid you could not use any of the other controls on the form until you fixed the date. I can see wanting to use the “Close” or “Clear” buttons. So what I opted to do was put the validation in the VBA for the OnClick event behind the Save button.
Here is the code I inserted right after the value from the Date textbox is picked up:
If strDate Like "####" Then
'The date is a four-digit year,
'continue with the OnClick event.
ElseIf strDate Like "####-####" Then
'The date is two four-digit years separated by a dash,
'continue with the OnClick event.
ElseIf strDate Like "" Then
'No date, which I consider okay,
'continue with the Onclick event.
Else:
MsgBox ("Please enter a 4-digit year or two 4-digit years separated by a dash")
'The date is invalid, set focus to the date field and stop the OnClick event.
Me.txtDate.SetFocus
Exit Sub
End If
