Delete button for continuous form from searching form

[Gareith Stanley]

Hi there

I have an access form that allows the user to browse records (< and > buttons) sorted by another field within that table (eg, all entries under a staff group, all entries under a student group).

I have added a delete button to the form using code supplied by another user of the forum (thanks to PEB):

Dim rst As Recordset
 
Set rst = Me.RecordsetClone
rst.Delete
rst.Close
rst.Requery

From what I can tell it works great on the first record, but any subsequent records I get the 'no current record' popup. I need it to work multiple times if other entries need to be deleted. (I also want it to give an 'are you sure yes/no' popup but that's a different issue)

Can anyone help or is more info required? :)

Cheers
Gareith

[MMcCarthy]

Hi Gareith

The following will give you the popup message ...

Dim rst As Recordset
Dim rslt As Integer
 
Set rst = Me.RecordsetClone
rslt = Msgbox "Are you sure you wish to delete this record?", vbYesNo
If rslt = vbYes Then
   rst.Delete
   rst.Close
   Me.Requery
End If
 

Also you were requerying the recordset and not the form. See how this works now. It may require further tweaking.

Mary

[Gareith Stanley]

Thanks Mary!

The popup code works great.
The outstanding issue now was if the returned filter records was 1 of 20 then it showed the next record fine, but if it was 1 of 1 then the code failed because the only record had been deleted. I've just made the delete record routine exit the form completely which is working fine with the users.

Thanks again :)
Gareith

[MMcCarthy]

Thanks Mary!

The popup code works great.
The outstanding issue now was if the returned filter records was 1 of 20 then it showed the next record fine, but if it was 1 of 1 then the code failed because the only record had been deleted. I've just made the delete record routine exit the form completely which is working fine with the users.

Thanks again :)
Gareith

You're welcome.

[NeoPa]

Very picky point, I know, but you probably want the rst.Close line outside of the If section.

Dim rst As Recordset
Dim rslt As Integer
 
Set rst = Me.RecordsetClone
rslt = Msgbox "Are you sure you wish to delete this record?", vbYesNo
If rslt = vbYes Then
  rst.Delete
  Me.Requery
End If
rst.Close
 

[MMcCarthy]

Good point Ade.