On_Click event not running all the code

[Kevin Bruce]

This probably Day One type stuff, but the simple answer hasn't made itself
apparent to me yet.

I have a button to which is attached an On_Click event that does the
following:

1. Prints a Letter of Agreement,

2. runs an update query that records the date of the Agreement being issued
and also fetches an invoice number from a table and attaches it to the
current recordset.

3. Prints an invoice for the items on the Agreement

4. Runs an update query that adds 1 to the last invoice number

5. Prints a summary



After number 2) above, the code stops running, and I don't know why. Below
is my code.

Thanks in advance for any assistance.

_Kevin

'Print the Agreement three copies

stDocName = "rptBookingPresenterAgreement"

For intPrintCount = 1 To 3

DoCmd.OpenReport stDocName, acNormal

Next intPrintCount



'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

'===============

'CODE STOPS HERE

'===============


'print the Booking Fee Invoice -- three copies

stDocName = "rptBookingFeeInvoice"

For intPrintCount = 1 To 3

DoCmd.OpenReport stDocName, acViewNormal

Next intPrintCount


'update invoice number

stDocName = "qryinvoicenumberupdate"

DoCmd.OpenQuery stDocName


'print the Presenter's schedule

stDocName = "rptBookingPresenterSummary"

DoCmd.OpenReport stDocName, acNormal

 

[Graham R Seach]

Kevin,

I can't see why the code should stop in the place you indicate, however,
acNormal is not the correct constant to use. You should be using
acViewNormal. Try using the following code; at least using db.Execute,
you'll see if there is an error.

Dim db As Database
Set db = CurrentDb

'Print the Agreement three copies
stDocName = "rptBookingPresenterAgreement"
For intPrintCount = 1 To 3
DoCmd.OpenReport stDocName, acViewNormal
Next intPrintCount

'update the records to show that the Agreement has been issued
'and record the booking fee invoice number for each event
db.Execute "qryBookingContractedToPresenterUpdate", dbFailOnError

'print the Booking Fee Invoice -- three copies
stDocName = "rptBookingFeeInvoice"
For intPrintCount = 1 To 3
DoCmd.OpenReport stDocName, acViewNormal
Next intPrintCount

'update invoice number
db.Execute "qryinvoicenumberupdate", dbFailOnError

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

Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[John Vinson]

DoCmd.OpenQuery stDocName, acNormal, acEdit

'===============

'CODE STOPS HERE

'===============

Yes, it will; it's waiting for you to close the Query datasheet grid.
May I ask why you're opening it at all? Do you require the user to
manually record the fact that the report's been printed? It might be
simpler all around to just run an Update query instead!

 

[Kevin Bruce]

John_

Thanks for your response.

What makes you think I am NOT trying to run an update query? The stDocName
below refers to a perfectly functioning update query that I've written which
updates all the records that it is supposed in the assigned manner. I am not
at all certain what you mean by this code waiting for me to close the query
data sheet grid; I wasn't aware that I was opening it -- I agree with you:
why would I want to open it in the first place? All I want is for the update
query to run and then for the next line of code after that to execute. From
what you've told me, clearly there is something wrong with this line of
code:

DoCmd.OpenQuery stDocName, acNormal, acEdit

How do I rewrite this so that it simply runs the update query and then gets
on with the rest of the procedure?

Thanks for your assistance.

_Kevin

 

[John Vinson]

DoCmd.OpenQuery stDocName, acNormal, acEdit

How do I rewrite this so that it simply runs the update query and then gets
on with the rest of the procedure?

I'd suggest using the Query's Execute method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.Querydefs(stDocName)
qd.Execute dbFailOnError
Set qd = Nothing