Override Warnings?

[Ryan Langton]

I have a form that deletes a record if the first field is left blank by the
user. I give the user a warning box "This field cannot be left blank or the
record will be deleted. Do you want to delete the record?". The problem
I'm having is after they say Yes to my warning, Access then gives them
another warning "Are you sure you want to delete a record?" Is there a
better way of doing this so they don't get the Access warning in addition to
mine? Here is my code used to delete the record (within the condition that
tests the user response).

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

 

[Sylvain Lafontaine]

I don't use the DoCmd.DoMenuItem, so I don't really know but you can try to
uncheck the "Record changes" checkbox under the Edit/Find tab of the Options
dialog window. Don't confuse it with the "Document deletions" under it; as
this is for object deletions, not record deletions.

The following article may be of interest to you:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;234866

 

[Ryan Langton]

Thanks for the help. The only reason I'm using DoCmd.DoMenuItem is because
I looked at how Microsoft pre-programmed a generic Delete button in access
and this is exactly how they do it. I was actually hoping there was a
better way of deleting the "current record" than by using the menu items. I
could use a recordset object but how do I set that object to the current
record on the form?

Ryan

 

[Sylvain Lafontaine]

Personally, I use a macro with the action set to RunCommand and the command
set to DeleteRecord and a button on the form with its click event set to the
name of this macro.

I never searched another way because it's not a good idea in my opinion to
directly change the recordset bound to a form.

Maybe you can call directly this command from VBA, I don't know.

For the recordset, you can get a reference to the current recordset with
Recordset.Clone but I'm not sure; as I always got mixed with all these
commands like Recordset.Clone and .RecordsetClone (which are not the same
thing). The situation is even further complicated by the fact that
DAO.Recordset and ADO.Recordset object don't have the same rules on this
aspect. I never remember which one follow which rules.

 

[Ryan Langton]

I'm trying using a recordset clone and this appears to be working great.
However, all the fields fill in with #deleted and when I move off the record
(it's a continuous form) I get an error saying I can't update the record
because somebody else already did or changed the value of a key. Despite
the error, the record disappears and it works properly. I wonder is there
anyway to abandon the "update on exit" functionality that is apparently
causing this problem? Because I get the same error if I try to move focus
elsewhere on the form or do me.requery or me.refresh.

 

[Brendan Reynolds]

I prefer to use a SQL statement to delete records, Ryan. I work with MDBs
rather than ADPs, but I believe this technique should work in the same way
with an ADP ...

CurrentProject.Connection.Execute "DELETE * FROM Table1 WHERE ID = " &
Me!ID
Me.Requery

.... where 'ID' is the name of a field (or fields) that uniquely identify the
record.

No warning is given before the record is deleted using this technique, so
make sure your own safety checks are water-tight before using it.

 

[Ryan Langton]

I didn't even think about deleting by the unique ID using SQL! Thanks!
This should make the code easier.

 

[Ryan Langton]

Nevermind, fixed this problem by setting Me.Dirty = false. However, now it
is deleting the wrong record apparently so I'm going to try using SQL for
the delete rather than a recordset.

 

[Ryan Langton]

Hmm now I'm getting an error "Incorrect syntax near '*'". It looks like the
* in "DELETE * FROM" SQL is wrong.

 

[Brendan Reynolds]

Sorry Ryan, that syntax would have been correct for Jet, I forgot that
because you're using SQL Server you need to leave out the '*'. Just "DELETE
FROM TableName" etc. should do it.

 

[Vadim Rapp]

Hello,
You wrote on Fri, 15 Apr 2005 15:10:32 -0500:

RL> I didn't even think about deleting by the unique ID using SQL! Thanks!
RL> This should make the code easier.

for you - yes; but this will result in the form losing synchronization with
the underlying recordset. I.e. the form won't know that the data has
changed. You then have to immediately requery, which will move you to the
1st record.

Vadim Rapp

 

[Vadim Rapp]

Hello,
You wrote on Fri, 15 Apr 2005 15:11:17 -0500:

RL> Nevermind, fixed this problem by setting Me.Dirty = false.

Trying to fool Access? it will strike back.

Vadim Rapp

 

[Vadim Rapp]

Hello,
You wrote on Thu, 14 Apr 2005 08:59:05 -0500:

RL> I have a form that deletes a record if the first field is left blank by
RL> the user. I give the user a warning box "This field cannot be left
RL> blank or the record will be deleted. Do you want to delete the
RL> record?". The problem I'm having is after they say Yes to my warning,
RL> Access then gives them another warning "Are you sure you want to delete
RL> a record?" Is there a better way of doing this so they don't get the
RL> Access warning in addition to mine? Here is my code used to delete the
RL> record (within the condition that tests the user response).

RL> DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
RL> DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Use form event BeforeDelConfirm; assign Response=acDataErrContinue.

Vadim Rapp

 

[Brendan Reynolds]

I included a requery in my example, Vadim. Obviously the form must move to a
different record after the delete, as the record at which the form was
positioned no longer exists. The default behaviour will be to go to the
first record, but it is simple enough to change that behaviour if desired.
Here's an example that, after the requery, will go to the record that used
to be the next record after the deleted record, or the new record if there
is no 'next' record.

Obviously this is just quick-and-dirty example code - production code will
also need to check whether we are already at the new record before the code
runs.

My experience is mostly with MDBs rather than ADPs, but I did test this
(albeit only breifly) in an ADP before posting it.

Private Sub Command12_Click()

Dim varNextID As Variant

CurrentProject.Connection.Execute "DELETE FROM tblAccountChange WHERE
LogID = " & Me!LogID
DoCmd.GoToRecord acDataForm, Me.Name, acNext
If Not Me.NewRecord Then
varNextID = Me!LogID
Else
varNextID = Null
End If
Me.Requery
If Not IsNull(varNextID) Then
Me.RecordsetClone.Find "LogID = " & CLng(varNextID)
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If

End Sub

 

[Graham R Seach]

Ryan,

Is there a reason you haven't tried using the form's
BeforeUpdate/BeforeInsert event procedures. You can write code to check that
all the mandatory fields have been entered, and if not, issue your warning,
and set Cancel=True.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Ryan Langton]

What does Cancel do? I want the record to be deleted if the user changes
the first field to blank. The idea is that the user will be doing this to
existing data to delete the current record, just like you would do on many
forms using a "delete" button.

I would just use a standard delete button, but this is a sub-form that
includes many records and although a delete button is possible, it is not
practical because each record would have it's own button and it would make
my subform too clunky. I also tried just adding the record navigator bar
but for some reason the delete button on it is greyed out!!

 

[Sylvain Lafontaine]

The red button with a X on it is not a delete button but a Cancel Read
button. It's there for the purpose of stopping reading any further result
from long queries. When all the available data has been read, it becomes
greyed out because there are nothing left to cancel.