Validation rule on Form

[zbird]

Hi,

I have a bit of knowledge on Access, although I still use the 2003 version of the software.
I have just built up a database and I have a question about validation rules on Forms.

I have four dates on my form:

Proposed Date
Scheduled Date
Approval Date
Rejection Date.

I would like to build up a validation rule among these fields. Access allows me to create this in the Proposed Date field property table:
Validation rule: [Proposed Date] < [Scheduled Date]
I can also create [Schedule Date] < [Approval Date], but in both cases I need to fill out the Scheduled Date field at first, which doesn't come in a logical order on the form.

My aim to build up a chronological validation rule among three dates. I also would like to avoid that somebody types a date in both Approval and Rejection Date field on the same record.

Is there anybody who has any suggestion?

Many thanks,
Susanna

 

[boblarson]

Don't use validation rules. Use the BEFORE UPDATE event of the form instead and you will find better results. Your Before Update event would look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim blnError As Boolean
   Dim strErrMsg As String
 
   If Me![Proposed Date] < Me![Scheduled Date] Then
      blnError = True
      strErrMsg = "Proposed date can't be earlier than Scheduled date" & vbCrLf
   End If
  
   If Me![Scheduled Date] <  Me.[Approval Date] Then
      blnError = True
      strErrMsg = strErrMsg & "Scheduled date can't be earlier than Approval Date" & vbCrlf
   End If
 
  ' and then do the other checks if you have them as well just like those.  Then
  ' at the end use:
 
   If blnError Then
      MsgBox "You have errors that need to be corrected: " & vbCrLf & strMsg
      Cancel = True
   End If
 
End Sub

 

[zbird]

Hi,

I really appreciate it! I couldn't have figured this out alone.
Many thanks,
Susanna