Automatic alarms for upcoming events

[schlow09]

I have been trying to automate certain steps in this database for a company I am working with. I would like to have a query or report run automatically at startup showing all the records that have a due date (the date when people are supposed to pay) of a week from the current date so I can see which orders have a payment due date that is near to come. This date is a field in the orders table. I don´t know if anyone could help me with this, I don´t really know how to tackle this situation. The idea is to have an automatic reminder of which clients have to pay in the following week each time you open the file.

If anyone has any advice, or maybe a complete different idea for completing this task I would be really grateful.

Thanks in advance,

[janders468]

I am assuming the hang up is how to fire the query or report on startup and not how to design the query or report. You can do this in two ways. You can create a macro titled AutoExec and have it run your query or report, or you can go to tools..Startup select a form to open on startup and in that form's open event you can have it run the query or report.

[vksingh24]

You can follow the following steps in order to fix your problem

1) Write a query that will return the desire result
2) Create a report and set it recordsource to query (of step 1)
3) Create a macro and name it "autoexec". In this macor Selct "openReport" as the action and select report name ( of step 3)

Please write back if you have any question.

[schlow09]

Thanks a lot to both of you guys, the AutoExec worked perfecty to run the query at startup, so that is fixed. However, I´m still having trouble with finding the records with a date that is within now and a week from now. I tried using DateAdd("d",7,Date()) but that gave all the records with the date seven days from now and only that day. How can I make the query get me all the records with the date between today and 7 days from today??

once again, thanks a lot

[schlow09]

I think I managed to do what I was asking for in the last post..

In the query´s date field I put a filter like this: Between Date() And DateAdd("d",7,Date()) and I think it worked...

Now I just want the AutoExec macro to open the report at startup ONLY if the query has results. Sometimes there won´t be any orders that will expire in the following week so the query will have no results, and I would like the AutoExec not to open the report if this is the case.

Thanks, if anyone can give a little advice

[janders468]

Before I begin I would say that you should put all of this in a vba function and just have the AutoExec macro run that function using runcode (you have to use a function a sub won't work for runcode). You could make another query that has a single value which is the count of records in your other query. There are many ways you could extract that result, you could throw it into a recordset or use a dlookup, I'd be happy to expound further if you need help on either of those. In your code you could then have an if statement that checks if the value is greater than 0, if it is run the query, if not exit the function and resume the normal startup.

[nico5038]

For having the data from the last seven days you'll have to use:

DateAdd("d",-7,Date()) 
 

See the "-" I added :-)

Personally I prefer to use the short version:

WHERE dtDatefield between Date() and Date() - 7
 

as it performs faster as the DateAdd function.

I would also switch from using a query at startup into showing a mainform with a datasheet subform based on the query. Thus the user can see the result and print a report, but also continue to another form when no report is needed.

Nic;o)