Pop Up Box 2

[louisa]

A little while ago John Big Booty helped me out with some code in order for a pop up box to display a reminder if a documents received box hasn't been completed after 3 days. I now need a pop up box for a field called ContractEndDate - this will be filled out with a date and i am hoping someone can help me with getting a pop up box to remind me of this 60days before the ContractEndDate??? Any help would be great.

 

[JamesMcS]

You'd need to put it in an event but you could create a quick form - so if you've got a main form that opens with the database, put something like "if contractenddate=date(now()) then open popupform"

Then just create a quick little form with a text box for contract date and a command button to run an update query, to update contractenddate to the contents of the text box.

 

[John Big Booty]

Try something along the lines of;

If ContractEndDate - 60 = Date() Then
     MsgBox "Contract ends in 60 Days" [COLOR="Green"]'Or insert any other appropriate action[/COLOR]
End If

 

[louisa]

Hi all, i am trying to get the above code to work. I have placed it in the on load event of my form and put in a date of 17/12/2010 for testing purposes. However i cannot for the life of me get it to work.

 

[John Big Booty]

Hi all, i am trying to get the above code to work. I have placed it in the on load event of my form and put in a date of 17/12/2010 for testing purposes. However i cannot for the life of me get it to work.

That date is only 31 days in the future for the code to work you would need to change it to something like;

If ContractEndDate - 60 <= Date() Then
     MsgBox "Contract ends in Less than 60 Days" [COLOR="SeaGreen"]'Or insert any other appropriate action[/COLOR]
End If

 

[John Big Booty]

....and I'd probably put the code in the On Current event rather than the On Load event.

 

[louisa]

Hi John,

That has worked perfectly but as there may be more than one record that is due to end in less that sixty days i was hoping it would alert on opening the db. Also is there a way i can assign it to a customerI.D so i can see the name of the customer on the alert?

 

[DCrake]

What you need is a simple query with the calculation John provided in your condition line. Include in your query the ID and name of the person/company you want to view.

Next create a new form with a list box on it and set the record source to this query. Make the form a model popup form.

Then on the launch of your application do a count on the query to test for existing records, if any found then launch the overdue form, which will display all the records that match the condition.