how to auto print a report to file 1

[frosty90]

I have a database that prints reports based on user selected criteria. Basically it pulls information based on a state selected. I use a listbox with each state represented. A qurey is run on click to pull a report based on the records that coinside with that state. After this the user opens each state and prints the report to word then saves them to drive. This can be a time comsuming process for the user. What I would like to do is automate this process. What I have done so far is I can get the report to autoprint the first state in the list but will not continue the loop due to the fact you can only have one report window open at once inside access 2000. I need to know how to make the report once loaded save to file auto print. Any help or suggestion would be greatly appriecated.

Thank you
Chris Frost

 

[BobJacksonNCI]

Just a hint to get you started.
If your drop-down list is not a table, you'll need to create a table and load the list of states. Following works if the list table is named tblState and the field is named State_ID. Depending on the number of states, this could be written differently using an inline list of states. However, maintenance is more cumbersome. Better to have a separate table and then base off that so there's only one place to add a new state.

In my example, The

Dim RST as recordset, TheState as string

Set RST = currentdb.openrecordset ("tblState",dbopensnapshot)

RST.movefirst
do until RST.eof
theState=rst![State_ID]
docmd.openreport "ReportName",,,"[reportsourcestate]=" & TheState
rst.movenext
loop

set RST=nothing

Note that I am quotation mark challenged...
In the docmd statement, you may have to put quotes around TheState


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[frosty90]

Thank you Bob,
I plugged in the sample code today and it seems to try to do what I need. I'm still having a problem with what I need for this database. I will try to explain how I have the report is generated. First I have users that enter information about each state, dates that a state may have been contacted, any notes about the conversation etc. This is pushed to a "tblstates" Then a report is generated based on each specific state. This is how I made this work: I setup a second table "tblcodes" with only the "code" of each state and the corresponding state name. Then I made a query which states that if a user wants a report from Alabama this would be code 100 or 125 or 300.(the "tblstates and tblcodes are both linked with by "codes record&quot Next I made a form with a list box including all the states names. Each state name corresponds with its number or numbers. Based off this query it will open a report indicating which state, state code, notes and date contacted.

Now here is where I have my problem. At the end of every month a report must be generated for every state that has a record. This all, at this point has to be done manually. First the user must click the state they wish to view click the command button and open the report. Once the report is open they need to publish in word. Then save the word document to file. A second state is selected doing the same method and so on.. After all this is done the file containing each report is zipped then e-mail to a different dept. I need to know if the above process can be automated. I keep thinking that the OutPutTo command has something to do with my problem I'm just not sure how.
Thanks again for any suggestions
Chris Frost

 

[BobJacksonNCI]

Chris,

You're swimming in the deep end!
As I understand it, you want:
1. Multiple reports (by state)
2. Export out/Import into Word
3. Make Word save the documents.
4. Zip the documents.
5. Send out as e-mail.

Each of these is a challenge by itself!

And the group is beyond my capabilities. It IS a candidate for an Office collaboration expert…
(THAT’S A HINT TO OTHERS WHO READ THIS)

I've run across post(s) here that explain how to generate e-mails from Access using Outlook.

Good luck and I’ll be monitoring the post, although I doubt there’s much more I can do.
Bob


You can start with the following thread and an excert of the code. Note the loop is not quite the same as I wasn’t aware of “do until RST.eof” when I worked on that. Note that when multiple reports are generated in one pass, you must write code to assign different file names.

thread703-624967


Dim rptdate As String
Dim Path As String
Dim fullpath As String
Dim dbThis As Database
Dim rsetTeams As DAO.Recordset
Dim intCount As Integer

Set dbThis = CurrentDb
Set rsetTeams = dbThis.OpenRecordset("tlkpteam&quot

' Following to include Yesterday's date as part of each snapshot file name.
rptdate = Format((Date - 1), "mm&quot & "-" & Format((Date - 1), "dd&quot & "-" & Format((Date - 1), "yy&quot

' SET PATH FOR SNAPSHOT OUTPUT - NOTE THAT "TEAM" IS PART OF THE RESULTING FILE NAME

' *** SET PATH
Path = "M:\Shareall\Team Reports\All-In-One Reports\TEAM "

' FOLLOWING IS REQUIRED TO SET RecordCount TO THE NUMBER OF RECORDS IN THE DATASET
rsetTeams.MoveLast
rsetTeams.MoveFirst

For intCount = 1 To rsetTeams.RecordCount

' rsetTeams(1) REFERS TO THE FIELD lkupTeam (2ND COLUMN) WHICH CONTAINS TEAM NUMBER (ALPHABETIC)
fullpath = Path & rsetTeams(1) & " " & rptdate & ".snp"
DoCmd.OpenReport "rptTEAM", acPreview, "", "[team]=""" & rsetTeams(1) & """"
DoCmd.OutputTo acReport, "rptTEAM", "SnapshotFormat(*.snp)", fullpath, False, ""
DoCmd.Close acReport, "rptTEAM"

rsetTeams.MoveNext

Next intCount


FOLLOWING IS THE SYNTAX FOR THE OUTPUTTO COMMAND:

DoCmd.OutputTo objecttype[, objectname][, outputformat][, outputfile][, autostart][, templatefile]

The OutputTo method has the following arguments.

Argument Description

objecttype One of the following intrinsic constants:
acOutputDataAccessPage
acOutputForm
acOutputModule
acOutputQuery
acOutputReport
acOutputServerView
acOutputStoredProcedure
acOutputTable

objectname A string expression that's the valid name of an object of the type selected by the objecttype argument. If you want to output the active object, specify the object's type for the objecttype argument and leave this argument blank.
If you run Visual Basic code containing the OutputTo method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.

outputformat One of the following intrinsic constants:
acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS
If you leave this argument blank, Microsoft Access prompts you for the output format.

outputfile A string expression that's the full name, including the path, of the file you want to output the object to.
If you leave this argument blank, Microsoft Access prompts you for an output file name.

autostart Use True (–1) to start the appropriate Microsoft Windows–based application immediately, with the file specified by the outputfile argument loaded. Use False (0) if you don't want to start the application. This argument is ignored for Microsoft Internet Information Server (.htx, .idc) files and Microsoft ActiveX Server (*.asp) files.
If you leave this argument blank, the default (False) is assumed.

templatefile A string expression that's the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.

 

[BobJacksonNCI]

Chris, did you drown?

Kidding! Thought you'd have posted an update by now...

Check thread705-712587 for an example of trigger e-mail for a report snapshot file.

HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[frosty90]

Bob,
Thanks alot for your help with this. I could not get the reports to open each state and then close. I have found a way to open , several reports one right after another by useing you idea. Somewhat modified to fit my needs. I am using
DoCmd.OutputTo acOutputReport, "Query1", acFormatHTML, "Alabama.htm", True, "ANYTHING.HTML
then the second report Docmd.....and so on
This way I get the look I'm going for in the way the report looks. As you stated I could use any format to but outputto in this format allows me to write a html template with our logo. The only problem now is writing 52 seperate reports with the command I wrote above. Since these reports will open one right after another I have come across two new problems. Each report once opened leaves 52 explorer windows. I am working on a vb program that will close this window once opened to run in between each open report. Second is xp defaults to "my documents." I have to tried to set path to no avail. I have researched this I have found that the default is in the system registry. I am working on changing the registry to default to my location and then change back when complete. Any more ideas or suggestions are greatly appreciated.
Thanks again
Chris Frost

 

[BobJacksonNCI]

Chris,

A couple of hints:

DoCmd.OutputTo acOutputReport, "Query1", acFormatHTML, "Alabama.htm", True, "ANYTHING.HTML

"Alabama.htm" CAN contain the full path.


In my second post I use an OpenReport followed by an OutputTO.

The two commands are creating ONE report per team that ends up in report snapshot form. You could use that as an example, instead creating the reports and make them end up in HTML format...

OpenReport includes a Where clause that you could use to specify a state.


I suspect each of your runs is processing for every state in your table. Since you specify Alabama in your example, you probably created an Alabama file 52 times...

The example I gave is used to create a report for each team. Since I put the output snapshots in the same folder, I had to do something so the names aren't the same because otherwise each team report file would have overwritten the previous file. Although you're looking for a different form of output, you still have to do something to either put each file in a separate folder or vary the output name of each.

It appears to me that part of what you want to do is a lot like what I needed to do, substituting states for teams.

Good luck!
Bob

 

[frosty90]

Bob,
Thanks for all your help I really appreiciate it. The reports are all priniting and saving as html. It is working like I need it to. Saves me and the user alot of time. You have helped me alot with this again I thank you.
Chris Frost