By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Deleting a record using a form in Microsoft Access 2007

P: 2
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
Jun 5 '15 #1

✓ answered by jforbes

You could use RunCommand. This should delete the current record:
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)
You may want to include a select record command before deleting:
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunCommand(Command:=acCmdSelectRecord)
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2. sSQL = "DELETE FROM SomeTable WHERE ID=" & 23
  3. CurrentDB.Execute sSQL
A real world example of the Execute:
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo ErrorOut
  2.  
  3.     Dim sSQL As String
  4.  
  5.     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
  6.         sSQL = ""
  7.         sSQL = sSQL & "DELETE FROM PartAttachments "
  8.         sSQL = sSQL & "WHERE PartID = " & Me!PartID & " "
  9.         sSQL = sSQL & "AND AttachmentID = " & Me.PartAttachmentsSub!AttachmentID & " "            
  10.         CurrentDB.Execute sSQL , dbFailOnError + dbSeeChanges        
  11.         Me.refresh
  12.     End If
  13.  
  14. ExitOut:
  15.     Exit Sub
  16.  
  17. ErrorOut:
  18.     gErrorMessage = "SQL String: " & sSQL
  19.     Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)
  20.     Resume ExitOut

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
You could use RunCommand. This should delete the current record:
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)
You may want to include a select record command before deleting:
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunCommand(Command:=acCmdSelectRecord)
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2. sSQL = "DELETE FROM SomeTable WHERE ID=" & 23
  3. CurrentDB.Execute sSQL
A real world example of the Execute:
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo ErrorOut
  2.  
  3.     Dim sSQL As String
  4.  
  5.     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
  6.         sSQL = ""
  7.         sSQL = sSQL & "DELETE FROM PartAttachments "
  8.         sSQL = sSQL & "WHERE PartID = " & Me!PartID & " "
  9.         sSQL = sSQL & "AND AttachmentID = " & Me.PartAttachmentsSub!AttachmentID & " "            
  10.         CurrentDB.Execute sSQL , dbFailOnError + dbSeeChanges        
  11.         Me.refresh
  12.     End If
  13.  
  14. ExitOut:
  15.     Exit Sub
  16.  
  17. ErrorOut:
  18.     gErrorMessage = "SQL String: " & sSQL
  19.     Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)
  20.     Resume ExitOut
Jun 5 '15 #2

P: 2
Thanks a lot jforbes...
I really appreciate your help.
Jun 5 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.