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.SetWarnings 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*****@REMOVEyahoo.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

[Dave]

try wrapping the DoCmd like this

Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Record Changes", True
Application.SetOption "Confirm Document Deletions", True
Application.SetOption "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.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Action Queries", True

will do

hope this helps
Dave
"N. Graves" <ng*****@REMOVEyahoo.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

[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.Net 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*****@REMOVEyahoo.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]

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****************@telusplanet.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.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Record Changes", True
Application.SetOption "Confirm Document Deletions", True
Application.SetOption "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.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Action Queries", True

will do

hope this helps
Dave
"N. Graves" <ng*****@REMOVEyahoo.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*********@SeeSig.Invalid> wrote:

You can avoid the message with:
DoCmd.SetWarnings 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*********@SeeSig.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.execute 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.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Action Queries", True

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText

If you're going to use docmd.setwarnings 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.execute 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.btinternet.com:

try wrapping the DoCmd like this

Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Record Changes", True
Application.SetOption "Confirm Document Deletions", True
Application.SetOption "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.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "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(strSQL 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.ExecuteSQL()"
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

[Allen Browne]

Hi Tony

As you know, either will work, but CurrentDb upates all the collections of
the current database (usually dbEngine(0)(0)), creates a new object, and
points it to the current database.

dbEngine(0)(0) is therefore better because:
1. It is thousands of times more efficient, since it does not have to flush
the collections, and the object already exists.

2. It survives more than one line, e.g. the next line might be:
MsgBox dbEngine(0)(0).RecordsAffected & " deleted."

OTOH, CurrentDb() is better because:
1. It returns correct results if any fields/tables changed recently.

2. The current database might *not* be dbEngine(0)(0). Examples:
- after running a wizard,
- you work with transactions when more than one Database is open (which
happens automatically if you are working with RecordsetClone).

So, my personal practice is:
- Use CurrentDb() during where tables/fields are being created/modified.
- Use dbEngine(0)(0) for executing queries (other than DDL queries).

--
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.

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:a8********************************@4ax.com...

"Allen Browne" <Al*********@SeeSig.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.execute 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

[N. Graves]

David, Thanks for you information.

I'm getting a compile error for the user-defive type. My inexperience
will not let me continue could you please post this information for
me.

N. Graves
On Mon, 28 Jun 2004 21:25:56 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

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

try wrapping the DoCmd like this

Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "Confirm Record Changes", True
Application.SetOption "Confirm Document Deletions", True
Application.SetOption "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.SetOption "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.SetOption "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(strSQL 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.ExecuteSQL()"
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]

N. Graves <ng*****@REMOVEyahoo.com> wrote in
news:a4********************************@4ax.com:

I'm getting a compile error for the user-defive type. My
inexperience will not let me continue could you please post this
information for me.

As I wrote to you in email (many people, myself included, consider
it impolite to both post and email; if you do it, you should
indicate that you're doing so in your message. Had you done so I
would have responded only in the newsgroup, not in private email),
you surely are using A2K or later and are falling victim to MS's
ridiculously stupid decision to not use DAO as the default data
access interface.

To fix the problem, while viewing a code module, go to the TOOLS
menu and choose REFERENCES. Search for Microsoft DAO 3.6 Object
Library and check it off. If you're not using ADO, then uncheck it,
as it's not needed (and for many things, Access provides interfaces
to ADO objects like CurrentConnection via the Application objet).

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

[N. Graves]

David thanks you so much for taking the to answer both the newsgroups
post and my email.

I was not trying to be rude but thought I was probably not the only
person that could learn from you knowledge is why I posted it back in
this group.

This group is so great and you folks that answer these questions are
great. I have used it quite a bit lately and I did not mean to rude.
I apologize and will not do both again.

Thanks again for you responses. They are working perfectly!

N. Graves

On Wed, 07 Jul 2004 01:10:26 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

N. Graves <ng*****@REMOVEyahoo.com> wrote in
news:a4********************************@4ax.com :

I'm getting a compile error for the user-defive type. My
inexperience will not let me continue could you please post this
information for me.

As I wrote to you in email (many people, myself included, consider
it impolite to both post and email; if you do it, you should
indicate that you're doing so in your message. Had you done so I
would have responded only in the newsgroup, not in private email),
you surely are using A2K or later and are falling victim to MS's
ridiculously stupid decision to not use DAO as the default data
access interface.

To fix the problem, while viewing a code module, go to the TOOLS
menu and choose REFERENCES. Search for Microsoft DAO 3.6 Object
Library and check it off. If you're not using ADO, then uncheck it,
as it's not needed (and for many things, Access provides interfaces
to ADO objects like CurrentConnection via the Application objet).