Do not send email object if table/query is blank

[jgabel]

I have MS Access macros that emails information from tables and queries as attachments. Sometimes the data is blank, do you know of any options in which I can prevent the send object from sending the email attachments if the data is null?

Thank you!

 

[pbaldy]

I don't use macros, but you should be able to use DCount() to test if the table/query contains records.

 

[jgabel]

Thank you for your assistance! I performed a DCount off my query, although I'm not sure I did it right or understand reason why. Here's what my DCount expression looks like:
Count: DCount("EMPLOYEE","qry_audit_emp_demo")
The query is called: qry_audit_emp_demo and I chose to count the Employee number field it's numeric. My criteria in the query is hire date>9/1/2012 and when I run the query I get a total of 5 rows which is correct, but my count field indicates 0

 

[pbaldy]

The count wouldn't be in the query, it would be in the macro. I think it's called a condition in macros? In VBA it would look like:

If DCount("*", "QueryName") > 0 Then
  DoCmd.SendObject...
End If

 

[jgabel]

Thanks, I included the conditional format within my macro, but I'm not sure of the syntax, here is what I have - If DCount("*","qry_audit_emp_demo")>0 Then "DoCmd.SendObject", End If)
Thank you for your help!

 

[pbaldy]

Like I said, that was VBA code. I think your macro condition would be:

DCount("*","qry_audit_emp_demo")>0

which should make it only send the object if the count is greater than 0.

 

[jgabel]

Paul, makes sense now, sorry to be so dense!

It works perfectly now, if query count is null then it does not send email which is exactly what I was hoping for.

Thanks again

 

[pbaldy]

Excellent, glad it worked for you!

 

[jgabel]

The DCount in my Macro conditions works great in that the SendObject works if the count of the query or table >0, if the query or table =0 however I want the macro to continue to the next macro action, it seems to be stopping macro if dcount=0. How do I get it to continue...
My conditon expression: DCount("*","EPICtraining_Transfer_List")>0 then send object

 

[pbaldy]

I don't use macros, but I thought the condition only applied to the line on which it appeared. I use VBA code, where you have a lot more control, flexibility and error handling. In help it says:

You can have one condition control more than one action by typing an ellipsis (...) in the Condition column for each subsequent action that you want the condition to apply to.

So it would appear the failing the condition should not stop the macro.

 

[jgabel]

Thank you for your quick reply!

After the DCount condition>0 then send object my next condition is
Condition Action
... Open Query

I tried the ellipsis (...) in the condition line below the Dcount but it's not allowing me to continue with my macro.

 

[pbaldy]

The ellipsis make it apply the same condition. It sounds like you want to remove that.

 

[mahenkj2]

DCount("*","EPICtraining_Transfer_List")>0 then send object

after the above macro,

in next macro, try to use "OnError" in action and in this use "Next" in GoTo argument.

after this, you may continue your other macros.

hope it helps.

regards.

 

[jgabel]

Thank you for your assistance,

I have Access 2000 and it does not appear that I have the OnError action within my macro action drop down list, need to upgrade, lol

Any other suggestions i might try? Thanks again!!

 

[pbaldy]

Did you see:

The ellipsis make it apply the same condition. It sounds like you want to remove that.

 

[jgabel]

Thanks Paul, I did remove the ellipsis.

 

[pbaldy]

And does the macro continue beyond the failed condition now?

 

[jgabel]

Thanks Paul, I removed the ellipsis.

 

[mahenkj2]

OnErrror is applicable in A2007 onwards, may be.

I am not sure but you may try to search ALL ACTIONS AVAILABLE.

 

[jgabel]

After removing the ellipsis it still stops the macro after the condition of
DCount("*","EPICtraining_Transfer_List")>0 then Send Object action

Thinking the receipient of the report can learn to deal with a blank report. Not sure how critical this is.