Auto Print Report for only Latest Record on Add New Record Event??

[Jon-G]

Hi All,
I am looking for some help with a project I am working on where I need to automatically print a report from my database every time a new record is added to the table. The table contains 13 fields and the report needs to display 12 of them, the other being the index which is set to Autonumber.

The table is being updated solely by ODBC, this is working OK.

There is the potential for records to be added to the table very quickly via the ODBC link, so I need to safeguard that the report is being populated with correct information from the record that triggered the print event. Also, should multiple records be added in close succession, a report needs to be correctly generated/printed for each of one. The DB is to have no user intervention, and will just run on the PC at startup with all access menus/controls locked out.

Any information on how you think I should structure this, or any examples of helpful code that you might have would be very much appreciated.


Thanks in advance,
Jon.

 

[Pat Hartman]

Access (Jet) doesn't support triggers so there is no way to automatically print each record as it is added. The best you can do is to print all the records since the last time the print routine ran. You don't need to actually print a separate report for each item, you can add page breaks and even reset the page counter so it doesn't matter how many items get printed as part of the same report. The all look like individual reports.

I would add a table that records the autonumber of the last printed record and update it in the report footer of the report. The criteria for the report's query would select all rows with autonumbers > the last printed autonumber.

 

[Jon-G]

Access (Jet) doesn't support triggers so there is no way to automatically print each record as it is added. The best you can do is to print all the records since the last time the print routine ran. You don't need to actually print a separate report for each item, you can add page breaks and even reset the page counter so it doesn't matter how many items get printed as part of the same report. The all look like individual reports.

I would add a table that records the autonumber of the last printed record and update it in the report footer of the report. The criteria for the report's query would select all rows with autonumbers > the last printed autonumber.

Hi Pat,
Thanks for the prompt reply however you lost me a little

In the scenario above what would be invoking the print routine, and where would this code sit? Would this be driven from a timer?
Do you have any example code to hand to help me better understand how this would tie together?

How would you anticipate a database structed as described to behave if there were, for example, 10 records added to the database within the same second?

I suspect that the table will grow very quickly when the database is commisioned. Do you think that the size of the table might eventually slow the execution time of the query bound to the report you mentioned down beyond acceptable?

Thanks again,
Jon.

 

[Pat Hartman]

Let's back up a minute. At 10 records a second, your printer is never going to keep up. Even the fastest printers take a couple of seconds to print a page. Why are you printing each record?

 

[Jon-G]

The reports will be printed to a virtual postscript printer that is installed on the same machine. 10 reports per second is a worst case scenario that I must factor in, as this is the maximum number of records I estimate could be passsed to the table via the ODBC link at peak. Generally the loading will be very little.

The data from the print job will be captured by another application directly from the print spooler and archived as well as automatically printing a copy for the customer.

The above cant be changed its just a matter of if I can use access as a means to an end, and if so how

Jon

 

[Pat Hartman]

Unless there is some urgent need to pass the data immediately, I would set up an Access db and have it started several times a day by windows scheduler. The start up form would run the report that prints to the spooler. Use the table I mentioned earlier to keep track of the most recent record printed.

The alternative method is to use a timer event on a form. However, this requires that the Access database remain open permanently or at least for whatever part of a day that the records will be created. I think my previous suggestion will prove to be more stable.

 

[Jon-G]

Unless there is some urgent need to pass the data immediately, I would set up an Access db and have it started several times a day by windows scheduler. The start up form would run the report that prints to the spooler. Use the table I mentioned earlier to keep track of the most recent record printed.

The alternative method is to use a timer event on a form. However, this requires that the Access database remain open permanently or at least for whatever part of a day that the records will be created. I think my previous suggestion will prove to be more stable.

The report will be need to be generated as soon as the record its added to the table so I would need to have the DB open at all times. Do you think that this would cause a problem with stability then?

My VB knowledge is rather limited so if anyone has any pointers on how to structure the code Id appreciate it

 

[Pat Hartman]

The report will be need to be generated as soon as the record its added to the table so I would need to have the DB open at all times. Do you think that this would cause a problem with stability then?

Yes.

You will need to use the form with the timer event as I suggested. Since 24 x 7 is not a reality, you also have to take into consiteration the possibility of a down period so that when the .mdb reopens, it picks up any not previously reported data and that means you will need to use the last printed table as I suggested.