Delete table record

[Prayder]

I have created a form and need to be able to delete employees from a table. I built a command button using the wizard and this is the current vb code-

   Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
Exit_cmdDeleteEmployee_Click:
    Exit Sub
Err_cmdDeleteEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteEmployee_Click
    
End Sub

But when I click on the button I get a message saying it would create duplicate data in the table. What am I doing wrong?

 

[rzw0wr]

How are you telling Access what to select?

Dale

 

[billmeye]

On the surface your code looks good but I'm guessing you either have other code running in other events that is modifying the record prior to your deletion attempt creating the violation or you are trying to delete a new record that violates your data rules. Try adding this to your code:

If Me.NewRecord Then
Me.Undo
Else
 DoCmd.RunCommand acCmdSelectRecord
 DoCmd.RunCommand acCmdDeleteRecord
End If

This will undo a newly created record instead of trying to delete it and will bypass any field rules that you may have been inadvertently violating.

 

[Prayder]

In this form I have tabbed documents and when I delete an employee in the tabbed document I'm in... it works great but when I click on the other tabbed documents that field where the employee was shows "#deleted" all the way across the document where there is a field.

 

[billmeye]

Are all the controls bound to fields on the same table or are you using subforms on each of the tabs that have a master/child link? If the later you may simply need to add an Me.requery to cycle the records.

 

[rzw0wr]

requery the form.

Dale

 

[Prayder]

the controls are bound on the same table and I am using subforms.


Still kind of new so not sure what you mean by requery the form?

 

[billmeye]

To your code add Me.requery:

   Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord

[COLOR="Red"]Me.requery[/COLOR]

Exit_cmdDeleteEmployee_Click:
    Exit Sub
Err_cmdDeleteEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteEmployee_Click
    
End Sub

 

[Prayder]

The record gets deleted but in the other tabs that have subforms in them it still shows #Deleted in every box there is a field for.

 

[billmeye]

Instead try Me.Refresh and if that doesn't do the trick try docmd.GoToRecord,,acFirst

 

[Prayder]

Just wondering here.... will i need to type the same code in every subform even though I am deleting the employee from the first subform?

 

[Prayder]

what is interesting though is that when I close the database all together and reopen the main form that has the subforms in it... the record does not show up in any of the tabs...

 

[jdraw]

Prayder,
When you close the database, all forms and data are back "in sync". The record has been deleted, so no other records match the "deleted" record, so no other data appears in forms/subforms.

The #deleted you saw is just saying that -- the record that was here has been deleted and the form/subform has not been refreshed/requeried. Access is saying -- I'm showing you #deleted because this record or its related info is being removed from a table; and if you refresh the form/table I'll get rid of this little note I'm showing.

As an aside, many orgs would NOT delete the record physically (REMOVE IT FROM THE TABLE). They would have a flag/boolean on the record to show 'logically deleted". If set, the record would be excluded from ongoing operations. The record would be "logically removed", but physically still in the database. There may be some history etc related to that Employee, and it might be needed in future- so why delete it physically?

 

[Prayder]

We use this db just as a look up of sorts. the employee name and related info is kept on a separate db as well as hard copies of each employee.