Delete records from a table using controls on a subform (1 Viewer)

[brainox]

I have a sub form that allows users to add staff to a project team, once added it populates a table which updates the subform showing the selected employee. I am trying to enable a delete function that allows users to remove an employee from the project team in the subform showing selected staff. Here is the code I have so far, but it doesn't work;

Private Sub Command4_Click()
Dim dbs As Database
Dim rs As Recordset
Dim sqlstr As String
Set dbs = CurrentDb
sqlstr = "DELETE tbl_CapexStaff.* FROM tbl_CapexStaff WHERE CAP_ID = Forms!frm_Switchboard.CAP_Live"
dbs.Execute (sqlstr)
End Sub

Any ideas
Cheers

 

[pr2-eugin]

Try,

sqlstr = "DELETE tbl_CapexStaff.* FROM tbl_CapexStaff WHERE CAP_ID = " & Forms!frm_Switchboard.CAP_Live

 

[missinglinq]

Why not simply

DoCmd.RunCommand acCmdSelectRecord      
DoCmd.RunCommand acCmdDeleteRecord

or, if you will sometimes start to enter a new record, then change your mind and want to 'delete' that incomplete record

If Me.NewRecord = False Then
    DoCmd.RunCommand acCmdSelectRecord      
    DoCmd.RunCommand acCmdDeleteRecord
 Else
   If Me.Dirty = True Then Me.Undo
End If

Linq ;0)>

 

[brainox]

Why not simply

DoCmd.RunCommand acCmdSelectRecord

[B]DoCmd.RunCommand acCmdDeleteRecord[/B]

or, if you will sometimes start to enter a new record, then change your mind and want to 'delete' that incomplete record

If Me.NewRecord = False Then

[B]   DoCmd.RunCommand acCmdSelectRecord      [/B]
[B]   DoCmd.RunCommand acCmdDeleteRecord[/B]
[B]Else[/B]
[B]  If Me.Dirty = True Then Me.Undo[/B]
[B]End If[/B]

Linq ;0)>

This worked great
Thanks a lot

 

[missinglinq]

Glad we could help!

Linq ;0)>