Delete current record

[arclight]

When I run this code to delete the form's current record, the Me.Requery throws error #3167 (Record is deleted) and leaves "#Deleted" in all of the fields. Any thoughts? Thanks in advance.

Patrick
=========================

Private Sub DeleteProfessional_Click()

Dim MyDatabase As Database
Dim rstProfessionals As DAO.Recordset

Set rstProfessionals = Form_Professional.RecordsetClone
If (rstProfessionals.RecordCount = 0) Then
MsgBox ("There are no Professionals to delete!")
Exit Sub
End If

Confirm = MsgBox("Delete the current professional?", vbYesNo)
If (Confirm = vbYes) Then
' Warn if current professional is lead
If ProfessionalLead.Value = True Then
Continue = MsgBox("You are about to delete the current lead professional." & vbNewLine & _
"Continue?", vbYesNo + vbExclamation)
If (Continue = vbNo) Then
Exit Sub
End If
End If

' Delete the current record
SqlStr = "DELETE * FROM Professional Where ID = " & Me.ID
Set MyDatabase = CurrentDb
MyDatabase.Execute SqlStr

' Refresh the form data
Me.Requery
End If

End Sub

 

[spikepl]

Form_Professional.RecordsetClone

The above syntax is not the recommended one for casual referral to items in a form, because it can have confusing sideeffects. Do not use it, unless you know the consequences. Use the syntax:

Forms!myFormName.myItem

Replace

MyDatabase.Execute SqlStr

by

MyDatabase.Execute SqlStr, dbFailOnError

Compact and repair your db

And finally, when pasting code use code tags. Click Advanced, select code and click #

 

[nanscombe]

I am assuming that this is a delete button which deletes the current record on the form with a bit of added common sense checking beforehand.

If so ...

When you run the Control Wizard to create a record delete button it puts the following code behind it:

Private Sub DeleteProfessional_Click()
On Error GoTo Err_DeleteProfessional_Click


    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord

Exit_DeleteProfessional_Click:
    Exit Sub

Err_DeleteProfessional_Click:
    MsgBox Err.Description
    Resume Exit_DeleteProfessional_Click
    
End Sub

We can adapt that for your purpose:

Private Sub DeleteProfessional_Click()
[COLOR="Red"]dim pvResponse as variant[/COLOR]
On Error GoTo Err_DeleteProfessional_Click

[COLOR="red"]pvResponse = MsgBox("Delete the current professional?", vbYesNo)
If (pvResponse = vbNo) Then goto Exit_DeleteProfessional_Click
[/COLOR]
' Warn if current professional is lead
[COLOR="red"]If ProfessionalLead.Value = True Then
    pvResponse = MsgBox("You are about to delete the current lead professional." & vbNewLine & _
"Continue?", vbYesNo + vbExclamation)

 If (pvResponse = vbNo) Then goto Exit_DeleteProfessional_Click

End If[/COLOR]

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord

Exit_DeleteProfessional_Click:
    Exit Sub

Err_DeleteProfessional_Click:
    MsgBox Err.Description
    Resume Exit_DeleteProfessional_Click
    
End Sub

 

[missinglinq]

I'd also use two Commands to do this, rather than using a SQL Statement, but this is going to give the OP a second warning/confirmation message, so I'd do it like this, to suppress the second, Access-generated message:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

As always, be sure to include the last line, turning Warnings back on!

Linq ;0)>

 

[Pat Hartman]

Let me just add that in general you shouldn't be running queries that update records in the form's current recordset. You will sometimes get strange conflict error messages because your form may have dirtied a record and your update/delete query is also trying to update it. In this case you are updating only a single record so there is absolutely no reason to run an update query. Just use the delete command recommended as a replacement for your query.

 

[arclight]

Using DoCmd.RunCommand does the trick nicely if you switch the warnings off. Thanks for sharing your expertise.

Also good to know why using update queries is not a good idea in this situation - effectively pulling the rug out from underneath myself.

 

[missinglinq]

Glad we could help!

Linq ;0)>