Report not visible soon enough

[Kari]

I am trying to keep track of which records have been printed. I have added a
yes/no field to my table, and have an update query that sets the field to
“yes†after printing. I then added code to look for old, unprinted records
(sometimes the paperwork gets held up overseas and the record is added late).
So far, so good.

At this point what I would like to do is to open the report with the old
records (each report covers a week of data) and ask the user if they want to
print it. Since you cannot open multiple instances of a report reliably (as
per Allen Browne elsewhere on these boards), I need each report to open, then
the dialog (“Do you want to print this report?â€) to open. What is happening
is that the report is not visible when the dialog opens, and does not become
visible until the last dialog is closed (for testing I have about 10 weeks of
old data, therefore 10 separate reports, each with a dialog box). I have
tried Repainting, Setting Focus, Maximizing and Selecting the Report
(DoCmd.SelectObject) after opening, but nothing works.
I have run out of ideas, and I can’t find anything on the boards that is
similar.

Any help, including a different approach, would be most appreciated!

I am using Access 2007 on WIndows XP

Details follow (slightly simplified code):

The following code is behind the OnCLick event of a button on a form used to
select the date:
--------------------
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim Response2 As Single

'Open recordset of "old" shipped orders that have not been printed:
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("Ship Last Week Old Samples Weeks Query")
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Do While Not rst.EOF
OpenReportToWeekShipped (rst![WeekShipped])
Response2 = MsgBox("Print Report?", vbYesNo) 'if this is put
directly in the "IF" statment it always returns a 0
If Response2 = vbYes Then 'print report
DoCmd.PrintOut acPrintAll
End If 'if report is to be printed

rst.MoveNext
Loop 'if not rst.EOF

Function OpenReportToWeekShipped(dtWeekToPrint As Date)
Dim stWhereClause

stWhereClause = "[WeekShipped] = #" & dtWeekToPrint & "#"
On Error Resume Next 'If the OpenReport action is cancelled (due
to no data) an error is generated
DoCmd.OpenReport "Ship Last Week Samples Report", acViewReport, ,
stWhereClause

End Function

 

[Kari]

To Everyone Out There:

Please take pity on me and help me out. I will be out of the office until
Friday, but I will check back then for answers.

If anyone can recommend a better approach, I would appreciate that as well.

Thanks in advance.

Kari

 

[Klatuu]

There is no event for a report that will fire after the report has completed
the preview. So I would suggest controling from a form.

The best way to handle this is by using a form to control the printing of
the report. For testing purposes, what I did was create a form with a
command button "Preview Report". This button presents the report in Preview.

Then I use an option group to either print the report or not. In either
case, it closes the report. Here is the code necessary to do that:

Private Sub cmdFirstReport_Click()
DoCmd.OpenReport "rptClientList", acViewPreview
End Sub


Private Sub opgPrintOne_Click()
DoCmd.Close acReport, "rptClientList"
If Me.opgPrintOne = 1 Then
DoCmd.OpenReport "rptClientList", acViewNormal
End If

End Sub

The next issue which report to select next. You can use this as a basis and
expand on it to get the report you want.
--
Dave Hargis, Microsoft Access MVP

I am trying to keep track of which records have been printed. I have added a
yes/no field to my table, and have an update query that sets the field to
“yes†after printing. I then added code to look for old, unprinted records
(sometimes the paperwork gets held up overseas and the record is added late).
So far, so good.

At this point what I would like to do is to open the report with the old
records (each report covers a week of data) and ask the user if they want to
print it. Since you cannot open multiple instances of a report reliably (as
per Allen Browne elsewhere on these boards), I need each report to open, then
the dialog (“Do you want to print this report?â€) to open. What is happening
is that the report is not visible when the dialog opens, and does not become
visible until the last dialog is closed (for testing I have about 10 weeks of
old data, therefore 10 separate reports, each with a dialog box). I have
tried Repainting, Setting Focus, Maximizing and Selecting the Report
(DoCmd.SelectObject) after opening, but nothing works.
I have run out of ideas, and I can’t find anything on the boards that is
similar.

Any help, including a different approach, would be most appreciated!

I am using Access 2007 on WIndows XP

Details follow (slightly simplified code):

The following code is behind the OnCLick event of a button on a form used to
select the date:
--------------------
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim Response2 As Single

'Open recordset of "old" shipped orders that have not been printed:
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("Ship Last Week Old Samples Weeks Query")
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Do While Not rst.EOF
OpenReportToWeekShipped (rst![WeekShipped])
Response2 = MsgBox("Print Report?", vbYesNo) 'if this is put
directly in the "IF" statment it always returns a 0
If Response2 = vbYes Then 'print report
DoCmd.PrintOut acPrintAll
End If 'if report is to be printed

rst.MoveNext
Loop 'if not rst.EOF

Function OpenReportToWeekShipped(dtWeekToPrint As Date)
Dim stWhereClause

stWhereClause = "[WeekShipped] = #" & dtWeekToPrint & "#"
On Error Resume Next 'If the OpenReport action is cancelled (due
to no data) an error is generated
DoCmd.OpenReport "Ship Last Week Samples Report", acViewReport, ,
stWhereClause

End Function

 

[Kari]

Dave,

Thanks for your reply. What you're suggesting is essentially what I've got
set up (I'm not sure I made that clear in the original post). I have a form
that allows the user to enter the date and choose one of several reports.
For the report in question, the button triggers code that checks for older
records that have not yet been printed. If there are any such records I want
each report to open in Report mode (not preview) and ask if it should be
printed (via a pop-up dialog box). The problem is that the report doesn't
fully open (become visible on the screen) before the "Print Report?" dialog
box appears. The opening of both the report and the dialog box are triggered
from the code behind the button on the form. I hope that clarifies the
matter.

Just to see if it would make a difference I switched the view to Preview
instead of Report on the OpenReport command line, but it still behaves the
same, not showing the Preview until all 10 "Print Report?" dialogs have been
closed. Now that I think about it, Preview would work fine for my situation,
if I could get everything else to work. (I typically use Report view so that
I can put a "Print" button (and others as needed) directly on the report. I
think this provides a cleaner interface than having to go back to a separate
form to print. I don't think this approach would have worked before Access
2007.)

I also noticed in your sample code that you close the Preview and then
reopen it to print it. Isn't there a way to use VB to print a report that's
already open in Preview mode? Just curiouis. . . . .

Thanks again for your help,

Kari

P.S. Short day today, and then out of office until Tuesday, so please don't
take it personally if I don't respond right away.

There is no event for a report that will fire after the report has completed
the preview. So I would suggest controling from a form.

The best way to handle this is by using a form to control the printing of
the report. For testing purposes, what I did was create a form with a
command button "Preview Report". This button presents the report in Preview.

Then I use an option group to either print the report or not. In either
case, it closes the report. Here is the code necessary to do that:

Private Sub cmdFirstReport_Click()
DoCmd.OpenReport "rptClientList", acViewPreview
End Sub


Private Sub opgPrintOne_Click()
DoCmd.Close acReport, "rptClientList"
If Me.opgPrintOne = 1 Then
DoCmd.OpenReport "rptClientList", acViewNormal
End If

End Sub

The next issue which report to select next. You can use this as a basis and
expand on it to get the report you want.
--
Dave Hargis, Microsoft Access MVP

I am trying to keep track of which records have been printed. I have added a
yes/no field to my table, and have an update query that sets the field to
“yes†after printing. I then added code to look for old, unprinted records
(sometimes the paperwork gets held up overseas and the record is added late).
So far, so good.

At this point what I would like to do is to open the report with the old
records (each report covers a week of data) and ask the user if they want to
print it. Since you cannot open multiple instances of a report reliably (as
per Allen Browne elsewhere on these boards), I need each report to open, then
the dialog (“Do you want to print this report?â€) to open. What is happening
is that the report is not visible when the dialog opens, and does not become
visible until the last dialog is closed (for testing I have about 10 weeks of
old data, therefore 10 separate reports, each with a dialog box). I have
tried Repainting, Setting Focus, Maximizing and Selecting the Report
(DoCmd.SelectObject) after opening, but nothing works.
I have run out of ideas, and I can’t find anything on the boards that is
similar.

Any help, including a different approach, would be most appreciated!

I am using Access 2007 on WIndows XP

Details follow (slightly simplified code):

The following code is behind the OnCLick event of a button on a form used to
select the date:
--------------------
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim Response2 As Single

'Open recordset of "old" shipped orders that have not been printed:
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("Ship Last Week Old Samples Weeks Query")
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Do While Not rst.EOF
OpenReportToWeekShipped (rst![WeekShipped])
Response2 = MsgBox("Print Report?", vbYesNo) 'if this is put
directly in the "IF" statment it always returns a 0
If Response2 = vbYes Then 'print report
DoCmd.PrintOut acPrintAll
End If 'if report is to be printed

rst.MoveNext
Loop 'if not rst.EOF

Function OpenReportToWeekShipped(dtWeekToPrint As Date)
Dim stWhereClause

stWhereClause = "[WeekShipped] = #" & dtWeekToPrint & "#"
On Error Resume Next 'If the OpenReport action is cancelled (due
to no data) an error is generated
DoCmd.OpenReport "Ship Last Week Samples Report", acViewReport, ,
stWhereClause

End Function

 

[Klatuu]

There is no way in VBA to print a report that is already open.
--
Dave Hargis, Microsoft Access MVP

Dave,

Thanks for your reply. What you're suggesting is essentially what I've got
set up (I'm not sure I made that clear in the original post). I have a form
that allows the user to enter the date and choose one of several reports.
For the report in question, the button triggers code that checks for older
records that have not yet been printed. If there are any such records I want
each report to open in Report mode (not preview) and ask if it should be
printed (via a pop-up dialog box). The problem is that the report doesn't
fully open (become visible on the screen) before the "Print Report?" dialog
box appears. The opening of both the report and the dialog box are triggered
from the code behind the button on the form. I hope that clarifies the
matter.

Just to see if it would make a difference I switched the view to Preview
instead of Report on the OpenReport command line, but it still behaves the
same, not showing the Preview until all 10 "Print Report?" dialogs have been
closed. Now that I think about it, Preview would work fine for my situation,
if I could get everything else to work. (I typically use Report view so that
I can put a "Print" button (and others as needed) directly on the report. I
think this provides a cleaner interface than having to go back to a separate
form to print. I don't think this approach would have worked before Access
2007.)

I also noticed in your sample code that you close the Preview and then
reopen it to print it. Isn't there a way to use VB to print a report that's
already open in Preview mode? Just curiouis. . . . .

Thanks again for your help,

Kari

P.S. Short day today, and then out of office until Tuesday, so please don't
take it personally if I don't respond right away.

There is no event for a report that will fire after the report has completed
the preview. So I would suggest controling from a form.

The best way to handle this is by using a form to control the printing of
the report. For testing purposes, what I did was create a form with a
command button "Preview Report". This button presents the report in Preview.

Then I use an option group to either print the report or not. In either
case, it closes the report. Here is the code necessary to do that:

Private Sub cmdFirstReport_Click()
DoCmd.OpenReport "rptClientList", acViewPreview
End Sub


Private Sub opgPrintOne_Click()
DoCmd.Close acReport, "rptClientList"
If Me.opgPrintOne = 1 Then
DoCmd.OpenReport "rptClientList", acViewNormal
End If

End Sub

The next issue which report to select next. You can use this as a basis and
expand on it to get the report you want.
--
Dave Hargis, Microsoft Access MVP

I am trying to keep track of which records have been printed. I have added a
yes/no field to my table, and have an update query that sets the field to
“yes†after printing. I then added code to look for old, unprinted records
(sometimes the paperwork gets held up overseas and the record is added late).
So far, so good.

At this point what I would like to do is to open the report with the old
records (each report covers a week of data) and ask the user if they want to
print it. Since you cannot open multiple instances of a report reliably (as
per Allen Browne elsewhere on these boards), I need each report to open, then
the dialog (“Do you want to print this report?â€) to open. What is happening
is that the report is not visible when the dialog opens, and does not become
visible until the last dialog is closed (for testing I have about 10 weeks of
old data, therefore 10 separate reports, each with a dialog box). I have
tried Repainting, Setting Focus, Maximizing and Selecting the Report
(DoCmd.SelectObject) after opening, but nothing works.
I have run out of ideas, and I can’t find anything on the boards that is
similar.

Any help, including a different approach, would be most appreciated!

I am using Access 2007 on WIndows XP

Details follow (slightly simplified code):

The following code is behind the OnCLick event of a button on a form used to
select the date:
--------------------
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim Response2 As Single

'Open recordset of "old" shipped orders that have not been printed:
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("Ship Last Week Old Samples Weeks Query")
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Do While Not rst.EOF
OpenReportToWeekShipped (rst![WeekShipped])
Response2 = MsgBox("Print Report?", vbYesNo) 'if this is put
directly in the "IF" statment it always returns a 0
If Response2 = vbYes Then 'print report
DoCmd.PrintOut acPrintAll
End If 'if report is to be printed

rst.MoveNext
Loop 'if not rst.EOF

Function OpenReportToWeekShipped(dtWeekToPrint As Date)
Dim stWhereClause

stWhereClause = "[WeekShipped] = #" & dtWeekToPrint & "#"
On Error Resume Next 'If the OpenReport action is cancelled (due
to no data) an error is generated
DoCmd.OpenReport "Ship Last Week Samples Report", acViewReport, ,
stWhereClause

End Function

 

[Kari]

Dave,

Thank you for your help and for satisfying my curiousity (I will keep that
VBA quirk in mind for the future).

And, Oh boy, do I have egg on my face! I finally figured out what my
problem was. I can't believe I missed it, but I had Echo turned off while
this section of code was running. Do I feel dumb! The code was originally
written in a Macro and it only opened the current week's report. I'm in the
process of reworking it in VB to check for old, unprinted records and I
didn't notice that Echo was turned off. Just shows how dangerous a little
knowledge is.

One interesting thing I learned from this is that even with Echo off,
Message Boxes still appear on the screen. Good to know. (Of course if they
didn't I would have caught my mistake earlier! :-(

Thank you for all your help, and I'm really sorry that I wasted your time.

Kari

There is no way in VBA to print a report that is already open.
--
Dave Hargis, Microsoft Access MVP

Dave,

Thanks for your reply. What you're suggesting is essentially what I've got
set up (I'm not sure I made that clear in the original post). I have a form
that allows the user to enter the date and choose one of several reports.
For the report in question, the button triggers code that checks for older
records that have not yet been printed. If there are any such records I want
each report to open in Report mode (not preview) and ask if it should be
printed (via a pop-up dialog box). The problem is that the report doesn't
fully open (become visible on the screen) before the "Print Report?" dialog
box appears. The opening of both the report and the dialog box are triggered
from the code behind the button on the form. I hope that clarifies the
matter.

Just to see if it would make a difference I switched the view to Preview
instead of Report on the OpenReport command line, but it still behaves the
same, not showing the Preview until all 10 "Print Report?" dialogs have been
closed. Now that I think about it, Preview would work fine for my situation,
if I could get everything else to work. (I typically use Report view so that
I can put a "Print" button (and others as needed) directly on the report. I
think this provides a cleaner interface than having to go back to a separate
form to print. I don't think this approach would have worked before Access
2007.)

I also noticed in your sample code that you close the Preview and then
reopen it to print it. Isn't there a way to use VB to print a report that's
already open in Preview mode? Just curiouis. . . . .

Thanks again for your help,

Kari

P.S. Short day today, and then out of office until Tuesday, so please don't
take it personally if I don't respond right away.

There is no event for a report that will fire after the report has completed
the preview. So I would suggest controling from a form.

The best way to handle this is by using a form to control the printing of
the report. For testing purposes, what I did was create a form with a
command button "Preview Report". This button presents the report in Preview.

Then I use an option group to either print the report or not. In either
case, it closes the report. Here is the code necessary to do that:

Private Sub cmdFirstReport_Click()
DoCmd.OpenReport "rptClientList", acViewPreview
End Sub


Private Sub opgPrintOne_Click()
DoCmd.Close acReport, "rptClientList"
If Me.opgPrintOne = 1 Then
DoCmd.OpenReport "rptClientList", acViewNormal
End If

End Sub

The next issue which report to select next. You can use this as a basis and
expand on it to get the report you want.
--
Dave Hargis, Microsoft Access MVP


:

I am trying to keep track of which records have been printed. I have added a
yes/no field to my table, and have an update query that sets the field to
“yes†after printing. I then added code to look for old, unprinted records
(sometimes the paperwork gets held up overseas and the record is added late).
So far, so good.

At this point what I would like to do is to open the report with the old
records (each report covers a week of data) and ask the user if they want to
print it. Since you cannot open multiple instances of a report reliably (as
per Allen Browne elsewhere on these boards), I need each report to open, then
the dialog (“Do you want to print this report?â€) to open. What is happening
is that the report is not visible when the dialog opens, and does not become
visible until the last dialog is closed (for testing I have about 10 weeks of
old data, therefore 10 separate reports, each with a dialog box). I have
tried Repainting, Setting Focus, Maximizing and Selecting the Report
(DoCmd.SelectObject) after opening, but nothing works.
I have run out of ideas, and I can’t find anything on the boards that is
similar.

Any help, including a different approach, would be most appreciated!

I am using Access 2007 on WIndows XP

Details follow (slightly simplified code):

The following code is behind the OnCLick event of a button on a form used to
select the date:
--------------------
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim Response2 As Single

'Open recordset of "old" shipped orders that have not been printed:
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("Ship Last Week Old Samples Weeks Query")
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Do While Not rst.EOF
OpenReportToWeekShipped (rst![WeekShipped])
Response2 = MsgBox("Print Report?", vbYesNo) 'if this is put
directly in the "IF" statment it always returns a 0
If Response2 = vbYes Then 'print report
DoCmd.PrintOut acPrintAll
End If 'if report is to be printed

rst.MoveNext
Loop 'if not rst.EOF

Function OpenReportToWeekShipped(dtWeekToPrint As Date)
Dim stWhereClause

stWhereClause = "[WeekShipped] = #" & dtWeekToPrint & "#"
On Error Resume Next 'If the OpenReport action is cancelled (due
to no data) an error is generated
DoCmd.OpenReport "Ship Last Week Samples Report", acViewReport, ,
stWhereClause

End Function