Automatically run a report

[MrMcFestoe]

Hi,

I have a customers table, and in that i have three fields which i need to know if at any time they change, so i have a report that looks at these fields and printouts out the data.

What i want if possible, if any of the three fields change the report runs automatically.

is this possible.

 

[scriverb]

I have done such a thing by creating an invisible form that opens in addition to your MainMenu form. The OnTimer event procedure monitors the values and triggers the opening of the report.

Before we decide upon exactly how to do it for you I need to know if this application is a server based system. Do you multiple users with a FrontEnd(FE) application database linked through your network to a common shared BackEnd(BE) database?

Please explain your setup and we can make some decisions and get this to work for you.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MrMcFestoe]

My bd is based on a pc which has the hard drive set up for sharing, it is not split into a frontend or backend yet, that is my next big adventure when iam happy with the way my db works.

The way you have suggested it above, with the OnTimer function is this going to run the report after a time elapsed, i was going to try and make it run if any on of three fields had changed value.

I hope this makes sense.




Life is made on the decisions you make not he chances we take.

 

[scriverb]

No the OnTimer event just triggers code on a preset interval that will make the comparison of your data to the previous data stored. If changed then it will execute the OpenReport command.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MrMcFestoe]

Do i just put a OpenReport(name) in the OnTimer, i though the OnTimer only worked when a form was open, i need this to run on itss own if any changes are made.

Iam on the right track?

Life is made on the decisions you make not the chances we take.

 

[scriverb]

The only way to execute VBA code in a program is to have the database open and a form open so that the OnTimer Event Procedure will run. Now I have done this through the use of an invisible form that open upon opening the database and stays open the entire time the database is actively running. This form is never seen but the OnTimer event is setup to execute on a schedule and check to see if data is changed. This is the only way to accomplish this easily. Your user never knows that the form is opened. The switchboard or mainmenu doesn't have to stay active and your code processes run on there own in the background.

The only other way to do this is to have a database that's only function is to run and check the database for changes to the data and run the report if changes have occurred. The schedule for this database would be setup using the operating systems Scheduler. You could have it scheduled to run every hour or half hour to check the database. Then it would close automatically. I have not personally used this process but I know that others here at TT have posted details on how to use the scheduler. If you chose to do it this way I can direct you to the other postings.

But, personally I think the first option will work well for you. I can help you set it up and to make it work in your situation.

Just let me know.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MrMcFestoe]

Bob Scriver

going off your post your idea seems to be the best because its all kept in access and no other programs are required, so i create a form (invisible)

with the three fields i am monitoring on it ?

or is the invisible form just to run a code?

This is new to me so please bear with me on this.

Thanks

Life is made on the decisions you make not the chances we take.

 

[scriverb]

We can take this a step at a time. Create an form that has no controls on it. You can put a label with a description of its use later if you wish. Just a blank form, unbound, and we can add the code necessary later.

Just make it an inch or so square and postiion it in the upper left and save it. Let's call it frmTimer. We will open it Hidden upon opening the database.

Now are you using a switchboard or a form setup as your main menu? Are you using an AutoExec?

I usually use a custom main menu and use the AutoExec module to startup my database.

My AutoExec looks like this:
Action
RunCommand Cmd: WindowHide 'This hides the DB window
OpenForm Frm: frmTimer View: Form Window Mode: Hidden
OpenForm Frm: frmMainMenu View: Form Window Mode: Normal


Now before we continue I need to know if your are going to have multiple users updating the tables of this database. Are they linked to your database which is set for shared useage? If so then you have to have your database applications split between FE and BE. The other users must have a copy of the application with the tables and are linked to them on your PC. Otherwise they would be updating a seperate set of data tables. Please update me on the exact setup that you have. I am recommending that if you want multiple users to use this database that we walk through the process of creating a BE and removing the tables from the FE application and set up a linked table arrangement. This is not difficult and I can walk you through the process.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MrMcFestoe]

Not using a autoexec at the moment, using a switchboard this db has been kept simple up to now and has grown in to a monster.

The multiple user's not at the moment but it would be nice if i expand.

Life is made on the decisions you make not the chances we take.

 

[scriverb]

Okay, let's use your Switchboard. Open the Switchboard in Design mode. Open the forms properties window and select OnOpen event procedure. In this event procedure just before the End Sub, put the following VBA code:

DoCmd.OpenForm frmTimer, , , , , acHidden

This will open our Hidden form frmTimer upon opening the Switchboard.

We can address your FE and BE situation after we get this working. Create a new table called tblDBParameters. We are only going to create one record in this table with as many field as you need to store variable and parameter data. It is here where we will store the latest updated values for the fields that you are concerned about monitoring.

Create the following fields:
RecCounter AutoNumber

Now create one field for each of the target fields that we want to monitor from your other table. You can copy and paste them in from the other table and call them by the same names. that is okay. Just post back as to what the names are so I can include them in the code for the OnTimer event.

Save and name this table tblDBParameters Open the table and create one record by putting in some dummy data in the fields. Just one record now.

Post back with the other field names and the name of the table that we will be monitoring. Also, the name of the report that you want run when we find the updates.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MrMcFestoe]

Sorry its taken abit of time to post but my dad has taken ill at the moment.

the fields to be monitored are in a table called Table 1 (original i know) the fields are Level ONE, Level TWO, Withdrawn these need to be monitored and if any one changes then a report (Police Level URN2) is run which prints out the changed information.

Iam not checked my emails or response's everyday at the moment so please bear with me and i will get back to you.

Thanks