Delete still shows the deleted record

[Guest]

I have a form with a button to delete a record from a table. The delete
button works however the form doesn't advance to the next record or go back
to the previous record. The record no longer exists in the table but it is
still being shown in the form. I've tried adding commands to go to the next
record, but it never appears to get to that command. How do I get rid of the
deleted record and show the next record.

Private Sub Delete_Command_Click()
On Error GoTo Err_Delete_Command_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
' does not get to the following command
DoCmd.GoToRecord , , acNext
Exit Sub
Err_Delete_Command_Click:
If (Err.Number <> ACTION_CANCELLED) Then MsgBox Err.Description
Me!Name_Text.SetFocus
End Sub

Your help will truly be appreciated,

kitters
--

 

[Guest]

Here is a sub you can drop into a standard module and call it from any form.
It takes care of the problem.
Put this in the click event of your "DELETE" command button:
Call DelCurrentRec(Me)

Public Sub 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

DelCurrentRec_Exit:

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

DelCurrentRec_Error:

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

End Sub

 

[Guest]

I tried it and it produces an error, but it does delete the record. It's
difficult to debug cause if I step through it doesn't occur. I'm setting
break points to try and figure out where it's dying but I have to keep
closing and reopening Access cause it hangs.

Error -2147217864 Row cannot be located for updating

 

[Guest]

I don't think Access is really hanging. Comment out this line:
Application.Echo False
until you get it working.
That line turns off any screen updating and will make it appear as if Access
is not responding.

As to your error, I don't know what may be causing it. The code I provided
is something I use throughout my applications.

 

[Guest]

Hi Klatuu,

I've been trekking around the web and it looks like the problem maybe
because my form is based on an ADO recordset. I'm not experienced with
database programming - that is I'm a hack and learning as I go. This is a
new SQL Server DB and I'm borrowing code from the previous SQL Server DB
(same data, new imporved DB structure). Many of the problems I'm
encountering are because the previous forms were built in a different old
version of Access. Thanks very much for your help. My problem may not be
solved, but you've helped me get on the right track.

Regards,

Kit

 

[Guest]

You may be right. The code I provided is based on DAO.

I would think, however, that with some modification for ADO, the basic
concept should work.

 

[Guest]

I'll let you know how things go.

 

[Guest]

Klatuu,

You got me on the right track and actually helped me solve another bug. The
problem for the delete was a trigger in the table that referenced another
table. The trigger made it a one-to-many relationship that an Access to SQL
Server can not handle. This in turn led me to the solution for the other bug
- I couldn't add a new record to a form based on a query table. The query is
a simple INNER join between 2 tables. The form based on this query works in
an Access db, but not for a data source based on Microsoft SQL Server. I
probably should have mentioned this aspect of my form in my original
question. Sorry, but wouldn't you think that since both are Microsoft
products that there'd be better communications between the 2 applications?
Thank you so much you've been a saviour. I can now go on vacation without
the prospect of returning to work on problems I've yet to figure out. I'm
gonna head down to the New Orleans Jazz and Heritage Festival without that
monkey on my back.

Regards,

Kit