Query on start up / load

[KEVWB]

Hi,

How can i get access to run a query on start up and if the query meets criteria get it to send an email, send-object, if it doesn't then just get it to open as normal.



Ta

Kev

[orange]

What have you tried so far, and what were the issues/problems?

[KEVWB]

What have you tried so far, and what were the issues/problems?

Hi ,

thanks for the reply. I have a calibration database and have three queries , the first is for overdue calibration items, the second is for calibration items that require calibration this week and the third is a query that will give advance warning 1-3 days before an item is due.

What I would like to due on load is send an email with report attached for both late & due items if identified by the query. I have tested the queries with dummy data and they work well however I can't figure out how to send the email if the query finds the data.

Any suggestions that you can give would be much appreciated.

Thanks.


KevWB

[pbaldy]

You can use a DCount() to test whether the query returns any records. If it does, you can use SendObject to send out a report based on that query.

If DCount(...) > 0 Then

[KEVWB]

Good Morning Paul and Thanks,

Not come across this before but will give it a go and let you know, thanks for the response.

Ta

Kev

[pbaldy]

No problem Kev; post back if you get stuck. Welcome to the site by the way!

[KEVWB]

Hi,

I have tried dcount() but I am a little stuck. A macro opens the query[late calibration] first, I then have the following if statement;

If DCount([next due],[late calibration])>=1

Then sendobject

[next due] is a field within the query

I have also tried

If DCount([late calibration]![next due],[late calibration])>=1

Every time I run the macro I get the following error

Access cannot find the name"next due" you entered in the expression.

Any advice would be much appreciated, have also tried using count() but get the same result.

Thanks in advance

Kevin

[pbaldy]

You don't need to open the query, Access will do that internally. The DCount() uses string arguments (and since you just want a count, * is more efficient than specifying a field):

If DCount("*","[late calibration]")>=1 Then

[KEVWB]

Hi Paul,

it's me again, tried what you suggested but I am now getting;

Access cannot parse the expression If DCount("*","[late calibration]")>=1, have also tried without [] but does not make any difference, any suggestions, thanks for your help and patience

Ta

Kev

[pbaldy]

Is "late calibration" the name of the query? Does it run okay if you open it directly? Did you add the "Then" after that part? Can you post the db?

[KEVWB]

No problem Kev; post back if you get stuck. Welcome to the site by the way!

Hi Paul,

Tried again, it worked just as you advised, I owe you one,much obliged

Ta

Kev

[pbaldy]

Happy to help Kev.