Deleting a record using a form in Microsoft Access 2007

[Vihanga]

Hi,
I am a beginner in programming and am trying to write code to delete a record from a table using a record... Can someone help me in writing it?

Thanks

[jforbes]

You could use RunCommand. This should delete the current record:

Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)

You may want to include a select record command before deleting:

Call DoCmd.RunCommand(Command:=acCmdSelectRecord)
Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)

If you want to create a SQL command to delete a record instead of deleting the current record on a Form the basic syntax is like this:

Dim sSQL As String
sSQL = "DELETE FROM SomeTable WHERE ID=" & 23
CurrentDB.Execute sSQL

A real world example of the Execute:

On Error GoTo ErrorOut
    
    Dim sSQL As String
    
    If msgBoxAreYouSure("Are you sure you would like to Continue?  This will remove the link between this Part and the Attachment, but will not delete the Attachment.") Then
        sSQL = ""
        sSQL = sSQL & "DELETE FROM PartAttachments "
        sSQL = sSQL & "WHERE PartID = " & Me!PartID & " "
        sSQL = sSQL & "AND AttachmentID = " & Me.PartAttachmentsSub!AttachmentID & " "            
        CurrentDB.Execute sSQL , dbFailOnError + dbSeeChanges        
        Me.refresh
    End If
        
ExitOut:
    Exit Sub

ErrorOut:
    gErrorMessage = "SQL String: " & sSQL
    Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)
    Resume ExitOut

[Vihanga]

Thanks a lot jforbes...
I really appreciate your help.