Flag a field when due date is passed

[Guest]

Hello,

There is a date field (mm/dd/yyyy) that the users fill in with their desired
due date for the tasks. What I need to accomplish is this:

1. Have a flag field (check box) checked when the due date is passed...
2. Change the flag color to red (jumps at you when you open the record)

It would also be nice if there would be a message that pops up at
startup...that would be to the effect of "There are past due dates !! Would
you like to view them?"

Any help on this would be greatly appreciated.

Thanks,
Luther

 

[guido via AccessMonster.com]

There are a few ways to accomplish this. I'm not sure if you have an actual
field in the table that flags past due records, or if you just want to
display a past due on the form. Also, I'm not sure if once a task is complete
if the records are deleted or marked as complete. I've assumed you have a
field for past due and one for complete. If not, the below will still work
with some modifications.

First, create an update query that will update the past due field for all
record were the due date criteria is "<Date()".
Set the On Open event of the form with code similar to:
Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenQuery "Update_Past_Due"
If Not (IsNull(DLookup("[DUE_DATE]", "TBL_MY_DATA", "[DUE_DATE]<" & Date()
& " And Not([TASK_COMPLETE])"))) Then
MsgBox ("There are past due dates !! Would you like to view them?")
'other steps to view task.....
End If
End Sub

On the form, place a text box with the control source similar to:
=IIf([DUE_DATE]<Date() And Not([TASK_COMPLETED]),"Past Due","")
Then set the text box to have red text and formatted you your specifications.
It will only show up if needed.

 

[Guest]

Thank you. I am in the process of designing this form; There is a "date
completed" field that can probably be used to mark the records as complete.
If any of this new information changes anything, please let me know. Either
way, I will try this and report back.

 

[Guest]

Hi,

Can you expand on the "other steps to view task" you mentioned on the
Form_Open event, please?