validation rule creation not working as expected

[joe789]

Hi Folks,

Due to a glitch I am working around, I need to create validation rules for certain fields in VBA versus using the validation rule property in the field property. For some reason, this code I am using below isn't working properly ... any ideas would be appreciated!

Private Sub Needs_Assessed_by_Instrument___Employment__Date_AfterUpdate()

If Me.Needs_Assessed_by_Instrument___Employment__Date < #1/1/2011# Or Me.Needs_Assessed_by_Instrument___Employment__Date > Now() Then

MsgBox ("Date too far in the past or in the future, data not saved, please correct!")

Me.Needs_Assessed_by_Instrument___Employment__Date = ""

End If
End Sub


Basically, no matter what date I put in the field, it triggers the message of the date being too far in the past or in the future. I am trying to analyze the date after the field has been updated so that any date OUTSIDE of the range of 1/1/2011 and Today the user gets a notice that the date is an error and has the opportunity to correct it. Once I can get this to work, after the message is displayed to the user IF the date is an error, I will erase that field so that the user must go back to fix it.

 

[boblarson]

You need to use the BEFORE UPDATE event.

And then issue CANCEL = TRUE

if it fails

Private Sub Needs_Assessed_by_Instrument___Employment__Date_[B][COLOR=red]BeforeUpdate(Cancel As Integer)[/COLOR][/B]
 
   If Me.Needs_Assessed_by_Instrument___Employment__Date < #1/1/2011# Or Me.Needs_Assessed_by_Instrument___Employment__Date > Now() Then
 
      [B][COLOR=red]Cancel = True[/COLOR][/B]
      MsgBox ("Date too far in the past or in the future, data not saved, please correct!")
 
      Me.Needs_Assessed_by_Instrument___Employment__Date[B][COLOR=red].Undo[/COLOR][/B]
 
   End If
End Sub

 

[joe789]

Thank you for the reply boblarson. I tried the code and it didn't seem to work so I verified the code by using it on other dates in a different form and it did work perfectly - thank you. This to me indicates that some other code that was being used on the same date fields that I was trying to run this validation rule code on with interfering somehow. I am wondering if you can offer any assistance in trying to figure this out.


When the form is loaded, I have a bit of code that runs:

Dim FormatEmploymentDate As Date

If IsNull(Me.Needs_Assessed_by_Instrument___Employment__Date) Then
Else
FormatEmploymentDate = Format([Forms]![ABC].Needs_Assessed_by_Instrument___Employment__Date, "mm/dd/yyyy")
[Forms]![ABC].Needs_Assessed_by_Instrument___Employment__Date = FormatEmploymentDate
End If

This code works well because it forces whatever format is in the form to conform to MM/DD/YYYY format. The only reason I am doing this is because I stumbled on a glitch between SQL Server and Access and the only way I could get the format I wanted in the date was to use the above code. The problem is that using this code makes validation rules non workable, hence me trying to hard code the validation rules in ... your code works perfectly on dates in the form, just not any dates where the above code is used to force format them into MM/DD/YYYY ...

Any ideas or am I just out of luck?

Working around a glitch like this is a mess ... fun, but a mess.

Thanks

 

[boblarson]

Using FORMAT turns them into TEXT instead of a date. So, try this:

If CDate(Me.Needs_Assessed_by_Instrument___Employment__Date) < #1/1/2011# Or CDate(Me.Needs_Assessed_by_Instrument___Employment__Date) > Date Then

Also, when dealing with fields that don't have a time element, use DATE and not NOW.