BeforeDelConfirm event for form

[LB]

Hi

The situation: to only allow subform record deletion if
there is more than one record in the recordset (i.e.
there must always be one record in the subform data).

I have used the form event BeforeDelConfirm, which works
fine, providing only one record from the subform is
selected:
- I check the number of records in the recordset (filter
based on key field data in main form).
- return the record count (unfortunately this record
count also includes the records that are being deleted).
- if the record count is less than or equal to 1, then I
set cancel = true, which cancels the record deletion (and
the MS Access 'delete record confirmation' prompt is not
displayed).

Solution: Either: I need to be able to return the number
of records that are being selected for deletion. OR: I
need to be able to check the recordset count after each
record deletion (the MS Access help topic infers that
this is possible ....."The Delete event occurs after each
record is deleted. This enables you to access the data in
each record before it's actually deleted, and selectively
confirm or cancel each deletion in the Delete macro or
event procedure. ")

Any help would be appreciated.

Thank you.

Regards LB

 

[Dirk Goldgar]

Hi

The situation: to only allow subform record deletion if
there is more than one record in the recordset (i.e.
there must always be one record in the subform data).

I have used the form event BeforeDelConfirm, which works
fine, providing only one record from the subform is
selected:
- I check the number of records in the recordset (filter
based on key field data in main form).
- return the record count (unfortunately this record
count also includes the records that are being deleted).
- if the record count is less than or equal to 1, then I
set cancel = true, which cancels the record deletion (and
the MS Access 'delete record confirmation' prompt is not
displayed).

Solution: Either: I need to be able to return the number
of records that are being selected for deletion. OR: I
need to be able to check the recordset count after each
record deletion (the MS Access help topic infers that
this is possible ....."The Delete event occurs after each
record is deleted. This enables you to access the data in
each record before it's actually deleted, and selectively
confirm or cancel each deletion in the Delete macro or
event procedure. ")

Any help would be appreciated.

Thank you.

Regards LB

How about just turning AllowDeletions off in the subform's Current
event, if the subform's recordset contains only 1 record? Something
like:

Private Sub Form_Current()

With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
Me.AllowAdditions = (.RecordCount > 1)
End With

End Sub

 

[Dirk Goldgar]

Me.AllowAdditions = (.RecordCount > 1)

Oops! Make that

Me.AllowDeletions = (.RecordCount > 1)

Sorry.

 

[LB]

Thanks Dirk

Good idea, but if the subform has two records, then the
alow deletions is of course 'on', and would allow for
deletion of the two records (I need there to be at least
one record in the subform).

Had a further 'play', as you do... and came up with...

Private Sub Form_BeforeDelConfirm(Cancel As Integer,
Response As Integer)
Dim numLeft As Integer 'the number of records left in the
subform after record deletion.
Dim rst As Recordset 'recordset for the subform.

'counts the number of records left after the deletion.
Set rst = Me.RecordsetClone
numLeft = rst.RecordCount

'if there are no records left, then deletion is
cancelled.
If numLeft = 0 Then
MsgBox "For each Country, there must be at
least one Language Association recorded. ", _
vbExclamation, "Unable to delete
Language Association record(s)..."
Cancel = True
End If

rst.Close 'this is probably not required
for the recordsetclone, but it was doing dodgy things
without it.
Set rst = Nothing


End Sub

The above worked 90% of the time, the other 10% of the
time, the object was "not set" - in MS Help it mentioned
that after the form is closed or recordsource property
changed, then an error may occur (moving between records
may constitute a recordsource change for the
recordsetclone perhaps??

I suspect my original plan may in fact still be the best
option ... all I have to do is find a way to determine
the number of records that have been selected for
deletion!

Thanks for your help.
Regards LB

 

[Dirk Goldgar]

Thanks Dirk

Good idea, but if the subform has two records, then the
alow deletions is of course 'on', and would allow for
deletion of the two records (I need there to be at least
one record in the subform).

Shucks, you're right.

Had a further 'play', as you do... and came up with... [snip]
Set rst = Me.RecordsetClone [snip]
rst.Close 'this is probably not required
for the recordsetclone, but it was doing dodgy things
without it.

I don't know what "dodgy things" were happening, but you should not
close the form's recordsetclone. I'd expect dodgy things to happen if
you do that.

The above worked 90% of the time, the other 10% of the
time, the object was "not set" - in MS Help it mentioned
that after the form is closed or recordsource property
changed, then an error may occur (moving between records
may constitute a recordsource change for the
recordsetclone perhaps??

I suspect my original plan may in fact still be the best
option ... all I have to do is find a way to determine
the number of records that have been selected for
deletion!

Does the form's SelHeight property tell you what you want? In the
Delete event, but not in the BeforeDelConfirm event, it has the number
of records currently selected.