Action query from code

[Guest]

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?

 

[John Spencer]

If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

See:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Guest]

One way is settng the warnings off

Docmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings True
****************************
Another way will be

CurrentDb.QueryDefs("QueryName").Execute dbFailOnError

 

[Guest]

One way is settng the warnings off

Docmd.SetWarnings False
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings True

****************************
Another way will be

CurrentDb.QueryDefs("QueryName").Execute dbFailOnError

 

[Guest]

Wow, Ofer, I learn something every day here. I can sure see the value of the
syntax for the Execute method you are using. I would have written it as:
CurrentDb.Execute("QueryName"), dbFailOnError

Now a note about this method for the OP:

In addition to bypassing the warnings method, it is, IMHO, the very best way
to run action queries in Access. It is much faster than any other method
because it bypasses the Access IU and goes directly to Jet. The only caution
is that you should always use the dbFailOnError parameter; otherwise you will
not know an error occured. Remember what I said about bypassing Access?
That means if you don't use dbFailOnError, Access will not know there was an
error.

 

[Guest]

Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again

If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?

 

[Guest]

It's possible that the update query is updating the same table that the form
is bounded to.
Like trying to update the same record on the same time, and that preduce the
error.

Is that the case?
If so, why do you need to run an update query if you can update the field
directly through the form?

--
Good Luck
BS"D

Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again

If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?

 

[John Spencer]

Sounds as if the update is trying to change the same record that is
showing in your form. If you need to do this, you can try to force a
save of the bound record before you run the update query.

If Me.Dirty = True Then Me.Dirty = False
....
DbAny.Execute StrSQL, dbFailOnError
....


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again

If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?

 

[Guest]

The form has only some control and a few lables, I don't want the users
changing anything here. All the changes are generated in code or pulled from
other locations.
'I use alot of Dlookup()'

It's possible that the update query is updating the same table that the form
is bounded to.
Like trying to update the same record on the same time, and that preduce the
error.

Is that the case?
If so, why do you need to run an update query if you can update the field
directly through the form?

--
Good Luck
BS"D

Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again

If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?