Delivery Date Message

[Ade F]

Here's a question that may require some VB. I'm currently reading ACCESS 2002 VBA (ISBN 1-86100-821-X) and its a steep learning curve at the moment.

My situation is this, within the DB many manufacturing projects are currently created and a delivery date specified. Basically is there a way of the DB looking up a individual project delivery dates and two days before this is due showing a message box to the tune of

"You have 2 days to go on the JOE BLOGGS PROJECT. Please make sure all relevant info is updated to the project file"

The main thing being that the message displays the actual project name the date is relevant to. I'm sure there is a way and any comments would be much appreciated.

Regards

Adrian

 

[dcx693]

How often do you start up your database? If you do it often, you can have some code load at startup that does a simple date check. Access lets you specify a form to load at startup, and forms can have code that runs when the form loads and opens.

That code could load the due date data, do a count of how many projects fall within the two-day period and bring up the results within a form so you can view them.

If your database is left open for long periods of time, I think it's a bit trickier unless you have some existing code in it do a periodic check for project due dates.

 

[Ade F]

A little tricky

The DB does not run 24/7 so I would like the message box to appear at start up of the DB as well as when entering the Project form (continuous forms containing projects) .

The tricky thing is that the message box would have to decribe exactly which project is due for checking / updating (as above).

As far as simple date checking I dont have much knowlege as to how to implement this. Perhaps I little more help could be provided on this subject?

Regards

Adrian

 

[neileg]

Create a query that returns the name of the project and the delivery date. Add a criterion for the delivery date of 'Date()+2'

This will return all projects where the delivery date is today's date plus two days. If you base a form on this query and set it to load everytime you open the database, or when you open your main form, I think you get what you want.

However, unless weekends are normal working days, you will have to consider how you are going to deal with delivery dates that are Monday or Tuesday. It is quite possible to account for non-working days in Access, but I think you should leave this just now.

 

[Ade F]

Solution

Your solution is great although to put the cherry on the cake it would be nice to have the form only pop up if there is any data within the query.... any ideas? Thus not bringing up a form that has blank entries.

Great work.

Regards

Adrian

 

[Fizzio]

If you are using the query to populate the form - I generally use an unbound form with a listbox to identify items that need to be acted upon rather than a page per record / continuous form but the method is entirely up to you. If you use the query for the form's recordsource on the on open event

Sub Form_Open()
if me.recordsetclone.recordcount <1 then
cancel = true
end if
end sub

[b] or if you use the listbox to show the results[/b]

Sub Form_Open()
if me.nameoflistbox.listcount<1 then
cancel = true
end if
end sub

 

[dcx693]

Just curious, but how would you guys suggest handling this type of situation in a db that ran 24/7? Or that might run for long continuous periods with no guarantee that any particular form would be loaded (except perhaps a startup form)? Can you leave code running the background using a startup form and a timer event to periodically run code?

 

[Fizzio]

Have a look here at how we have been helping Ade to add this functionality to one of his applications.

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=190861#post190861

 

[dcx693]

As I suspected. Great stuff. Thanks.

 

[Ade F]

Many thanks fizzio

Again your knowledge has been excellent. Hope to chat weith you again soon.

 

[Fizzio]

Thanks and no problem.