Well darby..…I’m going to brave this post simply because no one is biting on it.
Your suspicions are correct. There is a way. There are lots of ways. The trouble is, you will need to know something about VB or VBA programming otherwise you are going to be completely lost with what you will be looking at….the required code to do it. Unless one of these fine people that view this forum is willing to attach a DB with the sample code you need to accomplish the task, your post will turn into a small book.
A front end unit (in any programming language) can be written to even utilize the MS Windows Scheduler or Outlook and through Windows Automation email your reports. Or it could be done directly through Access providing your DB it is up and running all the time. There are a lot of different variables to play with here.
Just as a simple example this would be how you could Output Multiple Reports to One Word File Automatically.
Let’s say you have a list of reports you have to send to a customer or a specific individual. This has to be done every so often and takes too long. As well as that the reports need to be in word format, which takes even more time, and there are 50 of them.
Create a new form. On this form put a list box called LIST0. Either fill the list box via code (see List all Reports example) or from a table via a query. Create a button called SENDIT. Go into the code for the form by clicking the code button on the toolbar and paste the following code.
Private Sub SENDIT_Click()
On Error GoTo ERRORHANDLER
Dim varItem As Variant
If List0.ItemsSelected.Count>0 Then
Dim AppWord As New Word.Application
Dim DocWrd As Word.Document
Dim i As Integer
Dim Progress as String
Dim EventTitle as String
AppWord.Visible = True
Set DocWrd= AppWord.Documents.Add
DocWrd.PageSetup.TopMargin = 36
DocWrd.PageSetup.BottomMargin = 36
DocWrd.PageSetup.LeftMargin = 36
DocWrd.PageSetup.RightMargin = 18
i = 0
For Each varItem In List0.ItemsSelected
i = i + 1
Progress = "Processing... " & List0.ItemData(varItem)
DoCmd.OutputTo acOutputReport, List0.Column(0, varItem), acFormatRTF, "c:\temp\" & List0.ItemData(varItem) & ".rtf", False
AppWord.Selection.InsertFile "c:\temp\" & List0.ItemData(varItem) & ".rtf", "", False, False, False
If i < List0.ItemsSelected.Count Then
Progress = "Generating Email"
DocWrd.SaveAs "c:\temp\" & EventTitle & ".doc", wdFormatDocument
DocWrd.BuiltInDocumentProperties("Title").Value ="Set the title of the combined reports - " & Date
AppWord.Options.SendMailAttach = True
Set DocWrd = Nothing
Set AppWord = Nothing
Progress = ""
Progress = ""
If MsgBox("Do you want to start over?", vbCritical + vbYesNo) = vbYes Then
Save the form and run it. Select the reports you want to send in the list and click the button. All the reports will be run and joined into one nice long word document. Then bring up your mail client with the document attached.
List all Reports example
This piece of code shows you how to fill a combo box with a list of reports within the current database.
Create a form and put an unbound combobox on the from and call it ReportList. Then put the following code in the form open event.
Private Sub Form_Open(Cancel As Integer)
Dim Dbs As Database
Dim Rpc As Container
Dim Rpt As Document
Set Dbs = CurrentDb
Set Rpc = Dbs.Containers!Reports
For Each Rpt In Rpc.Documents
ReportList.RowSource = ReportList.RowSource & Rpt.Name & ";"
Set Dbs = Nothing
Set Rpc = Nothing
Set Rpt = Nothing
After the form loads you'll have a combobox with a list of the reports available. Once a user has selected a report you could have a button which opens the report or prints it maybe. To find out the report name just use Me.[ReportList].Value
Alternatively if you want to be a little smarter. Just put a listbox on a form and set it's rowsource to :-
SELECT Name FROM msysobjects WHERE (((msysobjects.Type)=-32764));
If you don’t know programming, you can now quickly see how cryptic things can be. In any case, you can rest assured, nothing is impossible. Besides…now that I’ve posted this, you can bet someone has something better to offer as a post.
I achieve this by using the scheduler on out W2K server. I create a version of the database that runs a macro that uses the sendobject function and then closes the database. You just need to ensure that you have a configure email client on the server. Just set the scheduler to run the mdb file at whatever times you want.
You can get clever and check for the existence of a chk file on the machin, or look up the username to decide whether to run the sendobject macro in the autoexec. That way you only have to maintain one version of the database.
I was trying to do the same thing, I managed to find a database someone create with a clock that runs in real time, what I did was to add a feature to the on timer event that runs the reports or another program etc. might be worth a look. I'm sure this can be tweaked to suit any application.
I have attached the clock file. You may as well have a look.