Preventing Multiple Records from being Deleted via Record Selector

[dch3]

I have a continuous form where the Record Selectors have to be displayed on
the form. I need to prevent a user from selecting multiple records and then
deleting them. The ability to delete a record needs to be at the single
record level as there are specific conditions under which a record can be
deleted. (I've got a command button in the form's detail section that deletes
the record if allowed.)

I'm pretty certain that you can't use VBA to check each record to be deleted
and then abort the delete on a record by record basis. So I was thinking
about setting the form's AllowDeletions property to FALSE by default and then
in the DELETE command button change the value to TRUE to allow the single
record to be deleted.

Ideas?

 

[Jeanette Cunningham]

dch3,
avoid using the form's Allow Deletions property, once you allow edits,
deletions are allowed as well.

To prevent deletions, you can code the On Delete event of the form.
The Delete event occurs when the user performs some action, such as pressing
the DEL key to delete a record, but before the record is actually deleted.

Declare a module level delete variable like this
Dim bolOKDelete as Boolean

by putting the above line at the top of the form's module, just below the
line that says Option Explicit.

Code the form's delete event like this

Private Sub Form_Delete(Cancel As Integer)
Cancel = Not bolOKDelete
End Sub

For your delete button, you set bolOkDelete = True when you are ready to
delete the record.
Once the record has been deleted, you set bolOKDelete back to False (this
happens in the code for the Click event of your delete button).
The only way a record can be deleted is by clicking your Delete button.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[John Spencer]

Jeanette,
Pardon me, but I have never been able to delete a record on a form if
the form's Allow Deletions property is set to false.

Can you give me a scenario where you have Allow Deletions set to false
(no) and you can delete a record from the form?

I can delete the record using a query and executing the query. However,
that is not deleting it from the form.

Yours in confusion,
John

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

 

[John Spencer]

That is the way I do it. I have a function that I use. I just call the
function with

sCmdDelete Me
or
sCmdDelete Me, "Name record type"

Copy and paste this into a module and you can call it from any form or
subform



Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form
' 1/14/04 - JPS Added code to change deletion property on form
' to allow deletion
'---------------------------------------------------------------
' 8/19/2005 spencer
' Code Change: Add optional strCaption to code to allow specific
' user message
'---------------------------------------------------------------

Dim tfAllowDeletions As Boolean
'1/14/04
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 'Nothing to delete
End If

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

If .NewRecord = False Then
'Delete existing record
tfAllowDeletions = .AllowDeletions
If .AllowDeletions = False Then .AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
.AllowDeletions = tfAllowDeletions

Else
'Unsaved new record cannot be deleted so just undo it
.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

 

[Jeanette Cunningham]

John,
following advice from these discussion groups I have always believed that if
a form has allow edits set to true, then setting allow deletions to false
will not prevent a record from being deleted. I admit I have never
explicitly tested this, I just followed it and assumed it was true.
Thanks for the correction - it is good to set the record straight for those
who read these replies and use the advice for their own databases.
Now if the posts that I read about setting allow deletions to false had been
corrected by a knowledgeable person like yourself several years ago, we
wouldn't have this confusion.


Jeanette Cunningham -- Melbourne Victoria Australia