how can I run an update query on a report close?

[Guest]

Here is what i'm trying to accomplish:

I have my tblorders, that contains information about my new orders, and a
check box "printorders".

If the order has been printed and sent, the box is "true", otherwise it is
treated as a new order and shows up on my rptneworders.

When I print my orders and close my report, I want to run my update query so
that all my orders in my tblorders, that have false printorder checkbox are
updated to true..

Can anyone help?

Thanks,

Brook

 

[Rick Brandt]

Here is what i'm trying to accomplish:

I have my tblorders, that contains information about my new orders,
and a check box "printorders".

If the order has been printed and sent, the box is "true", otherwise
it is treated as a new order and shows up on my rptneworders.

When I print my orders and close my report, I want to run my update
query so that all my orders in my tblorders, that have false
printorder checkbox are updated to true..

Can anyone help?

Thanks,

Brook

In your report's Close event...

If MsgBox("Mark Records Printed?", vbYesNo) = vbYes Then
CurrentDB.EXECUTE "UPDATE TableName SET printorders = True", dbFailOnError
End If

I added the If-Then block because your users might preview the report before
making the "official" print and that gives them the option to not run the query.
You might not need that.

 

[Guest]

Thanks Rick,

That is definately what I am trying to accomplish, however I am getting
an error:

its a VB error:

Run Time Error: 3061
Too Few Parameters. Expected 1.

When I click the debug button, it goes to this line:

CurrentDb.Execute "UPDATE tblinventory SET printorders = True", dbFailOnError



Any Ideas?

Brook

 

[Rick Brandt]

Thanks Rick,

That is definately what I am trying to accomplish, however I am
getting
an error:

its a VB error:

Run Time Error: 3061
Too Few Parameters. Expected 1.

When I click the debug button, it goes to this line:

CurrentDb.Execute "UPDATE tblinventory SET printorders = True",
dbFailOnError



Any Ideas?

That would usually indicate that the field name in the SQL statement cannot be
found in the Table so Access assumes it must be a parameter.

Try creating a saved query object that does the desired update and then use...

CurrentDb.Execute "QueryName", dbFailOnError

 

[Guest]

Hello Rick,

Thank you... that worked perfectly... I created an update query,
qryupdateorders, and ran the execute against that.

I just have one last question, how can I code a confirmation box on the
number of orders that where updated?

Thanks,

Brook

 

[Rick Brandt]

Hello Rick,

Thank you... that worked perfectly... I created an update query,
qryupdateorders, and ran the execute against that.

I just have one last question, how can I code a confirmation box on
the number of orders that where updated?

Ok, for that we need to use a database variable which means you will need a
reference to the DAO library (at least with the knowledge I have). This is
because CurrentDB goes out of scope as soon as that line is executed so is no
longer available to give you the RecordAffected value. This will though.


Dim db as Database
Set db = CurrentDB

db.Execute "QueryName", dbFailOnError
MsgBox db.RecordsAffected & " Record Updated"

set db = nothing

 

[Guest]

Can I customize the message box? I.E. "6 Orders have been Sent"

Brook

 

[Rick Brandt]

Can I customize the message box? I.E. "6 Orders have been Sent"

You can put whatever text you want in the MsgBox.

MsgBox db.RecordsAffected & " Orders have been sent"

 

[Guest]

stupid me...

I should have caught that...

my last question.. can I have it set up as a vbyesno? So that the use has
the option to cancel the update?

Brook

 

[Rick Brandt]

stupid me...

I should have caught that...

my last question.. can I have it set up as a vbyesno? So that the
use has the option to cancel the update?

That was the purpose of the original MsgBox that I gave you. By the time
the message is displayed indicating the quantity of rows that were updated
it is too late to cancel.

 

[Guest]

Rick,...

I do understand that, but I was wondering if there was a way to do the
following based on both the suggestions that you gave me:

I.E. you are about to update db.RecordsAffected, VByesno?

Is this possible?

To have the record count on the msgbox? along with a yes / no option?

Brook

 

[Rick Brandt]

Rick,...

I do understand that, but I was wondering if there was a way to do
the following based on both the suggestions that you gave me:

I.E. you are about to update db.RecordsAffected, VByesno?

Is this possible?

To have the record count on the msgbox? along with a yes / no option?

Well you could drop the use of the Execute statement and instead use..

DoCmd.OpenQuery "QueryName"

That will present the same warning prompts as you would get when running the
query manually from the db window. Most of us use the Execute method
expressly to eliminate those prompts.

 

[Guest]

Thanks Rick,

I kinda figured that since the updatequery would have the actual record
count.

Thanks for all your help and responses..

maybe you can help me with some of my other post in the reports group...

Brook