A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#Deleted# records in Form



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2007, 04:53 PM posted to microsoft.public.access.forms
So Call Me Crazy
external usenet poster
 
Posts: 40
Default #Deleted# records in Form

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
  #2  
Old May 11th, 2007, 06:26 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default #Deleted# records in Form

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

  #3  
Old May 11th, 2007, 08:23 PM posted to microsoft.public.access.forms
So Call Me Crazy
external usenet poster
 
Posts: 40
Default #Deleted# records in Form

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

  #4  
Old May 11th, 2007, 08:39 PM posted to microsoft.public.access.forms
So Call Me Crazy
external usenet poster
 
Posts: 40
Default #Deleted# records in Form

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

  #5  
Old May 11th, 2007, 08:52 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default #Deleted# records in Form

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

  #6  
Old May 11th, 2007, 10:08 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default #Deleted# records in Form

On Fri, 11 May 2007 08:53:01 -0700, 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


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]
  #7  
Old May 18th, 2007, 08:32 PM posted to microsoft.public.access.forms
So Call Me Crazy
external usenet poster
 
Posts: 40
Default #Deleted# records in Form

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.