Deleting A Record

[rookery]

Hi Guys

I'm pretty new to all this business so you'll have to excuse my ignorance but this is my situation:

I have created a Form using a wizard to display the contents of a connected Table. I now want to install a Delete facility (Button) to remove the DISPLAYED record. However using the following code deletes the first record in the table, and not the record displayed in the Form:

Private Sub Delete_Click()
If MsgBox("Do You Want To Delete The Record?", 4, "Deletion?&quot = 6 Then
Set dbs = CurrentDb
Set MyRS = dbs.OpenRecordset("Customers", dbOpenDynaset)
Forms![Customers].Bookmark = MyRS.Bookmark
With MyRS
.Delete
End With
MsgBox "Record Deleted!!", 64, "No Turning Back!!"

dbs.Close
MyRS.Close

Else
Exit Sub
End If
End Sub

Was my first mistake to use the Wizard? How accurate is my code? I think I'm going wrong because I should have used a SQL query as the first argument after OpenRecordset, instead of just the Table name. Is this correct and if so any clues as to what the SQL should read?
Any help much appreciated.

 

[Aivars]

Hi!

There are lot of ways how to delete record. One way is using the record set:
Private Sub Delete_Click()
dim MyRS as recordset

If MsgBox("Do You Want To Delete The Record?", 4, "Deletion?&quot = 6 Then
Set MyRS = me.recordsetclone
With MyRS
.bookmark=me.bookmark
.Delete
End With
MsgBox "Record is Deleted!!", 64, "No Turning Back!!"

MyRS.Close
'Requery form
me.requery
End If
End Sub

Easier for programming beginners is to create command button using wizard (choose Record operation on wizard list then choose Delete record or something like this...). Add msgbox in command button On Click sub program, such :

Private Sub Delete_Click()
If MsgBox("Do You Want To Delete The Record?", _
vbyesno+vbdefaultbutton2, "Deletion?&quot = vbyes then
..........
... code generated by wizard ....
..........
endif
end sub

Aivars

 

[rookery]

Cheers Aivars it works s treat!! I dont think I was too far off. For instance I dont really understand why my line:

Forms![Customers].Bookmark = MyRS.Bookmark

is any differnet from your code:

MyRS.bookmark=me.bookmark

If you could explain it would be nice but failing that thanks for your help again! I'm sure I'll be requiring it again sometime in the future!!

 

[Aivars]

There is following difference:

You tried to bookmark form, but in this case it's needed that recordset should be bookmarked as form one.

Hereto you mistook trying to bookmark not equal recordsets:

dbs.OpenRecordset("Customers", dbOpenDynaset)
and
Forms![Customers] recordset.

Aivars