cancel update

[Bill H.]

On a form, I have several required fields. I use the if/else construct to
test the required fields to see if data has been entered, and run this as a
BeforeUpdate event.

I also have a delete record button on the form (DoCmd.RunCommand
acCmdDeleteRecord). If, part way through data entry, they decide to delete
the record, when the docmd is run, it also causes the beforeupdate event to
fire, and gives the user those messages about the required fields. After
dismissing the message boxes, then the record is deleted.

How do I cancel the update so that the DeleteRecord command can run
unfettered? I also need to be able to run the DeleteRecord button on
records that have been entered in the past and thus have all their required
fields filled in.

Thanks.

 

[missinglinq via AccessMonster.com]

You shouldn't be using DoCmd.RunCommand acCmdDeleteRecord to cancel a new
record! The record save is being forced by this, which is why your
BeforeUpdate event is being triggered. Instead, use Me.Undo behind the button
to dump the new record! This same button can be used if the user edits a
record's data and then decides he/she doesn't want the edit to stand.

 

[Bill H.]

OK, that sounds reasonable, but can the same button then also be used to
delete a record previously entered into the database?

I'm thinking not...

 

[John Spencer]

Here is a bit of code that I have in a function that deletes a record from a
form. I have the code in a standard module and can call it from any form or
subform.

From a form or subform you can call the code with a line Like

sCmdDelete Me

IF you wish to include a bit of detail, you can use the optional strCaption
to pass in a record number or other information such as the kind of record
you are deleting.

Public Function sCmdDelete(frmAny As Form, Optional strCaption As String) As
Boolean
'Delete the currently selected record on a form
' 8/19/2005 John Spencer
'---------------------------------------------------------------

Dim tfAllowDeletions As Boolean
Dim tfReturn As Boolean

tfReturn = True

On Error GoTo ERROR_sCmdDelete

With frmAny
If strCaption = vbNullString Then
strCaption = .Caption
End If

If strCaption = vbNullString Then
strCaption = .name
End If

If .CurrentRecord > 0 Then 'make sure there is a record

If .NewRecord = True And .Dirty = False Then
Exit Function
End If

If MsgBox("Delete selected " & strCaption & " record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then

If .NewRecord = False Then
tfAllowDeletions = .AllowDeletions
If .AllowDeletions = False Then .AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
.AllowDeletions = tfAllowDeletions
Else
.Undo
End If 'Delete existing records only

End If 'Confirm delete
End If 'Current Record
End With

EXIT_sCmdDelete:
sCmdDelete = tfReturn
DoCmd.SetWarnings True
Exit Function

ERROR_sCmdDelete:
Select Case Err.Number
Case 2501
'action cancelled
Case Else
MsgBox Err.Number & ": " & Err.Description, , "Error:
modButtons.sCmdDelete"
End Select

tfReturn = False

Resume EXIT_sCmdDelete
End Function

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

 

[Bill H.]

Thanks.

I get a compile error on this line:

MsgBox Err.Number & ": " & Err.Description, , "Error:
"modButtons.sCmdDelete ""

 

[John Spencer]

That and the next line should all be one line.

'============ All one line =====================
MsgBox Err.Number & ": " & Err.Description, , "Error:modButtons.sCmdDelete"
'============ All one line =====================

Or you can fix it by adding the continuation character to the line.
That is a space followed by an underscore.


MsgBox Err.Number & ": " & Err.Description, , _
"Error:modButtons.sCmdDelete"


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

 

[Bill H.]

Yes, I do have it as all one line.

Ah, but I see some differences in what I have and what you just did.

That's works better!