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

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

-- 
Dave Hargis, Microsoft Access MVP


"So Call Me Crazy" wrote:

> 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

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


"Klatuu" wrote:

> 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
> 
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "So Call Me Crazy" wrote:
> 
> > 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

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.

"Klatuu" wrote:

> 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
> 
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "So Call Me Crazy" wrote:
> 
> > 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

Good, glad I could help.
-- 
Dave Hargis, Microsoft Access MVP


"So Call Me Crazy" wrote:

> 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.
> 
> "Klatuu" wrote:
> 
> > 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
> > 
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "So Call Me Crazy" wrote:
> > 
> > > 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

On Fri, 11 May 2007 08:53:01 -0700, So Call Me Crazy
<SoCallMeCrazy@discussions.microsoft.com> wrote:

>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

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]

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.   ??

"Klatuu" wrote:

> Good, glad I could help.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "So Call Me Crazy" wrote:
> 
> > 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.
> > 
> > "Klatuu" wrote:
> > 
> > > 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
> > > 
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "So Call Me Crazy" wrote:
> > > 
> > > > 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