Friday, June 3, 2011

Validating a Year Field in MS Access

For my folder list database, which is where students enter basic folder level data for our finding aids, I only want a four-digit year or two four-digit years separated by a dash in the data field. The reason for this is that programming that runs against the folder list later relies on that format for the date. I have been having a problem with students remembering this and decided to force them to comply via programming in the database.

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.
          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.
          Exit Sub
     End If

No comments:

Post a Comment