Suppressing query messages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnny Bright - 15 Nov 2005 19:25 GMT

Hi there,

I have 6 queries I need to run which will fill in null records in 6 fields.
I don't want my users to have to click "Yes" 6 times. How can I suppress the
"You are about to update xxx rows" messages. The following code and sql are
an example.

DoCmd.OpenQuery "qryUpdtGST0", acNormal, acEdit

SQL code from the query SQL View:

UPDATE tblFinalData SET tblFinalData.GSTRatio = 0
WHERE (((tblFinalData.GSTRatio) Is Null));

Could I, as an alternative, run an update query in code? I've just never
been very good at the proper formation of this sort of coding.

All help is greatly appreciated.

Thanks,

JR

Signature

www.brightfuture.ca/bright
My email address can be found on my site.

Reply to this Message

Michael Suttkus, II - 15 Nov 2005 19:53 GMT

Choose the menu Tools, then Options. On the "Edit/Find" tab of the dialog
box that comes up, you'll see a check box labeled "Action Queries" inside a
box called "Confirm". If you turn that off, Access will no longer ask you if
you want to run an action query.

If you want to temporarily turn it off in the code, but don't want to turn
it off in general, you can use:

DoCmd.SetWarnings False

Then set it back to true after running your queries.

There's another way to do the same thing, which involves accessing the
Applicatino.SetOption & .GetOption methods. It's a bit lower level, but lets
you handle the situation with a bit more finess.

> Hi there,
>
[quoted text clipped - 18 lines]
>
> JR

Reply to this Message

giorgio rancati - 15 Nov 2005 19:56 GMT

Hi JR,

You can make only one query
The MyQuery sql Code
----
UPDATE MyTable
SET Field1=IIF(IsNull(Field1),0,Field1),
Field2=IIF(IsNull(Field2),0,Field2),
Field3=IIF(IsNull(Field3),0,Field3),
Field4=IIF(IsNull(Field4),0,Field4),
Field5=IIF(IsNull(Field5),0,Field5),
Field6=IIF(IsNull(Field6),0,Field6)
----

and execute
----
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyQuery"
DoCmd.SetWarnings True
----

or
----
Currentproject.Connection.Execute "Execute MyQuery"
----

bye

Signature

Giorgio Rancati
[Office Access MVP]

> Hi there,
>
[quoted text clipped - 18 lines]
>
> JR

Reply to this Message