Limit/force date entry

[SueBK]

I have a table capturing project activities with three date fields (opened, started, closed) that is fed by an entry form used by Project Managers. Opened is filled automatically with today's date when the record is created. The other two are supposed to be actual dates (ie already started or already closed; not we hope to start/close).

Almost complete edit of my post after chatting to my boss:

I want to limit the started and closed fields to any date in the past OR any date occuring in the next 7 days. If a PM tries to enter a date outside that range I want to code a pop up message explaining why they can't, and to clear the date field back to Null.

Do I do the date limitation through the validation rule? or in my VB code? If in the VB code, how do I go about that? I think I can do the message box and clear to null, 'cause you've all helped me do that for other areas of the DB.

 

[evanscamman]

In the BeforeUpdate event of your ClosedDate field:

If ClosedDate > Date()+7 then
Msgbox "You may not enter a future date!"
Cancel = True
ClosedDate.Undo
End If

This should do it.
Evan

 

[missinglinq]

This should do what you want:

Private Sub StartedDate_BeforeUpdate(Cancel As Integer)
 If Me.StartedDate > Me.OpenedDate Then
  If MsgBox("This is an ACTUAL date field. Explanation blah blah. Are you sure you wish to enter this date?", vbQuestion + vbYesNo, "Enter This Date ???") = vbNo Then
   Me.Undo
  End If
 End If
End Sub

Make sure that code you're using to set the opening date to today is using Date() and not using Now().

 

[evanscamman]

I finished my post and then saw that you had changed your question.
I edited my first response to answer it.

Evan

 

[SueBK]

Evan
Works beautiful, except ... to doesn't return the box value to zero - but it won't let you leave until you put a valid date in it. Possibly more effective, but could be potentially very frustrating for PMs without a lot of Access experience.

My field is called "Started", my box on the form is called "StartDateBox" (they were both the same, so I changed one to avoid any potential conflicts). The code I've got is:

Private Sub StartDateBox_BeforeUpdate(Cancel As Integer)
If Started > Date + 7 Then
MsgBox "You may not enter a future date!"
Cancel = True
Started = Null
End If
End Sub

Edit: Moved the code to "After Update". Works perfectly. Thank you.