Delete button woes

[ChopinFan]

I have a form bound directly to a table and the form has a delete button. I was having issues with the regular menubar delete feature behaving unpredictably, so I coded it myself, but now, it's giving me an runtime error "Could not update; currently locked." If I go to another record, then return to the record I want to delete, however, it runs fine. What am I doing wrong?


Private Sub cmdDeleteRecord_Click()
Dim com As New ADODB.Command
Dim strSQL As String

If MsgBox("Are you sure you want to delete this?", vbYesNo, "Careful!") = vbYes Then
strSQL = "DELETE tblEmployeeMMTaskCat.*, tblEmployeeMMTaskCat.Employee_id, tblEmployeeMMTaskCat.Task_Category_id " & _
"FROM tblEmployeeMMTaskCat WHERE (((tblEmployeeMMTaskCat.Employee_id)= " & Me.Employee_id & ") " & _
"AND ((tblEmployeeMMTaskCat.Task_Category_id)=" & Me.Task_Category_id & "));"

With com
.CommandText = strSQL
.ActiveConnection = CurrentProject.Connection
End With
com.Execute
Me.Requery
DoCmd.GoToRecord , , acLast
End If

End Sub

 

[kjv1611]

Have you tried just using this?

Dim strSQL As String    
strSQL = "Your SQL Statement"
DoCmd.RunSQL strSQL

This will work if the table you're dealing with is inside of Access.

It may end up working the same, but I figured it's worth a try.

Another option would be to close your form, run the delete process, then re-open the form, b/c it sounds like there is some record-locking going on.

There are probably at least one or two more ways to do this, as well... One would be to use a DAO.Recordset to open the table, and delete the one record you wish to delete. I don't know if it would be as fast, but it might get around the recordlocks easier, dont' know for sure.

 

[ChopinFan]

Tried that, thanks, but it still has issues. It still displays the information in the form, but if you go off the record and back on again, you see the same old #Deleted. I thought about using a recordset but I keep thinking there's got to be an easier way.

 

[kjv1611]

What about running the SQL, then using:

Form.Requery

And see if that refreshes the data to current, which should eliminate the old #Dete data.