prevent multiple records deletion in a form

  • Thread starter Thread starter alex
  • Start date Start date
Multiple how? Cascade delete where you delete all related records when the
current record is deleted; you delete more than one record while the form is
open, but do it one at a time (single form view); or you select multiple
records at a time (continuous form view or datasheet view) for deletion?

If the latter, I just tried the following and it appeared to work. Create a
Form level variable called intDeleteCount As Integer then

Option Compare Database
Option Explicit
Dim intDeleteCount As Integer

Private Sub Form_Current()
intDeleteCount = 0
End Sub

Private Sub Form_Delete(Cancel As Integer)
If intDeleteCount > 0 Then
Cancel = True
End If
intDeleteCount = 1
End Sub

What this does is set intDeleteCount to 1 the first time you run through the
Delete event. You run through this event for each record you attempt to
delete. The event will be cancelled for subsequent trips through the event
until the current delete is completed. When the current delete is completed,
the record after the deleted record becomes the current record, causing the
Current event to fire and reset our flag.
 
To cancel the delete if the user has selected multiple records in a
continuous/datasheet form:

Private Sub Form_Delete(Cancel As Integer)
If Me.SelHeight > 1 Then
Cancel = True
MsgBox "Multi-row deletion not allowed."
End If
End Sub
 
Hi.

I found both of these responses useful (thanks). Just recording my solution incase it helps someone else.

I needed to ignore 'delete record' (repeated msgbox) when user presses CTRL + A (by mistake) within formview form. I also tried a boolean form variable (with the reset in form_current), however this seemed to change (boolean) values for every alternate delete. The following worked well:

If Me.SelHeight > 1 Then
Cancel = True

fvDelCount = fvDelCount + 1
If fvDelCount = 1 Then
MsgBox "not able to delete multiple selected records... bla bla"
ElseIf Me.SelHeight - 1 = fvDelCount Then
'resets counter and turns of multi record selection
fvDelCount = 0
Me.SelHeight = 0
End If
else
'continue with code for form_delete...
endif

Regards
Lorraine
 
Back
Top