Trouble with deleting a record with vba

[Jaymin]

Hope you can help,i know this should be simple but it is bugging me,
I have a form with a two cacading combo boxes, i have a button to delete the record, filtered by the second combo box. i use DoCmd.RunCommand acCmdDeleteRecord when the button is clicked.
it delets a record but not the one selected, it deletes the first one in the combo box list

Public Sub cmdExitAndDelete_Click()
On Error GoTo Err_cmdExitAndDelete_Click

If IsNull(cboStation) Then
MsgBox ("You have to enter data first!")
Me.cboStation.SetFocus
Cancel = True

ElseIf IsNull(cboStaff) Then
MsgBox ("You have to enter data first!")
Me.cboStaff.SetFocus
Else
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "frmDelete_Staff"
End If

Exit_cmdExitAndDelete_Click:
Exit Sub

Err_cmdExitAndDelete_Click:
MsgBox Err.Description
Resume Exit_cmdExitAndDelete_Click e

End Sub

I have used this option with the same result

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Peter,

 

[joeKra]

Please explain more how your form looks like....
how will the Delete cmd determine which record to delete , will the combo box filter the populated records??

 

[Jaymin]

Thanks JoeKra,
I have attached the form, basically i have two cascading combo boxes, the first selects the station, the second you select the staff at that station, this is achieved by the staff combo being filtered by the first station combo box.
I have three buttons, one to close the form without deleting anything, the second is to delete the selected staff and close the form, see code from previous post, the third is to delete the staff but leaves the form open.

SELECT tblStation.IDStation, tblStation.StationName FROM tblStation;

this is the rowsource from the station combo

SELECT qryName.IDStaff, qryName.Name, qryName.IDStation FROM qryName WHERE (((qryName.IDStation)=forms!frmDelete_Staff!cboStation));

This is the rowsource for the staff combo

SELECT tblStaff.IDStaff, [surname] & ", " & [firstname] AS name, tblStaff.IDStation FROM tblStaff WHERE (((tblStaff.IDStation)=forms!frmDelete_Staff!cboStation));

This is the rowsource for the staff combo not using a query

SELECT tblStaff.IDStaff, [surname] & ", " & [firstname] AS Name, tblStaff.IDStation FROM tblStaff;

This is the record source for the form

The delete works except is deletes the first record instead of the one selected by the staff combo box.

Hope i provided enough information that you can help me,

Peter

 

[joeKra]

Try this one:

Currentdb.Execute "DELETE * FROM tblStaff WHERE  tblStaff .IDStaff  = " &forms!frmDelete_Staff!cboStaff&";"

 

[Jaymin]

joeKra,
The code you gave me works just find, i would like to thank you very much for you time and effort in helping me with this.
Peter