Delete record acCmdDeleteRecord not removing record: Form

[Guest]

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo, "Atlantic County
Office Of GIS")
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form using
Dsum. It works, sort of... The record is zero'd out so that all the fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to why
this might be happening?

 

[Guest]

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you

 

[Guest]

Well, its an Event Procedure from a command button that runs this code. Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form using
Dsum. It works, sort of... The record is zero'd out so that all the fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to why
this might be happening?

 

[Allen Browne]

No, you do not need to execute a SQL statement if you are deleting a record
from a bound form.

The record cannot be "deleted" if it is a new one (unsaved new record). If
it is dirty at all, Access has to undo or save it. Saving (the default)
makes no sense, particularly as it might save, so try somthing like this:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.Close acForm, Me.Name
End If

That should generate the built-in delete confirmation message. You can have
your custom one as well if you wish.

If you still believe the record is not being deleted, add this line just
before the RunCommand:
Debug.Print Me.[ID]
substituting your primary key field name for ID. You should then see the
primary key value of the record that was deleted in the Immediate Window
(Ctrl+G) after this runs. You can then investigate whether the remaining
blank record is the same one, or is being caused by some other means (e.g.
faulty code in the Current event of the Form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, its an Event Procedure from a command button that runs this code.
Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form
using
Dsum. It works, sort of... The record is zero'd out so that all the
fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to
why
this might be happening?

 

[Guest]

Thanks for the response Tom. I am reluctant though... The record I am
trying to delete is the many part of a one-to-many relationship. What would
be the syntax for setting the criteria to the current record and only the
current record? Or, wouldn't that matter? I am using the docmd.runcommand
acCmdDeleteRecord which erases it but doen't remove it from the table.
--
http://njgin.aclink.org

Make that the 3.6 Object Library for Access 2000/2002/2003. Temporarily
confused it with JET 4.0. Oh, the ravages of aging....sigh.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Nope. Just do something like this:

Private Sub cmdDeleteRecord_Click()
On Error GoTo ProcError

Dim strSQL As String

strSQL = "DELETE [table.*] FROM table WHERE criteria"
CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdDeleteRecord_Click..."
Resume ExitProc
End Sub
******************************

An example strSQL statement for a numeric primary key would be the following:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryID = " & Me.CategoryID

If the criteria involves a text value, then you'll need to wrap the criteria
in quotes or use Chr(34), as in the following examples:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = '" & Me.CategoryName & "'"

or

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = " & Chr(34) & Me.CategoryName & Chr(34)


In order to use the optional dbFailOnError parameter, you will need to set a
reference to the "Microsoft DAO 4.0 Object Library" (or version 3.51 for
Access 97).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Well, its an Event Procedure from a command button that runs this code. Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org


:

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form using
Dsum. It works, sort of... The record is zero'd out so that all the fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to why
this might be happening?

 

[Guest]

I guess I could add some sort of clean up routine at the end of it where I'd
delete all records with a zero value in some field maybe? What do you all
think, am I not doing something correctly? It sure does feel like it ;-)
--
http://njgin.aclink.org

Thank you for the response Allen. I actually saw this on a previous thread
and I plan on using it for the unsaved record.

This is a saved record that I am attempting to delete and I am using
something very similar to the second part of your argument. It does erase
it, but it doesn't remove it from the table. I'm using 2000 and I have some
controls on the form that are locked. Do you think this has anything to do
with it?
--
http://njgin.aclink.org

No, you do not need to execute a SQL statement if you are deleting a record
from a bound form.

The record cannot be "deleted" if it is a new one (unsaved new record). If
it is dirty at all, Access has to undo or save it. Saving (the default)
makes no sense, particularly as it might save, so try somthing like this:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.Close acForm, Me.Name
End If

That should generate the built-in delete confirmation message. You can have
your custom one as well if you wish.

If you still believe the record is not being deleted, add this line just
before the RunCommand:
Debug.Print Me.[ID]
substituting your primary key field name for ID. You should then see the
primary key value of the record that was deleted in the Immediate Window
(Ctrl+G) after this runs. You can then investigate whether the remaining
blank record is the same one, or is being caused by some other means (e.g.
faulty code in the Current event of the Form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, its an Event Procedure from a command button that runs this code.
Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org


:

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form
using
Dsum. It works, sort of... The record is zero'd out so that all the
fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to
why
this might be happening?

 

[Guest]

Nope. Just do something like this:

Private Sub cmdDeleteRecord_Click()
On Error GoTo ProcError

Dim strSQL As String

strSQL = "DELETE [table.*] FROM table WHERE criteria"
CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdDeleteRecord_Click..."
Resume ExitProc
End Sub
******************************

An example strSQL statement for a numeric primary key would be the following:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryID = " & Me.CategoryID

If the criteria involves a text value, then you'll need to wrap the criteria
in quotes or use Chr(34), as in the following examples:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = '" & Me.CategoryName & "'"

or

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = " & Chr(34) & Me.CategoryName & Chr(34)


In order to use the optional dbFailOnError parameter, you will need to set a
reference to the "Microsoft DAO 4.0 Object Library" (or version 3.51 for
Access 97).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Well, its an Event Procedure from a command button that runs this code. Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form using
Dsum. It works, sort of... The record is zero'd out so that all the fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to why
this might be happening?

 

[Guest]

Make that the 3.6 Object Library for Access 2000/2002/2003. Temporarily
confused it with JET 4.0. Oh, the ravages of aging....sigh.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Nope. Just do something like this:

Private Sub cmdDeleteRecord_Click()
On Error GoTo ProcError

Dim strSQL As String

strSQL = "DELETE [table.*] FROM table WHERE criteria"
CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdDeleteRecord_Click..."
Resume ExitProc
End Sub
******************************

An example strSQL statement for a numeric primary key would be the following:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryID = " & Me.CategoryID

If the criteria involves a text value, then you'll need to wrap the criteria
in quotes or use Chr(34), as in the following examples:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = '" & Me.CategoryName & "'"

or

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = " & Chr(34) & Me.CategoryName & Chr(34)


In order to use the optional dbFailOnError parameter, you will need to set a
reference to the "Microsoft DAO 4.0 Object Library" (or version 3.51 for
Access 97).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Well, its an Event Procedure from a command button that runs this code. Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form using
Dsum. It works, sort of... The record is zero'd out so that all the fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to why
this might be happening?

 

[Guest]

Thank you for the response Allen. I actually saw this on a previous thread
and I plan on using it for the unsaved record.

This is a saved record that I am attempting to delete and I am using
something very similar to the second part of your argument. It does erase
it, but it doesn't remove it from the table. I'm using 2000 and I have some
controls on the form that are locked. Do you think this has anything to do
with it?
--
http://njgin.aclink.org

No, you do not need to execute a SQL statement if you are deleting a record
from a bound form.

The record cannot be "deleted" if it is a new one (unsaved new record). If
it is dirty at all, Access has to undo or save it. Saving (the default)
makes no sense, particularly as it might save, so try somthing like this:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.Close acForm, Me.Name
End If

That should generate the built-in delete confirmation message. You can have
your custom one as well if you wish.

If you still believe the record is not being deleted, add this line just
before the RunCommand:
Debug.Print Me.[ID]
substituting your primary key field name for ID. You should then see the
primary key value of the record that was deleted in the Immediate Window
(Ctrl+G) after this runs. You can then investigate whether the remaining
blank record is the same one, or is being caused by some other means (e.g.
faulty code in the Current event of the Form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, its an Event Procedure from a command button that runs this code.
Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form
using
Dsum. It works, sort of... The record is zero'd out so that all the
fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to
why
this might be happening?

 

[Guest]

I am using the docmd.runcommand acCmdDeleteRecord which erases

it but doen't remove it from the table.

I'm not sure where you got the idea that a record can be "erased".
Docmd.Runcommand acCmdDeleteRecord is one method of deleting records. There
are others, as you've already seen.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks for the response Tom. I am reluctant though... The record I am
trying to delete is the many part of a one-to-many relationship. What would
be the syntax for setting the criteria to the current record and only the
current record? Or, wouldn't that matter? I am using the docmd.runcommand
acCmdDeleteRecord which erases it but doen't remove it from the table.
--
http://njgin.aclink.org

Make that the 3.6 Object Library for Access 2000/2002/2003. Temporarily
confused it with JET 4.0. Oh, the ravages of aging....sigh.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Nope. Just do something like this:

Private Sub cmdDeleteRecord_Click()
On Error GoTo ProcError

Dim strSQL As String

strSQL = "DELETE [table.*] FROM table WHERE criteria"
CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdDeleteRecord_Click..."
Resume ExitProc
End Sub
******************************

An example strSQL statement for a numeric primary key would be the following:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryID = " & Me.CategoryID

If the criteria involves a text value, then you'll need to wrap the criteria
in quotes or use Chr(34), as in the following examples:

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = '" & Me.CategoryName & "'"

or

strSQL = "DELETE [Categories.*] FROM Categories " _
& "WHERE CategoryName = " & Chr(34) & Me.CategoryName & Chr(34)


In order to use the optional dbFailOnError parameter, you will need to set a
reference to the "Microsoft DAO 4.0 Object Library" (or version 3.51 for
Access 97).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Well, its an Event Procedure from a command button that runs this code. Do I
then need to run an SQL statement to remove the record from the table?
--
http://njgin.aclink.org


:

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main form using
Dsum. It works, sort of... The record is zero'd out so that all the fields
are empty but it is not deleted from the table. Is this because I have
controls that are locked? Or rather, are there any suggestions as to why
this might be happening?

 

[Allen Browne]

Something is wrong.

If you are successfully deleting the record, then it *is* removed form the
table.

Either you are not in fact deleting the record (e.g. you are suppressing the
error message that lets you know the delete failed), or else you have code
that is creating another bad record.

Your comments to Tom about cascading the delete into another table or tables
could have something to do with it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I guess I could add some sort of clean up routine at the end of it where
I'd
delete all records with a zero value in some field maybe? What do you all
think, am I not doing something correctly? It sure does feel like it ;-)
--
http://njgin.aclink.org

Thank you for the response Allen. I actually saw this on a previous
thread
and I plan on using it for the unsaved record.

This is a saved record that I am attempting to delete and I am using
something very similar to the second part of your argument. It does
erase
it, but it doesn't remove it from the table. I'm using 2000 and I have
some
controls on the form that are locked. Do you think this has anything to
do
with it?
--
http://njgin.aclink.org

No, you do not need to execute a SQL statement if you are deleting a
record
from a bound form.

The record cannot be "deleted" if it is a new one (unsaved new record).
If
it is dirty at all, Access has to undo or save it. Saving (the default)
makes no sense, particularly as it might save, so try somthing like
this:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.Close acForm, Me.Name
End If

That should generate the built-in delete confirmation message. You can
have
your custom one as well if you wish.

If you still believe the record is not being deleted, add this line
just
before the RunCommand:
Debug.Print Me.[ID]
substituting your primary key field name for ID. You should then see
the
primary key value of the record that was deleted in the Immediate
Window
(Ctrl+G) after this runs. You can then investigate whether the
remaining
blank record is the same one, or is being caused by some other means
(e.g.
faulty code in the Current event of the Form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, its an Event Procedure from a command button that runs this
code.
Do I
then need to run an SQL statement to remove the record from the
table?
--
http://njgin.aclink.org


:

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a
record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main
form
using
Dsum. It works, sort of... The record is zero'd out so that all
the
fields
are empty but it is not deleted from the table. Is this because I
have
controls that are locked? Or rather, are there any suggestions as
to
why
this might be happening?

 

[Guest]

Well, elsewhere I am using DoCmd.SetWarnings (WarningsOff), and then
DoCmd.SetWarnings (WarningsOn), however I don't think the warning actually
come back on. Any thoughts on these commands? Otherwise I'll look into it a
bit further and if/when I know somehthing I'll reply back.

Allen, Tom, & ldiaz, I really appreciate your time and involvement. Not
just with this thread either. You and several others have been there
throughout the development of anything I do in Access. I've only been doing
this maybe 10 months now and you all have taught me quite a bit with your
involvement in this community. I just want to say, I am very grateful and
thank you!

--
http://njgin.aclink.org

Something is wrong.

If you are successfully deleting the record, then it *is* removed form the
table.

Either you are not in fact deleting the record (e.g. you are suppressing the
error message that lets you know the delete failed), or else you have code
that is creating another bad record.

Your comments to Tom about cascading the delete into another table or tables
could have something to do with it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I guess I could add some sort of clean up routine at the end of it where
I'd
delete all records with a zero value in some field maybe? What do you all
think, am I not doing something correctly? It sure does feel like it ;-)
--
http://njgin.aclink.org

Thank you for the response Allen. I actually saw this on a previous
thread
and I plan on using it for the unsaved record.

This is a saved record that I am attempting to delete and I am using
something very similar to the second part of your argument. It does
erase
it, but it doesn't remove it from the table. I'm using 2000 and I have
some
controls on the form that are locked. Do you think this has anything to
do
with it?
--
http://njgin.aclink.org


:

No, you do not need to execute a SQL statement if you are deleting a
record
from a bound form.

The record cannot be "deleted" if it is a new one (unsaved new record).
If
it is dirty at all, Access has to undo or save it. Saving (the default)
makes no sense, particularly as it might save, so try somthing like
this:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.Close acForm, Me.Name
End If

That should generate the built-in delete confirmation message. You can
have
your custom one as well if you wish.

If you still believe the record is not being deleted, add this line
just
before the RunCommand:
Debug.Print Me.[ID]
substituting your primary key field name for ID. You should then see
the
primary key value of the record that was deleted in the Immediate
Window
(Ctrl+G) after this runs. You can then investigate whether the
remaining
blank record is the same one, or is being caused by some other means
(e.g.
faulty code in the Current event of the Form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, its an Event Procedure from a command button that runs this
code.
Do I
then need to run an SQL statement to remove the record from the
table?
--
http://njgin.aclink.org


:

You need run a SQL like this:

DELETE [table.*]
FROM table
WHERE criteria

maybe this help you


--
Lorenzo Díaz
Cad Technician


:

I've got a command button that uses the following to delete a
record:

Response = MsgBox("Delete The Current Material?", vbYesNo)
If Response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MaterialSum
DoCmd.RunCommand acCmdCloseWindow
ElseIf Response = vbNo Then
Exit Sub
End If
Where MaterialSum is a module that totals the price to the main
form
using
Dsum. It works, sort of... The record is zero'd out so that all
the
fields
are empty but it is not deleted from the table. Is this because I
have
controls that are locked? Or rather, are there any suggestions as
to
why
this might be happening?

 

[Guest]

If you are turning warnings off in code, but they are not being turned back
on, even though you have a statement to do so, this indicates that your
procedure may be bombing out before it gets to the statement that should
restore warnings. You should make sure to turn warnings back on in an error
handler. Please see a previous post that I made on this topic:

http://groups.google.com/group/micr..._frm/thread/299730458445a298/a413daf8e6cc4585

You're very welcome for the help. I think I can speak for others by saying
that we enjoy seeing people learn how to solve problems. I hope to see your
name as a frequent poster who provides answers to others in the coming years.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Well, elsewhere I am using DoCmd.SetWarnings (WarningsOff), and then
DoCmd.SetWarnings (WarningsOn), however I don't think the warning actually
come back on. Any thoughts on these commands? Otherwise I'll look into it a
bit further and if/when I know something I'll reply back.

Allen, Tom, & ldiaz, I really appreciate your time and involvement. Not
just with this thread either. You and several others have been there
throughout the development of anything I do in Access. I've only been doing
this maybe 10 months now and you all have taught me quite a bit with your
involvement in this community. I just want to say, I am very grateful and
thank you!

 

[Guest]

Here is the code I use for the warnings:
DoCmd.SetWarnings (WarningsOff)
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings (WarningsOn)
DoCmd.RunCommand acCmdCloseWindow
Thanks for the link I'll check it out.
I used the code similar to what you and Allen were bouncing back and forth on:
stSQL = "DELETE [MATERIALDETAILS.*] FROM MATERIALDETAILS WHERE
[QUANTITY] = 0"
CurrentDb.Execute stSQL
and applied it after the control on the other form gets the focus to delete
out zero'd
records. Kind of lame, I know. But it seems to work. If its terribly
frustrating for you all
I could send or post all of the code. Short of that, I'll wait and see how
it goes. So far it feels like
kind of a long way around but it works the way I need it to. If