Consider:
Code:
If IsNull(Me.StartDate) Or Me.StartDate = "" Then
MsgBox "The StartDate you entered is incorrect", vbExclamation, "Date Error !"
ElseIf Me.StartDate > Me.EndDate Then
MsgBox "Start Date should be less than End Date", vbExclamation, "Date Issue!"
Me.StartDate.SetFocus
Else
AssignDate = Nz(DLookup("StartDate", "ProjectSchedule", "Activity='" & DMax("Activity", "ProjectSchedule", "Activity<'" & Me.Activity & "'") & "'"), Date)
If Me.StartDate < AssignDate Then Me.StartDate = AssignDate
End If
EDIT: I posted before reading previous posts (I was testing and composing). The sample db only has one table so was not aware of the relationship to projects. My suggestion should be adaptable for the additional PID criteria. It worked for the data structure provided.