Delete query with list box for parms modifies FIRST record

[Guest]

I have a table, tblConsultant, with 2 fields, ConsultantID (autonumber), and
CName (text). I created a form with a list box that is bound to those 2
fields. I then select the consultant I want to delete and press the delete
command button (which runs VBA code) and it deletes that record
perfectly...However, it also changes the first field's value in the list to
the ConsultantID autonumber. For example, the list box displays 3 names,
Kevin, Melissa, Tim. If I delete Tim, which has the autonumber of 14, the
list becomes 14, Melissa. So, Kevin's name changes to the value 14. It only
does this when I use the form to get the value that I want to delete. If I
hard code a value in, then it does not change the value of the 1st field. It
will do this whether I run the query from with VBA or within a predefined
query. I can not figure out why this is happening. I will post the VBA code...

Private Sub CmdDeleteCons_Click()
If Me.lstName <> "" Then
Dim sql As String
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Set cmd = New ADODB.Command
Set conn = CurrentProject.Connection
Set cmd.ActiveConnection = conn
sql = "DELETE ConsultantID FROM tblConsultant WHERE (((ConsultantID)=" &
me.lstname & "))"
cmd.CommandText = sql
cmd.Execute
conn.Close
Set cmd = Nothing
Set conn = Nothing
DoCmd.Close acForm, "frmAddCons", acSaveNo
DoCmd.OpenForm "frmAddCons"
Else
MsgBox "Please select the name of the consultant you would like to
delete from the list box.", , "MISSING CONSULTANTS NAME"
End If
End Sub

Also, I would like to know a better way to refresh the list box without
opening and closing the form.

Thanks in advance for any help!!

 

[Guest]

Well, I ended up deleting the listbox and creating a new one. It seems to
have fixed it. So, I think that I had the control source assigned to itself
and that is what caused the problem.

At any rate, how can I refresh the listbox from the command button without
closing the form and reopening it?

 

[Guest]

1. First, I think you probably want to "DELETE * FROM ... WHERE " rather
than "DELETE ConsultantID ..."

2. To requery the listbox, use the requery method.

me.lstName.Requery

after executing the DELETE command

HTH
Dale

 

[Guest]

Dale,

Thank you for the help. I made those two changes and they work great!
Thanks!
Tim