Complete Record Deletion

  • Thread starter Thread starter Merwin12
  • Start date Start date
M

Merwin12

Hi to all,
I have a form with a delete command.
This is the code behind it:

If Not IsNull(Me.txtclientid) Or Me.txtclientid <> "" Then
If MsgBox("Do you want to delete" + Chr(13) + _
"Client Id = '" & Trim(Me.txtclientid) & "'? " + Chr
(13), vbYesNo + vbCritical, "Outside Storage") = vbYes Then
Dim strquery As String
Dim qd As QueryDef
strquery = "exec usp_deleteClient @intClientid = " &
Me.txtclientid
Set qd = CurrentDb.QueryDefs("qps_DeleteClient")
qd.SQL = strquery
DoCmd.SetWarnings 0
MsgBox "Record Deleted", vbInformation, "Outside
Storage"
On Error Resume Next
qd.OpenRecordset
DoCmd.SetWarnings -1
Set qd = Nothing
End If
Else
MsgBox "Please enter the Client ID you want to
delete.....", vbCritical, "Outside Storage"
Me.txtclientid.SetFocus
End If

This code is okay except after the record deleted message,
the text box retains the value and when I close the form,
it saves the record back again.

Ex. Client Id: 37
Last Name: Jones
First Name: Amy

After closing the form and then I will look at the table,
it retains client id 37 and last/first name as null fields.

How can I erase the id entered so it doesn't save it
when I close the form? thanks.
 
It depends on what your stored procedure is doing. Can you be sure it is
correctly deleting the record?

Instead of:
DoCmd.SetWarnings 0
On Error Resume Next
qd.OpenRecordset
DoCmd.SetWarnings -1
Click to expand...

Try this:
qd.Execute, dfFailOnError

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Back
Top