Stop #deleted from showing in a subform

[Rick Allison]

Access 2000 base code

I have a form with four subforms. I have code that deletes one row from
each of two different subforms. When I do that I see #deleted displayed in
the subform's controls briefly then the controls are blank. I don't want to
see the #deleted at all.

I've tried docmd.echo and application.echo. That does not work.
I tried to requery on the subform, that worked, no #deleted displayed, but
doing that created a new row in the database that I just deleted.
Therefore, the requery did not work.

Is there anything I can do that will hide the #deleted from being displayed?
It looks unprofessional.

Thanks,

Rick

 

[Jeanette Cunningham]

Hi Rick
here are my notes on delete and #deleted from previous discussion group
posts.
It appears as an answer - but I don't have the original question.
You might like to try this approach to see if it resolves your problem.

Continuous form based on junction table.
First, undo the form (Me.Undo) BEFORE attempting to delete.

Second, delete the record from the form's recordset, not directly from the
table.

If IsNull(Me.txtWebComType) Then
Dim strMsg As String
Dim strTitle As String
Dim strSQL As String
strMsg = "Would you like to delete this entry?"
strTitle = "Delete WebCommunication Record"
Me.Undo ' do this unconditionally
Cancel = True
If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _
= vbYes Then
Me.Recordset.Delete
End If
End If

Instead of
Me.Recordset.Delete
try
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.Delete
End With

The method using RecordsetClone should work with all versions of Access and
not require a Requery. Opening a separate Recordset will still require a
requery (as will a SQL delete) because the form's recordset is not being
used.

Yes - basically the user has four choices (you may not wish to offer all of
them):

1. Do nothing and undo the change to the record.
2. Delete the junction record and leave the WebComs record intact
3. Delete the junction record and the WebComs record if it is no longer
referenced.
4. Delete all related junction records AND the WebComs record

RecordsetClone should be used only to delete the junction record that is
currently displayed in your subform, simply to avoid the #Deleted display.
You could do a SQL delete followed by Me.Requery if you prefer (remembering
the Me.Undo first, of course!)

If you ALWAYS want to delete ALL the related junction table records, then
forget the RecordsetClone.Delete and just execute the SQL delete followed by
Me.Requery. (The cascade deletes will take care of the rest.)