Reports not flushing

[Niall]

I have a small problem - have an MS Access (2003) front end into a SQL Server
backend, and when we run reports for all clients, we are finding that
intermittently client A's details are displayed on client B's report.

Basically, we are retrieiving a list of clients from our sql server backend,
and for each row in the recordset, opening a report, writing to PDF and
saving the file, closing the report, and rs.MoveNext until rs.EOF

In the actual report, we have a variable g_clientNo, which displays the
current client number taken from the recordset. What we are finding is that
g_clientNo will display Client B, but will display the subreport for client
B.

My suspicions are that this behaviour is similar to that displayed in
http://support.microsoft.com/default.aspx/kb/883867/en-us (BUG: A control on
a form or on a report that refers to a control on a subform or on a subreport
is blank in Access 2003). I have currently raised a support call with MS
Australia, to no avail.

The process has been running for a few years here, however this has been the
first time this behaviour has been observed. The only thing that has
changed, is that we are now on faster computers.

In the event that this occurs, the company would prefer a blank report to
display, rather than one with another client's details - for obvious reasons!


Is it possible to do something like flush the report?

Further info - it doesn't happen to every report in the sequence, but when
it does, it is always the prior client's info that is in the other client's
report.

We have a number of different reports - it is not restricted to any one of
them, therefore we can (hopefully) exclude the code as being at fault
(especially as its been running for a number of years without problems).
Currently we're getting around 9 reports out of about 2500 that have
displayed this problem. (see what I mean about intermittent!!!)

Hopefully you gurus can give me some ideas please.

Cheers,
Niall

 

[Allen Browne]

Niall, the first issue would be whether you have split the application, and
given each user a different front end (i.e. not all opening the same front
end.) Given that you are using a global variable to do this, this would be
essential.

If splitting is a new concept, see:
http://allenbrowne.com/ser-01.html

There is a bug in Access (all versions before 2007) where it misapplies the
Filter of a report where there are multiple instances open. Again, this
should not occur if each user has their own copy of the front end, unless
you are explicitly opening multiple instances with the New keyword.

Another possibility is that the report is being opened from a form where the
current record is dirty. It's worth explicitly saving anyway:
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport ...

It should not be the issue in the kb article you linked to: that was
supposedly fixed long ago (SP1 for Office 2003.)

 

[Niall]

Thanks Allen - all users have their own copy of the MS Access 2003 front end
- working into a SQL Server 2000 back end.

This application has been working (reasonably correctly) for a few years
now, and this is 'new behaviour'. We are a new team, as the old team jumped
ship, however the only change we have been able to identify, is that the
users are all using faster computers now. No changes have been identified
to the code, other than a "sleep 100" has been commented out (that was
between the openreport and the closereport.

The 'merging' of the data has happened approx 8 times in over 20000 reports
- ie not very often, however in our business, once is more than enough!

Code we are using is basically

Get recordset from sql server
for each row
call code below
movenext


code:

Call WriteToLog("C:\STM\SaveImage.log", strTempFile, True, False)
If Not app_is_running("PremSaveImage") Then
SendKeys strTempFile
SendKeys "~" ' send enter
End If

'start-up the print dialog. The sendkeys stacked up above will then flow
into the dialog box


DoCmd.OpenReport strReport, acViewNormal
' sleep_seconds (1)

DoCmd.Close acReport, strReport

Application.Printer = Application.Printers(strPrinter)
create_print_file = strTempFile

 

[Allen Browne]

Hmm: There's plenty scope for problems there.

I don't see a need for a global variable here: it would certainly be worth
passing a private variable for the customer number, so there is no chance of
it being overwritten.

Stuffing characters into the message queue (SendKeys) is always a last-ditch
approach. I can't see what dialog box those keystrokes are targeted at, but
if you are trying to suppress the "Printing" dialog, this might help:
http://www.mvps.org/access/api/api0037.htm

It's also unclear why you are setting the Printer repeatedly rather then
before the entire run. If this is to create a PDF, you may be able to do it
this way:
http://www.lebans.com/reporttopdf.htm

If the output is just text, it may be possible to avoid the entire problem
by using TransferText or OutputTo.

It's not clear why the Sleep 1 was needed (because this calls several other
routines, and there could be more in the module of the report.)

If the new, faster machines are dual core, again there is the possibility of
parallel processing (esp. with stuffing the message queue.)

Because it happens rarely, there could still be the possibility that some
user is opening the same front end as another instead of their own.

Not sure what else to suggest as possible approaches, workarounds, or
debugging techniques. It looks like you are writing a log at some point, so
perhaps your logs may shed light on what is unique to the cases where it
occurs.

 

[Niall]

Thanks Allen,
The code was written sometime around Noah and his Ark - the sendkeys is to
provide a unique filename for the pdf995 program to write the pdf image of
the report. I'm currently working on using Distiller to create the pdf and
a different system (not sendkeys/outputto etc) to save the report.

I'm not entirely sure why its setting the printer object so many times -
haven't had a chance to work that one out yet. The sleep 1 is commented out
- I'm also not sure why it was in there in the first place.

My major problem is that substance of the reports contains client financial
details, and if (as has happened) one clients details get sent to another
client, my company is liable for a rather large fine, therefore not only do I
have to develop a better way of creating these reports, but I also have to
explain to the Board why this happened and what I have done to prevent it
from happening in the future - this is why it is so important for me to
diagnose what the problem is.

Latest from MS Support is "can you reproduce the problem in Northwind" -
well, I fail to see how that is relevant - we're using MS Access purely as a
front end and reporting tool, into a SQL Server backend. I have managed to
identify that the report is already 'merged' at the save, and the only code
prior to that is the

DoCmd.OpenReport strReport, acViewNormal

DoCmd.Close acReport, strReport

The global variable for ClientNo is basically used because it is required in
a number of different modules and forms. Not the best approach, however
there is just so much to change in this application that I haven't got around
to it yet.

My belief (as yet unproven) is that for some reason the recordsource of the
SUBREPORT has not filled the bound fields, and they are still bound to the
previous report/subreport combination. If we run the report a second time,
there is no problem, or the problem will recur but in a different report.

Every user has their own copy of the front end on their local machine - I
don't see any way that more than one user can be using that particular front
end at the same time.

The log doesn't shed any light on it either unfortunately.

I wonder if there is any way I can match up the global variable (in the
report) with something in the subreport as a sanity check?

I'm not sure that I explained exactly how the process works earlier, so I'll
give it a go now - it may help someone to diagnose what's going on.

We have a number of reports, such as "Client Header Page", "Portfolio
Valuation", "Income Report" etc etc. These are what is known as a "Report
Pack", and the user has the choice of either producing all reports in the
report pack for a particular client (which does NOT seem to display the
problem but takes a significant amount of time and user input), or produce a
particular report for all clients (which is where the problem seems to
occur). Each of these report filenames is stored in the sql server
database, and at a later date, another process collects all the report
filenames for a particular client and merges the files into a single file for
printing. The problem has already occured prior to the 'merge reports for
printing' stage.

Thanks to everyone (especially Allen) for trying to help with this - any
ideas will most certainly be appreciated.
Niall

 

[Allen Browne]

To match the variable with the report, perhaps you could create a logging
table, and use some events to write entries there.

For example, you might write a log record when the user clicks the button to
open the report and when the Format event of the report Header fires. Record
the value of the global variable, date/time of Now(), and the user and/or
computer name:
http://www.mvps.org/access/api/api0008.htm
http://www.mvps.org/access/api/api0009.htm