Pop-up reminder based on date field in a subreport 1

[migv1]

I've done quite a bit of VBA coding in Excel, and now I have to do the same in Access. Unfortunately, the syntax is very different, and I'm lost at this point.

I have created a database of companies with which we maintain service contracts, and users can select from a list of companies to display an individual company report that includes company data, contact information, and subreports for contract information and current projects.

So far so good. Now, my boss wants a pop-up reminder to appear when a company's contract expiration is less than 6 months from the current date. How do you get a message box to appear based on the difference between the current date and the value of an expiration date field in the contract information subreport?

(Also, what's the best reference for someone familiar with Excel VBA to get up and running quickly in Access VBA?) Thanks in advance for any and all suggestions!

 

[RoyVidar]

Hi!

This is the table and relationship forum, this question is perhaps better addressed in one of the other Access Fora, see the Related Forums box at the right, btw, see also this faq on how to get the most out of the membership faq181-2886, it would also outline that a bit more precise questions might lead to more accurate and precise answers;-)

That said, some approaches:
* in the form where you select the company to view, you could put in a condition

* create a report to be run at different intervalls showing all customers where the expiration day is less then 6 month from today date

To calculate, try for instance the DateDiff function, in a text control for instance:

=DateDiff("m",txtExpDate,Date())

To get a popup, then perhaps use the on current event of the form, using something like this:

[tt]If DateDiff("m",Me!txtExpDate,Date)>=6 then
Docmd.openform "frmMyPopUp"
End If[/tt]

It seems however the usual approach would be to use for istance conditional formatting in the format menu (access 2000+ versions) to highlight the control in stead.

The datediff function (take a look in the help files) takes the difference in the timeintervall, so:

DateDiff("m",#11/1/2003#,#5/1/2004#)
DateDiff("m",#11/30/2003#,#5/1/2004#)
DateDiff("m",#11/1/2003#,#5/31/2004#)
DateDiff("m",#11/30/2003#,#5/31/2004#)

all produces 6, so for more accuracy, if needed, you could perhaps use "d" as intervall (days) and calculate... You could also try searching these forums (Access Fora) on "datediff", and perhaps also "month", which should provide lot's of results.

Here's a thread on book recommandations, covering several aspects thread702-793040.

Roy-Vidar