Delete message - Can is selectively be turned off?

[N. Graves]

Thanks for take time to read my question!!

I'm using code that will automatically delete rows of data in a field
and of course when you do this Access will prompt you that you are
about to delete x number of rows.

Most of the time I want this message be displayed. Can I use code
that will delete specified rows and not give the message?

Here is the code that I'm currently using:

Dim SQLDelete As String

SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

DoCmd.RunSQL SQLDelete

Thanks

N. Graves

[Allen Browne]

You can avoid the message with:
DoCmd.SetWarnin gs False

However, you won't know if anything went wrong with the action query. The
following method is better, as it gives a message only if there is a
problem:
dbEngine(0)(0). Execute strSQL, dbFailOnError

If you need an all-or-nothing result for your action query, you can wrap the
whole thing in a transaction. Example in article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"N. Graves" <ng*****@REMOVE yahoo.com> wrote in message
news:nq******** *************** *********@4ax.c om...

Thanks for take time to read my question!!

I'm using code that will automatically delete rows of data in a field
and of course when you do this Access will prompt you that you are
about to delete x number of rows.

Most of the time I want this message be displayed. Can I use code
that will delete specified rows and not give the message?

Here is the code that I'm currently using:

Dim SQLDelete As String

SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

DoCmd.RunSQL SQLDelete

Thanks

N. Graves

[Dave]

try wrapping the DoCmd like this

Application.Set Option "Confirm Record Changes", False
Application.Set Option "Confirm Document Deletions", False
Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Record Changes", True
Application.Set Option "Confirm Document Deletions", True
Application.Set Option "Confirm Action Queries", True
i use all three lines to turn everything off when distributing run-times as
i dont want uses to have to fiddle around

you will probably find that you dont need all three lines and just

Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Action Queries", True

will do

hope this helps
Dave
"N. Graves" <ng*****@REMOVE yahoo.com> wrote in message
news:nq******** *************** *********@4ax.c om...

Thanks for take time to read my question!!

I'm using code that will automatically delete rows of data in a field
and of course when you do this Access will prompt you that you are
about to delete x number of rows.

Most of the time I want this message be displayed. Can I use code
that will delete specified rows and not give the message?

Here is the code that I'm currently using:

Dim SQLDelete As String

SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

DoCmd.RunSQL SQLDelete

Thanks

N. Graves

[Don Leverton]

Hi "N",

Try using the Execute Method (DAO), but be sure to include the dbFailOnError
option.
Like this:

....
Dim MyDB As DAO.Database
Set MyDB = CurrentDB

Dim SQLDelete As String
SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

MyDB.Execute SQLDelete, dbFailOnError

Set MyDB = Nothing
....

--
HTH,
Don
=============== ==============
Use My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==
"N. Graves" <ng*****@REMOVE yahoo.com> wrote in message
news:nq******** *************** *********@4ax.c om...

Thanks for take time to read my question!!

I'm using code that will automatically delete rows of data in a field
and of course when you do this Access will prompt you that you are
about to delete x number of rows.

Most of the time I want this message be displayed. Can I use code
that will delete specified rows and not give the message?

Here is the code that I'm currently using:

Dim SQLDelete As String

SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

DoCmd.RunSQL SQLDelete

Thanks

N. Graves

[N. Graves]

Thanks so much for your good and quick response...

p.s. N = Norris ;-)

Thanks

On Mon, 28 Jun 2004 15:43:13 GMT, "Don Leverton"
<le************ ****@telusplane t.net> wrote:

Hi "N",

Try using the Execute Method (DAO), but be sure to include the dbFailOnError
option.
Like this:

...
Dim MyDB As DAO.Database
Set MyDB = CurrentDB

Dim SQLDelete As String
SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

MyDB.Execute SQLDelete, dbFailOnError

Set MyDB = Nothing
...

[N. Graves]

Thanks for you support and quick answer.

I was really pissed to when I found that my database now requires that
I have to include this.

Thanks

N. Graves

On Mon, 28 Jun 2004 15:40:17 +0000 (UTC), "Dave" <[e-mail] > wrote:

try wrapping the DoCmd like this

Application.Set Option "Confirm Record Changes", False
Application.Set Option "Confirm Document Deletions", False
Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Record Changes", True
Application.Set Option "Confirm Document Deletions", True
Application.Set Option "Confirm Action Queries", True
i use all three lines to turn everything off when distributing run-times as
i dont want uses to have to fiddle around

you will probably find that you dont need all three lines and just

Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Action Queries", True

will do

hope this helps
Dave
"N. Graves" <ng*****@REMOVE yahoo.com> wrote in message
news:nq******* *************** **********@4ax. com...

Thanks for take time to read my question!!

I'm using code that will automatically delete rows of data in a field
and of course when you do this Access will prompt you that you are
about to delete x number of rows.

Most of the time I want this message be displayed. Can I use code
that will delete specified rows and not give the message?

Here is the code that I'm currently using:

Dim SQLDelete As String

SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

DoCmd.RunSQL SQLDelete

Thanks

N. Graves

[N. Graves]

Wow great follow up to my questions! Thank you Allen.
N. Graves

On Mon, 28 Jun 2004 23:37:44 +0800, "Allen Browne"
<Al*********@Se eSig.Invalid> wrote:

You can avoid the message with:
DoCmd.SetWarnin gs False

However, you won't know if anything went wrong with the action query. The
following method is better, as it gives a message only if there is a
problem:
dbEngine(0)(0). Execute strSQL, dbFailOnError

If you need an all-or-nothing result for your action query, you can wrap the
whole thing in a transaction. Example in article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

[Tony Toews]

"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

However, you won't know if anything went wrong with the action query. The
following method is better, as it gives a message only if there is a
problem:
dbEngine(0)(0). Execute strSQL, dbFailOnError

Just curious. Why not currentdb.execu te etc?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"Dave" <[e-mail] > wrote:

you will probably find that you dont need all three lines and just

Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Action Queries", True

I prefer, if DAO, to use Currentdb.Execu te strSQL,dbfailon error command instead of
docmd.runsql. For ADO use CurrentProject. Connection.Exec ute strCommand,
lngRecordsAffec ted, adCmdText

If you're going to use docmd.setwarnin gs make very sure you put the True statement in
any error handling code as well. Otherwise wierd things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execu te took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David W. Fenton]

"Dave" <[e-mail] > wrote in
news:cb******** **@sparta.btint ernet.com:

try wrapping the DoCmd like this

Application.Set Option "Confirm Record Changes", False
Application.Set Option "Confirm Document Deletions", False
Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Record Changes", True
Application.Set Option "Confirm Document Deletions", True
Application.Set Option "Confirm Action Queries", True
i use all three lines to turn everything off when distributing
run-times as i dont want uses to have to fiddle around

you will probably find that you dont need all three lines and just

Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Action Queries", True

will do

Why in the world use RunSQL and all those extra switches when in
99.999% of cases, Executing the SQL directly will do the job without
needing to fiddle with warnings and options?

If you want something as simple as DoCmd.RunSQL, try this:

Public Function ExecuteSQL(strS QL As String, _
Optional db As Database) As Boolean
On Error GoTo errHandler

If db Is Nothing Then
Set db = CurrentDB() ' or DBEngine(0)(0)
End If
db.Execute strSQL, dbFailOnError

exitRoutine:
Exit Function

errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & Err.Number & ": " & Err.Description , _
vbExclamation, "Error in mdlDWF.ExecuteS QL()"
Debug.Print "SQL Error: " & strSQL
Resume exitRoutine
End Function

You can then call that as:

ExecuteSQL strSQL

The db argument is useful if you're already caching a global db
variable and want to re-uses it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc