Check before Update Query run

Question

I need to check if a particular date in a table falls before another date and modify the contents of that particular rows data with other contents.

for eg: all the customers requests that fall before 10-05-2009 are to be udpated "no responses"

This query should run automatically as and when I open the database.

Help appreciated.

Answer 1

Hi, the below example looks at a field "request date" in table tblactivity and updates the comment field if the request date is over 30 days old. You could call this from your default form load event if you are using a default form in your db.


Public Sub UpdateComment()
Dim strSQL As String

strSQL = "UPDATE tblactivity SET tblactivity.Comment = 'no responses' WHERE (((tblactivity.[Request Date])<Now()-30));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub


Regards

ADG