Validation problems...

[dbDamo]

Hi

I am having some difficulty adding some validation rules to a few fields in a form and would be very grateful for any help/advice.

Here is what I am trying to achieve:-

Test Start Date

>= Project Start Date
AND <= Project Finish Date
AND Cannot be Null when Test Status (list box) equals either "In Progress", "Incident" or "Complete"

Planned Completion Date

>= Test Start Date
AND <= Project Finish Date
AND Cannot be Null when Test Start Date Is Not Null

Completion Date

>= Test Start Date
AND Cannot be Null when Test Status (list box) equals "Complete"


I'm ok with ensuring the dates fall within the correct ranges, but I get lost when trying to add the remaining rules. I'm working in Access 2000.

Thanks in advance.

 

[HiTechCoach]

I normally handle this with each control's before update event and also again in the form's before update.


Example:

Private Sub Planned_Completion_Date_BeforeUpdate(Cancel As Integer)
' >= Test Start Date
'AND <= Project Finish Date
'AND Cannot be Null when Test Start Date Is Not Null


If Me.Planned_Completion_Date < Me.Test_Start_Date Then 
   MsgBox "Planned Completion Date must be  >= Test Start Date."
   Cancel = True
   Exit sub 
End  If

If IsNull(Me.Planned_Completion_Date) abd  Not IsNull(Me.Test_Start_Date) Then 
   MsgBox "Your message here."
   Cancel = True
   Exit sub 
End  If



If Me.Planned_Completion_Date > Me.Project_Finish_Date Then 
   MsgBox "Your message here."
   Cancel = True
   Exit sub 
End  If

End Sub

I also repeat the checks in the form's Before Update event.

 

[DCrake]

Test Start Date
>= Project Start Date
AND <= Project Finish Date
AND Cannot be Null when Test Status (list box) equals either "In Progress", "Incident" or "Complete"

Ok you first need to priorotise the rules

Select case Me.LstTestStatus
   Case "In Progress", "Incident", "Complete"
      If IsNull(Me.TestStartDate) Then
          MsgBox ErrMsg
      Else
         '...validate date range
      End If
End Select

In the above example you would only need to validate the date range if first the test status deems you should and the fact that the user has entered a date in the field. By placing the most obvious one first it makes the need to perform more validation checks less likely, thus improving performance and efficiency.

David

 

[dbDamo]

Thanks for your response. I have just realised however, that some of the fields used for the validation are from a sub form within the main form and I am now having trouble referencing the fields from the subform.

From the example you have just given me, the field from the sub form is "Project Finish Date", the subform is called "Project Subform" - any ideas how to refer to it?

 

[DCrake]

You could create hidden controls on your main form that refer to controls on your subform and use these for the validation. To enable referencing use the expression builder to create the link.

 

[dbDamo]

I can't believe I didn't think of that!!!

Thanks very much.

 

[dbDamo]

Private Sub Actual_Start_Date_BeforeUpdate(Cancel As Integer)
'Is Not Null when Test Status = In Progress, Incident or On Hold
'AND >= Project Actual Start Date
'AND <= Project Planned Completion Date
Select Case Me.Test_Status
    Case "In Progress", "Incident", "Complete"
        If IsNull(Me.Actual_Start_Date) Then
        MsgBox "Actual Start Date must be entered if the Test Status is either In Progress, Incident or Completed"
        Else
        If Me.Actual_Start_Date < Me.Project_Actual_Start_Date Then
        MsgBox "Actual Start Date must be >= Project Actual Start Date"
        Cancel = True
        Exit Sub
        End If
 
        If Me.Actual_Start_Date > Me.Project_Planned_Completion_Date Then
        MsgBox "Actual Start Date must be <= Project Planned Completion Date"
        Cancel = True
        Exit Sub
        End If
        End If
End Select
 
 
End Sub

This code works well, the only problem being that the user can still tab through the Actual Start Date field without entering a value when the test status condition is met. If the user enters an incorrect value into the Actual Start Date field and then deletes it, the error message appears when the user tries to tab out.

Would this be solved by adding the validation to the forms before update event? If not, any other ideas?

Cheers

 

[DCrake]

On the lost focus test for Null or Empty string in the textbox and only perform the validation if there is something to validate. also if you want to prevent the user from bypassing the start date then on the GotFocus of the End Date get it to test for a date in the start date and if it is Null then set the focus to the start date. Also on the got focus of the start date get it to clear all the subsequent associated fields that are reliant on the start date validation routine.

 

[dbDamo]

I am pleased that I may have given the impression that I know a bit about vb, but I have only been learning it for the last week so I don't really have a clue!!!

Is there any chance you could explain how to do this?

Cheers

 

[dbDamo]

I've added all of the validation rules to the forms before update event and it works nicely. Thanks for all your help!!!