Can I Run 1 Report 150 times with a prompt filled in each time by a Macro?

[shepherd01]

I have created a report that prompts me to enter a store number when I open it. For instance, when I click it, it asks "Store number" and I type 140 into the popup box and then the resulting report is for store 140 ONLY.

I basically want to run the same report again and again (using macros) to create a new report for each store, without having to create 150 separate copies of the report (that would each be then run once). The reason is because I've already tried this! Everything works fine until you want to change something minor about the design of the report. Then you have to go through change 150 reports instead of just changing the "Master" copy.

PS, I'm going to paste below a similar question from a member named ROACH. He seems to be having the same problem, but nobody replied to his post!


08-18-2004, 12:54 PM
Roach
Registered User
Join Date: Jun 2002
Posts: 13
Macro to fill in query/report prompts
--------------------------------------------------------------------------Greetings all - Are there any macro commands that fill in prompts from a query / report? I want one report to be automatically e-mailed to a few different distributions. The report prompts for a Client, Channel, and Format, among other things. How can I tell the macro which specific Client, Channel & Format to fill in without it "asking" me? Great thanks.

Peace,
Roach

 

[pbaldy]

I know you're looking for a macro, but I don't use them. Here's a code solution that does what you want, presuming you have a table with store ID's in it. You'll obviously have to change the names from this test data I used to test (this correctly printed a report for each employee):

  Dim strSQL           As String
  Dim strCriteria      As String
  Dim db               As DAO.Database
  Dim rs               As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT ID FROM tblEmployee;"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  Do While Not rs.EOF
    strCriteria = "ID = " & rs!ID
    DoCmd.OpenReport "rptEmployee", , , strCriteria
    rs.MoveNext
  Loop
  
  Set rs = Nothing
  Set db = Nothing

To use this, you'd take the criteria off the report, so that if run by itself it prints all stores. The code will restrict it to each store.

 

[shepherd01]

Thanks!

I'm going to try your suggestion. I made a report that is based on a Query and it's in the Query where the Criteria are set, not in the Report. Is that going to matter if I use this code?

 

[shepherd01]

Problem...

Is this code missing something? How do I get it to start running?

P.S. My VBA experience is rather limited, but I have all day tomorrow to fiddle with this.... so I think I can do it!

 

[pbaldy]

First question: as I mentioned, the code is based on it passing the parameters to the report, not the query passing them. The query should return all store's data; the code will restrict the report to each store in turn.

Second question. The code is not missing anything; it worked exactly like that in my test. As I mentioned, you'd need to replace the table and field names with yours. In my test, I had this code behind a button. You should put it in whatever event you want to trigger the printing.

If you don't figure it out right away in the morning, post a sample of your db and I'll add this to it.

 

[shepherd01]

The Database

Good morning!

I'm posting my database here because I just don't have enough VBA experience to implement this. (P.S. I ended up having to post it on the web due to the file size being larger than the 100K limit. See Link at end)

(The dollar amounts shown in the sample database are fake)

Please run "Macro 1" first to see what the result of the database output is! (don't worry, nothing malicious!). You have to choose a number between 1 and 27 when it asks you for the "Email Group" and I've also left out the email address, so that you can have a chance to look at the attachment before sending it. (Also, it may not look quite the same on your computer as it does on mine because I had to set up a "custom page size" on my computer to accomodate such a tiny report.) The report is designed to be viewed on a Blackberry, so that's why it's so narrow. And yes, Paul, I know that I'm supposed to get rid of the criteria, but I left it in there just so you can see what I'm talking about.

Each "Email Group" is linked to a certain group of stores which end up summarized on the report.

Paul, I also created "Form 1" where I did put a button, and I did put your raw code in there. I initially tried to change your code to match my database, but for the purposes of this example, I "reset" it back to your original code.

Thank you so much for your help!

www.josephshepherd.com/Bluebook Results Database (WEB).zip

or

http://www.josephshepherd.com/Bluebook%20Results%20Database%20(WEB).zip

Sincerely,
Joe

 

[pbaldy]

Oh, emailing a filtered report is a horse of a different color, but still doable. I assume you want to send the report to all 27 email groups, each filtered to its stores? I assume you have addresses stored somewhere? You'd want to apply them instead of the hardcoded email address if so (I removed mine after testing). Hopefully I've remembered all the changes I made. If you want to PM me an email address, I'll send you the version with the changes I've made. In summary, I:

Added a public variable to hold the current email group
Added code to the report open event to filter using that variable
Added DAO reference
Modified code behind button

The code as is will send an email to the hardcoded address for each email group (I tested that). I'm guessing you want to send each to a different address, which will require some changes, depending on how you get the address. Some other thoughts:

Don't use spaces in your object names. More trouble than it's worth.

Don't use Date or Month as field names (or #). They are reserved words and may cause you trouble:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187

The Bluebook table looks like it has the start of repeating groups (2003, 2004) which is generally not a good design idea. You might want to search here on "normalization".

 

[jewels]

More info

Att pbaldy.
This is similar to what I am trying to do but I need to send each filtered report to a different report name in snapshot format.

Can you please help me with the additional code to do this.

Thanks,
Jewels