DELETE Btn

[craigmacca]

Hi i have a delete record button in MS Access.

instead of deleting just the current record, i need to run a sql stored procedure which is held on the sql server linked via a dsn

not sure how i can do this any ideas?

Private Sub DeleteCurrentOrder_Click()
On Error GoTo Err_DeleteCurrentOrder_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_DeleteCurrentOrder_Click:
Exit Sub
Err_DeleteCurrentOrder_Click:

Resume Exit_DeleteCurrentOrder_Click

End Sub

[DarkSprout]

To Use:
nResult = DKillRecord("tbl_TableName", "[anAutoNumberField] = " & nRecordToDelete_ID)

It will return the number of records deleted based on the Criterion

'// Darryl S. Drury (DarkSprout) - April 2007
Public Function DKillRecord(txtTableName As String, vCriterionToMatch As Variant) As Long
' Will return the number of records Deleted / a minus for a fail -1/-2/-3/-4
' nResult = DKillRecord("tbl_CustomerTable", "[txtLastName] = " & txtOldLastName)
' Add a ! at the begining of the 'Match Criterion' to Force a Number to stay a string
' eg. DKillRecord("tbl_Orders","[Cost]=!" & txtCost)
Dim nJobDone As Integer
Dim tb As DAO.Recordset
Dim vCriterionValue As Variant
Dim vFieldToWrite As Variant
Const DBLINE = vbCrLf & vbCrLf

On Error GoTo Fail
vCriterionValue = Trim(Replace(Replace(Right(vCriterionToMatch, Len(vCriterionToMatch) - InStr(1, vCriterionToMatch, "=", vbTextCompare)), "]", ""), "[", ""))
vFieldToWrite = Trim(Replace(Replace(Left(vCriterionToMatch, InStr(1, vCriterionToMatch, "=", vbTextCompare) - 1), "]", ""), "[", ""))
If Asc(Left(vCriterionValue, 1)) = 33 Then
vCriterionValue = Right(vCriterionValue, Len(vCriterionValue) - 1)
Else
If IsNumeric(vCriterionValue) = True Then vCriterionValue = Val(vCriterionValue)
End If
nJobDone = -2
Set tb = CurrentDb.OpenRecordset(txtTableName, dbOpenDynaset)
nJobDone = 0
tb.MoveFirst
Do While Not tb.EOF
If tb.Fields(vFieldToWrite).Value = vCriterionValue Then
tb.Delete
nJobDone = nJobDone + 1
End If
tb.MoveNext
Loop
tb.Close
GoSub ExitFunction

Fail:
If Err.Number = 3421 Then nJobDone = -3
If Err.Number = 3164 Then nJobDone = -4
If Err.Number = 3202 Then nJobDone = -5
nJobDone = IIf(nJobDone = 0, -1, nJobDone)

ExitFunction:
If nJobDone < 0 Then
If nJobDone = -1 Then MsgBox "Criterion Field Not Found: " & vFieldToWrite, 64, "DKillRecord ~ Data Match Fail"
If nJobDone = -2 Then MsgBox "Table Not Found: " & txtTableName, 64, "DKillRecord ~ Table Error"
If nJobDone = -3 Then MsgBox "Wrong Field Type: " & vFieldToWrite, 64, "DKillRecord ~ Field Error"
If nJobDone = -4 Then MsgBox "Field Cannot Be Updated: " & vFieldToWrite, 64, "DKillRecord ~ Field Error"
If nJobDone = -5 Then MsgBox "Field Cannot Be Updated: " & vFieldToWrite & DBLINE & "Record In Use By Another User", 64, "DKillRecord ~ Record Locked"
End If
DKillRecord = nJobDone
End Function