Help with using command button to delete a record from a table

[Zeunasc]

I have a table called tEmployeeData that only has one column
(presently) called EmployeeName. I built a form called
fDeleteEmployeeName that has a combo box and a command button. The
combo box brings up a list of all the employee names, and the command
button deletes the record out of the table that is referenced in the
combo box.

My problem is that once you delete the record, the combo box still
shows the name that was deleted. I think what would be easiest would
be to pop up a box saying xxx has been deleted and when the user
presses OK, the form closes. I have no idea how to code this. Also,
if you have a better way to do this, please let me know that too. Any
help would be appreciated!

I have this code on the command button:

Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click

Dim stDocName As String

stDocName = "qDeleteEmployee"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdDeleteEmployee_Click:
Exit Sub

Err_cmdDeleteEmployee_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteEmployee_Click


End Sub

And qDeleteEmployee is coded as follows:

DELETE *
FROM tEmployeeData
WHERE tEmployeeData.EmployeeName=forms!fDeleteEmployeeName!
cDeleteEmployeeName;


TIA!
Tim

 

[Guest]

try

me.myComboBox.Requery

after your DoCmd statement

a while since I've done it but should work I think

Finnie

 

[Zeunasc]

This does ALMOST everything I need. I added the line where you
instructed. Now, when I hit the button and delete a name, the name
still appears in the combo box as the selected name, however, it does
NOT appear in the drop down list.

Is there a way to say "after the DoCmd, reset the combo box to empty
and then Requery the combo box to get the updated values"?

Thanks!
Tim

 

[Guest]

try the following: (just replace the generic references with your own)


Dim db As Database
Dim wrk As Workspace
Dim strSql As String, strInput As String

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb()

strInput = Me.Combo0
strSql = "DELETE Table1.*, Table1.Name FROM Table1 WHERE " _
& "Table1.Name = " & "'" & strInput & "'" & ";"


db.Execute strSql, dbFailOnError


Me.Combo0 = ""
Me.Combo0.SetFocus


This works.

Finnie