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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|