Code for printing routine

[Kevin Bruce]

Here's the situation:

My program prints Letters of Agreement and invoices plus records the date on
which they were printed.
I have have an On_Click procedure that is intended to do the following:
If the contract and invoice have not yet been created, then print them and
record the date.
If they have already been printed and the dates recorded, simply preview
them.

Trust me, everything insofar as update queries and SQL statements for the
various objects works.

The problem: when creating new invoices, the data does not refresh at the
point where it is needed to create and print the invoices.

Note: the text box 'txtContractedToPresenter' is key here. If there is a
date in this text box, the print preview routine works just fine. Although
SQL statements for both the contract and the invoice are almost identical,
the invoice still does not print -- it contains an empty record set.



Private Sub cmdPrintOrPreview_Click()

Dim stDocName As String
Dim intPrintCount As Integer
Dim strContractDate As String

txtContractedToPresenter.SetFocus
strContractDate = txtContractedToPresenter.Text
If strContractDate = "" Then

'update invoice number
stDocName = "qryInvoiceNumberUpdate"
DoCmd.OpenQuery stDocName

'update the records to show that the Agreement has been issued
'and record the booking fee invoice number for each event
stDocName = "qryBookingContractedToPresenterUpdate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Print the Agreement
stDocName = "rptBookingPresenterAgreement"
DoCmd.OpenReport stDocName, acNormal

[THIS IS THE POINT WHERE THE PROBLEM OCCURS]

'print the Booking Fee Invoice
stDocName = "rptBookingFeeInvoice"
DoCmd.OpenReport stDocName, acViewNormal

'print the Presenter's schedule
stDocName = "rptBookingPresenterSummary"
DoCmd.OpenReport stDocName, acNormal

Else


'Preview the Agreement
stDocName = "rptBookingPresenterAgreement"
DoCmd.OpenReport stDocName, acViewPreview

'print the Booking Fee Invoice
stDocName = "rptBookingFeeInvoice"
DoCmd.OpenReport stDocName, acViewPreview

'print the Presenter's schedule
stDocName = "rptBookingPresenterSummary"
DoCmd.OpenReport stDocName, acViewPreview


End If

End Sub

 

[Rod Scoullar]

Kevin,

The key to the solution as you have rightly suggested is to discover why the
recordsource of the invoice printing report returns no rows.

I would try the following approach. You may have already done this or
something equivalent. Please excuse my lack of knowledge of how you go
about things.


Does the problem occur if you step through the code slowly line by line? If
it does then the problem can not be that the report is opened before the
data is committed to the database.

If you step through the code, do the tables concerned contain the expected
values immediately before the OpenReport is called? If so then the problem
is in the way the recordsource of the report is structured.

Does the recordsource of the report make any reference to controls on forms
which may not have been refreshed after data in the tables has been changed?

Extract the recordsource of the report and run it as a query when the
program is at the point at which the report is to be called. Play with the
criteria values and see if any changes produce the results you expect. Try
using a different type of join between the tables (if you are using more
than one table in the query.)

Good luck.

Rod Scoullar