#Deleted# records in Form

S

So Call Me Crazy

I've struggled with this before. It's been quite a while since I programmed
in Access.

When you delete a record from the source of a form, how do you get it to
stop showing up as #Deleted#? I've tried Requery.... ??

TIA
 
K

Klatuu

Ok, Crazy,

Here is a function that will delete the current record and make that
annoying #Deleted# disappear.

If you have a Delete command button on the form, call it like this:

DelCurrentRec(Me)

I have this function is a Standard Module so I can call it from any form.

Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function
 
S

So Call Me Crazy

Works great if I have it as the click event for the button. BUT, I'm doing
some other stuff, so it needs to be done in code.

When the user clicks the button to delete a record, I give them a msgbox
asking if they're sure. If they respond in the affirmative, I append the
record being deleted to another table for auditing purposes. Then I delete
the record and close the msgbox.

When I call the function within the code, I'm getting an error of type
mismatch. Here's the code (VERY simple):

Private Sub cmdYes_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Append Out of Service Machine"
DelCurrentRec (Forms![Computer Refresh Table View])
DoCmd.Close acForm, "Retire Msgbox"
End Sub
 
S

So Call Me Crazy

Oh brother!! Was I being stupid!!! So, sorry. Works better if I remember
that a function is supposed to be returning a value!! DUH!

Long week...

Thanks for your help!! That function works great.
 
J

John W. Vinson

I've struggled with this before. It's been quite a while since I programmed
in Access.

When you delete a record from the source of a form, how do you get it to
stop showing up as #Deleted#? I've tried Requery.... ??

TIA
Click to expand...

I've only seen this when the data is stored in a non-Access database - dBase
in particular. Compacting the backend (or whatever the dBase jargon term for
garbage collection is) will solve the problem. Where is this data stored?

John W. Vinson [MVP]
 
S

So Call Me Crazy

I am experiencing a "feature", however.

If the user has applied a filter to the form, and they happen to be on the
last record within that filter and delete that record, we get an error
message. It performs the delete fine, and I'm not running into a #Deleted#
record. The error message implies that it can't go to the record specified.

I'm wondering if the RecordsetClone is not what we believe it to be when
applying a filter. ??
 
Top