Using a MACRO to run Queries and save as Excel

[rbraitz]

Good Morning, I have an access database where I wrote a simple macro that exports a simple query into excel format and then emails the excel spreadsheet to the recipient. For example, the database is sales records, and basically I am sending each salesperson a list of their daily sales. Problem I have is that some salesmen don't have sales for a particular day, so the macro is sending out empty excel spreadsheets. How can I place something in the access macro that says/looks to see if the query has results. I am not a coder and don't know VBA.....but if I need to change the macro or the query that would be ok too. The macro has 100 events in it, one for each salesperson. TIA

 

[rbraitz]

Like micron I don't use macros either. I think the essence of your issue lies in

"I have macros set up to email REP1 - REP100." But you don't want to email all these Reps.
You only want to email those REPs who have had sales in the period.
To do so, identify the REPs with sales, then for each of those--send an email.

I believe this is the logic. I am not familar with macro coding.

I think that's what micron is advising as well.

So, would something like this work in order to use dcount?

run 1 query that first combines the sales information into "table2" but has a count by sales rep
so the query would look something like this
salesrep1 = 2 total
salesrep3 = 1 total

so, salesrep2 would be excluded

Then use the dcount and go against this table
use IF dcount ...... > 0, then run query 1, (for 1st salesperson)
use if dcount > 0, then run query 2 (for 2nd salesperson)

i understand that it can be messy, but would that work?

 

[Micron]

After my last post I did read that a query (or table you populate with a query) would be a work around but didn't post that right away - had to go out. You wouldn't need the DCount because this new data source would only contain records with sales, yes? I'd lean towards using a query that returns only records with sales rather than having to wipe a table, re-populate it with those records, then execute a queries against it (in your macro) every time you run this exercise, which I presume is every day. Main reason is that you'd have to redesign your macro from scratch - 100 new actions - and hope no changes are on the horizon, but I'm going to get off of that soap box.

 

[james_lankford]

if you
Create a New Macro
and then
Add New Action
one of the action choices is
If

which adds an
If Then Else
you could play around, experiment and try stuff out -- you'll probably figure something out

 

[Micron]

if you
Create a New Macro
and then
Add New Action
one of the action choices is
If

which adds an
If Then Else
you could play around, experiment and try stuff out -- you'll probably figure something out

I think maybe that was covered in post 9?

 

[JonXL]

Use VBA, as suggested.

Add the email address to your salesman table (if not there already).

Parameterize your spreadsheet query.

Create your query of sales joined to salesman.

In the code, use a recordset to open the joined table, loop through it (it'll only have salesman with sales), set the parameter for your spreadsheet query, run query, export.

This is set up once and maintains itself. No more hundred macro actions to change every time you want a shake up the business logic (as you did here).