Record is deleted#

[calvinle]

Hi,

I have a form set to continuous, and on the Details bottom part, I have added some field so that if they make changes on those field, it will also update the selected record. Kinda a split form way, but I prefer continuous because of its layout.

My concern is, when I filter that continuous form, and then delete record, sometimes I see the prompt "Record is deleted#". Seems like I am trying to access a deleted record problem.

Here is the code I am using to delete the record in the continuous form:

Me.Form.Recorsetclone.Bookmark = Me.Form.Bookmark
Me.Form.Recorsetclone.Delete
Me.Form.Refresh

Am I missing anything that's why I get that error sometime?

Thanks

 

[boerbende]

What happens if you try .requery in stead of .refresh?

 

[MarkK]

First, you probably don't need to use Me.Form, since Me probably already IS the form you mean, so that leaves . . .

Me.Recorsetclone.Bookmark = Me.Bookmark
Me.Recorsetclone.Delete
Me.Refresh

Second, you probably don't need to use the clone, since you already have the record at Me.Bookmark, so that leaves . . .

Me.Recordset.Delete
Me.Refresh

Then, as boerbende suggests, use Requery instead. Refresh saves and/or retrieves data for the existing records in the recordset, whereas Requery rebuilds the recordset from scratch. In the latter case, deleted records are removed, and records added by other users would appear. Which leaves . . .

Me.Recordset.Delete
Me.Requery

Does that solve it?

 

[calvinle]

I have tested:

Me.Recordset.Delete
Me.Requery

But the problem with this is because I have a command button on each record to delete that record, so when the record gets deleted, it will focus me another record, so when i click on that command, it wont delete that current record.
Unless I add the bookmark to it.

So my new code is:

Me.Recordset.Bookmark = Me.Bookmark
Me.Recordset.Delete
Me.Requery

I didn't see any error yet with this, but my concern is about the clone. reason I use the clone was for the filtering part. Because in this continuous form, I can also filter some record out, so I guess since the selected record will be deleted, it's useless to use the clone?

I will keep in loop if i ever encount the same error.

Thanks for the help.

 

[The_Doc_Man]

it's useless to use the clone?

I won't swear to this on a stack of bibles (not that I would anyway) but I believe that if you had done a

Set myRecSet = Me.Recordset.Clone

then after deleting a record, you would have to requery EVERY such copy of the recordset because the pointers are still in-place for each one. Note that if a recordset had been opened as a Dynaset with the Consistent option, the requery would not be needed.

 

[calvinle]

I came into a problem here.
By using the recordset only, after deleting the record, and remove the filter, it will prompt the error "No current record", but if I use the recordsetclone, there is no "No current record" error.
I had to put back the recordsetclone.

 

[arnelgp]

when you issue a Requery command all bookmarks are destroyed and re-created with new one. so the one you have saved becomes an invalid after issuing a requery command.
you need to save everything, including your filter, the previous/next record so you can go back to where you where in the recordset after issuing a requery.

 

[arnelgp]

when you issue a Requery command all bookmarks are destroyed and re-created with new one. so the one you have saved becomes an invalid after issuing a requery command.
you need to save the previous/next record so you can go back to where you where in the recordset after issuing a requery.

Private Sub Command1_Click()
    Dim varBM As Variant
    Dim pkKey As Variant
    Dim strFilter As String
    Dim rs As DAO.Recordset
    Dim bolOK As Boolean
    Set rs = Me.Table1_subform.Form.RecordsetClone
    With rs
On Error GoTo exit_code
        varBM = Me.Table1_subform.Form.Bookmark
        .Bookmark = varBM

        'move to next record
        .MoveNext
        bolOK = Not (.EOF)
        If bolOK Then pkKey = rs![id]
        If Not bolOK Then
            .Bookmark = varBM
            'move previous record
            .MovePrevious
            bolOK = Not (.BOF)
            If bolOK Then pkKey = rs![id]
        End If
        'go to bookmark again to delete
        .Bookmark = varBM
        .Delete
        .Requery
        'goto previous/next record, if any
        If bolOK Then
            .FindFirst "[id] = " & pkKey
            Me.Table1_subform.Form.Bookmark = rs.Bookmark
        End If
exit_code:
        
        If Err.Number = 3021 Then
            'no current record, ignore
        ElseIf Err.Number <> 0 Then
            MsgBox Err.Number & ", " & Err.Description
        End If
        .Close
    End With
    Set rs = Nothing
End Sub