Delete Problem!

[anthony_pacitto3]

okay, i have a problem with deleting items in my database...what happens is...lets say i have 3 items in my listbox and i decide to delete item # 2...it will delete it, but then when i restart my program it shows that it has deleted item #1 and item # 2 is still there what is happening? and how do i fix it to delete the correct/selected item?! please help! here is the code used:


If MsgBox("Are you sure you want to delete " & txtName.Text & "?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
Set MyData = OpenDatabase(App.Path + "\PAF.mdb")
SQL = "SELECT * FROM PAF"
Set MyRecord = MyData.OpenRecordset(SQL)
MyRecord.Delete
lstNames.RemoveItem lstNames.ListIndex

Else
Exit Sub
End If

[RobDog888]

Are you doing this inside Access' VBA IDE?
You should be executing a sqldelete statement instead.

[Hack]

Try

SQL = "DELETE FROM PAF WHERE thefieldname = '" & txtName.Text & "' "
MyData.Execute SQL

[RobDog888]

SQL = "DELETE FROM PAF WHERE thefieldname = '" & txtName.Text & "' "
MyData.Execute SQL

That wouldnt be preferred (may be invalid syntax too) if its in Access VBA which is why I was asking. For the possibility of it being Access VBA which is most possible as hes using DAO....

Application.CurrentDb.Connection.Execute "DELETE PAF.* FROM PAF WHERE thefieldname = '" & txtName.Text & "';", dbSeeChanges

Now if this is a multiuser database then the last option could be changed to reflect your best needs. I set it to generate an error if another user is modifying one or more of the records to be deleted.