send-object command not to send if no records in query

Question

I want to use the macro' "send-object command" to email the records of a bunch of queries on a daily basis, but I only want it to send the given email if there are records in the given query. if the query doesnt output any records, then I dont want that email to be sent. I know how to accomplish this by using an Acces report by setting the "on no data event", but I would prefer that my macro doenst run the Access reports and just runs the queries , because outputting the data in reoprt format uses to much resources on my computer. please note that I do not know VBA, so I would like to know if I can accomplish my objective without any VB programming. thank you very much for your advi

Answer 1

Unfortunately you need VBA to accomplish this. Are you willing to put some code in ?

Answer 2

I believe @JonH is right that you need VBA for this. However, it could be fairly easy VBA.

Say you have a query named MyQuery, and you want to do something only when that query returns one or more rows.

If DCount("*", "MyQuery") > 0 Then
    'do something '
End If

You could replace do something with the VBA equivalent for whatever your current macro does. You can convert a macro to VBA by right-clicking the macro name, choosing Save As, then selecting Module in the "As" box. If you run into trouble, show us the converted macro, and we can show you how to adapt it to run only when your query's record count is > 0.

Answer 3

In a macro, you can use the CONDITIONS column to test a DCount() expression to see if the results are >0 and then your SendObject will run.

To do this, open your Macro with the SendObject line.

From the VIEW menu, select CONDITIONS. This will add a column to the left.

In that column, put something like this:

  DCount("*", "MyQuery", "[conditions that are being tested, if necessary]")>0

This is a DCount() to see how many records the query returns. You'd leave the third argument out (along with it's leading comma) if your query is already appropriately filtered (e.g., it has a reference to a form control as criterion). If the query returns 0, the CONDITION returns FALSE and your SendObject command won't execute.

You'll have to figure out how to construct the DCount(), but the point is that by testing how many records will display in the query, you can conditionally execute the SendObject.