Help with Outputto and parameters

[Stoss]

I was wondering if someone could give me some advice. I have a report that is run using parameters [Start Date] and [End Date]. This is run from a cmd button, which works just fine. However, I now need to ALSO have an automated way of doing this task (basic date manipulation later on).

So, the question is, is there a way to pass parameters with VBA to output the file to a .rtf?

Thanks,
Stoss

 

[John Big Booty]

If your report is currently collecting the From and To dates from either a referring form or is calling for them to be manually input this situation should not change just because you are using DoCmd.OutputTo to send view this report in RTF format.

 

[Stoss]

Thanks for the response John.

The problem is, when I run these reports manually it ask for dates and I put them in, easy enough, but I need to run these automatically as well as manually. I can manipulate the dates later on but for now my hurdle is passing those parameters automatically (just as if i was typing them in manually)

I wanted to keep the same report and query for that report so I am not duplicating reports and queries just to get this to work.

Any ideas?
Stoss

 

[John Big Booty]

In the query you can use Forms!FormName!ControlName is criteria, you can then have a two fields on your form that provide the from and to dates.

Alternately if the from and to dates are based on the current date you could use Date() to return the current date and the DateAdd() functions, in the Criteria, to set you from and to dates relative to the current date.

 

[Stoss]

Again, thanks for the reply.

Well, at this point, I guess I am not going to be able to do what I want to do with it. Making the controls with the dates in them is a good idea, just seems clunky. I dont like having hidden controls on the form. Anyways, i really appreciate your help. I guess I am still confused why passing parameters through VBA to a report is that hard. MS needs to correct that.

Thanks,
Stoss

 

[John Big Booty]

Can you perhaps explain step by step exactly what you are trying to achieve, as I not entirely sure I'm following you

Also are your start and end dates totally dynamic, or are they related to the each other and/or to the current date?

 

[Stoss]

Yeah, I see how this can be confusing...let me try to explain.

I have a report that I run usually from a button click. When a person clicks this, they are asked a [start date] and an [end date]. I want this dynamic just in case "the manager" want to put in a different date criteria. However, I now did to have these same reports automated AS WELL as keeping the button click available. So, every Monday, for example, I need a report to run automatically for the previous 7 days. In this case, these [Start Date] and [End Date] will be semi-static (I will program in the dates to be entered bypassing user inputs).

In other words, with 1 report that has manual user inputs I also want the same report to be used where I can programmically enter in those "user inputs".

Hope that makes a little more sense!
Thanks,
-Stoss

 

[Stoss]

Oh, I should have also mentioned that this automated approach is using the DoCmd.Outputto to run this (as I want it to be hidden without the user seeing it run)

-Stoss

 

[John Big Booty]

OK, here's how I might achieve this.

Firstly create a copy of the query that you are currently using as the Record Source for your report (we'll call your current query Query1, for simplicity), we'll call that Query2, now in the criteria for your start date put Date() and in your to date criteria put DateAdd("d", -7, Date()) this will give you a report for the previous seven days, you can adjust this to suit your own needs.

Now make a copy of your current report and change it's Record Source to Query2

Now assuming that your DB is closed down at the end of each day and opened again the following morning you can use the following code in the On Load event of your switchboard or welcome form;

If [URL="http://www.techonthenet.com/access/functions/date/weekday.php"]Weekday[/URL](Date()) = 2 Then
     DoCmd.OutputTo (........
End if

 

[Stoss]

Thanks again for all your help.

If I am understanding you correctly. The only way to achieve the goal I want is to have 2 reports (the exact same (minus the recordsource)) and 2 querys that will function nearly the same?

In my initial thought process I thought this would end up being my only option. I was hoping that I could avoid this just because in the future if the reports get change, I will have to remember to change the other report and/or query. I guess that still seems very unfriendly to have to do it this way but I guess that is my best bet at this point and time.

Dear MS, please allow me to pass parameters to a report!!!

Again, Thanks so much for your time and advice. You have been a great help!
-Stoss

 

[millwheal]

I've just seen this whilst looking for answers to my own problem, so this is just a quick thought ...
I'm fairly sure you can change the data source of the report in your code, thus enabling you to have a single report, but two queries.

 

[Stoss]

Can you do that if you are just using an docmd.Outputto (i.e. NOT docmd.openreport)?

If so, do you know what that code is? I looked before and didn't find much on it.

Thanks,
Stoss

 

[millwheal]

Stoss,
I don't know if it would work with 'OutputTo', but what I had was several reports, each with up to six different sources, each report being run via a command button on one of several forms.
What I with did was:
1. Defined a public variable which I set, in each of the command button 'On Click' event procedures, to indicate the data source, before opening the report.
2. Created an 'On Open' event procedure for the report, which determined [using Select Case] the intended source and set Me.Recordsource to the name of the appropriate query.
This started with a single report with two possible sources, so the source names were hard-coded into the procedure. It gradually expanded to more reports and more sources, and I continued with the hard-coding of names [just more Cases in the Select Case]. I always meant to go back and look at putting the record source name in the public variable or even passing it as a parameter, but ... you know what they say about "if it ain't broke"!
Assuming that 'OutputTo' actually "opens" the report, I would have thought this would work, but as I said, I don't know for sure.