Deleting a record using DAO.Recordset 1

[Tofias1]

I again would like to say hello to anyone out there willing to look at my code. What it is supposed to do is delete a specific name out of a table once executed. It works, however, it only deletes the 1'st value in the field and not the one specified. I know that I can delete the data by using a form but I would like to be able to do it from the parent form instead. If anyone has any ideas they will all be greatly appreciated.

Thanks,
Tofias1

Private Sub Delete_Name_Click()
Dim stranswer As String
Dim rst As DAO.Recordset


stranswer = InputBox("Please Enter the Name which you would like to delete.&quot

If stranswer = Names.Value Then
Set rst = CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

With rst
.Delete
DeleteRecord = fldNames
End With
rst.Close

stDocName = "Table2" 'opens form and closes form to reset the list box.
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
stDocName = "Table2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If


End Sub

 

[FoxProProgrammer]

Hi Tofias1,

You need to find the record to delete before you execute rst.Delete. It seems to me that your code would delete the first record in the table, or where ever the record pointer happens to be positioned at the time. Try this instead. "Name" is the name of the field in your table that contains the person's name.

rst.FindFirst "Name = " & Chr(34) & stranswer & Chr(34)
If Not rst.NoMatch Then
rst.Delete
Else
MsgBox "Record selected for deletion not found."
End If

By the way, what does DeleteRecord = fldNames do? DeleteRecord looks like a variable but it isn't defined. You will also need to requery the form or control after the data is deleted so the changes will be visible.

Best regards,

dz
dzaccess@yahoo.com

 

[Tofias1]

dz,

Thanks a lot that worked great. I knew it had to do with finding my field but every time I wrote something I was getting a True error. Well tahnks again.
To answer your question I think it does not do anything I was taking a shot by trying do delete (fldNames = stranswer).

Tofias1

 

[FoxProProgrammer]

You're welcome! I'm glad that it worked. Thanks for letting me know, and thanks for the star.
dz
dzaccess@yahoo.com