Report not printing all groups

[Hayduke]

I have an Access mdb which has a user form with 3 inputs and a button.
After the user chooses the values for the inputs and clicks the
button, I have VBA which connects to an MSSQL database, and executes a
stored procedure using the values from the Access user form. The MSSQL
stored procedure populates a table in the MSSQL database; that table
is also referenced in an MSSQL view (the view is simply a SELECT *
from the table). Those pieces all work fine, and I've confirmed that
the MSSQL table is repopulated correctly via Access.

After the Access VBA executes the stored procedure, the VBA then uses
DoCmd.OpenReport to preview a report. The recordset for the report is
based on an Access linked table, which is the MSSQL view mentioned
above. The report has 2 groupings, based on 2 columns in the view/
linked table.

The problem I have is that everything runs fine and the report is
beautiful, except that the report sometimes does not display all the
groups (and corresponding detail records) which are in the view/linked
table.

This happens maybe 70% of the time the report is run. Clicking Design
for the report, and then clicking Print Preview often--but not always--
fixes the problem.

A couple other details:
1. The report groups which are not printing are always the last
groups from the report. So, for example, if 5 groups should print,
only the first 2 or 3 group will actually print.
2. I've used the Me.Requery and DoCmd.Requery to see if either of
those fixed the report, but neither have fixed the problem.

Does anyone have any ideas as to what's causing some groups not to
print?

 

[Hayduke]

The problem was caused because SQL Svr had not finished populating the
table by the time the Access report opened. It takes time to write to
disk, but Access does not know to wait before it opens the report.

There is an indirect way to handle this. Create a MODULE called, say,
modSleep, and put in it:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As
Long)
'This creates, once the moduel is compiled, user-defined function
sleep(ms)
'where MS is number of milliseconds to wait before executing the next
step--
'ususally a couple hundred ms is enough to finish a query, but
experiment

See also: http://database.ittoolbox.com/group...access-report-not-printing-all-groups-2203294