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

delete current records & related records together

WyvsEyeView
P: 46
Upon clicking Delete to delete the current record on frmTopics, I want several things to happen:

1) Display a custom message rather than Access's standard "You are about to delete n records" one.
2) Delete the record.
3) Delete all related attribute records.
4) Go to a new record.

Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2. On Error GoTo Err_cmdDelete_Click
  3.  
  4. DoCmd.SetWarnings (False)
  5.  
  6. Dim strMsg As String
  7. strMsg = "Are you sure you want to delete this topic? You cannot undo a deletion."
  8. If MsgBox(strMsg, vbOKCancel) = vbOK Then
  9.     DoCmd.RunCommand acCmdDeleteRecord
  10.     DoCmd.GoToRecord , , acNewRec
  11.  
  12.     Dim strSQL As String
  13.     strSQL = "DELETE * FROM tblTopicAttributes" & "WHERE tblTopicAttributes.top_id=" & Forms!frmTopics!nbrTopID & ";"
  14.     CurrentDb.Execute strSQL, dbFailOnError
  15. Else
  16.     Me.Undo
  17. End If
  18.  
  19. Exit_cmdDelete_Click:
  20.     Exit Sub
  21.  
  22. Err_cmdDelete_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_cmdDelete_Click
  25.  
  26. DoCmd.SetWarnings (True)
  27.  
  28. End Sub
The record is successfully deleted, and Access's standard message suppressed (is there a better way?) but there is the following problem:

When there are related attribute records that could be deleted, I get a "syntax error in FROM clause error" and the line CurrentDb.Execute strSQL, dbFailOnError is highlighted in my VBA code. I tried this out as a plain query, which worked, and then dropped it into this "formula." Maybe I just missed a quote or something, although I have tweaked it every way I can think of. This issue is somewhat related to my other thread about creating a new record and related records--the concatenation principle--which I now understand in theory, although apparently not in practice :)
Sep 20 '08 #1
Share this Question
Share on Google+
5 Replies


missinglinq
Expert 2.5K+
P: 3,532
Maybe I'm reading this wrong, but it looks like you're deleting a record, then trying to delete related records by using the nbrTopID field from the now deleted, non-existing record in the Where statement.

Maybe delete the related records then the main record?

Linq ;0)>
Sep 20 '08 #2

ADezii
Expert 5K+
P: 8,619
You can DELETE a single Topic, and all related Topic Attributes, in a single Executable Statement, with a few simple modifications to your logic and code:
  1. Assuming you have a valid Join between the Primary Key Field of your Topics Table {1} and the Foreign Key Field of the Topic Attributes Table {MANY}:
    • Check Enforce Referential Integrity on the Properties for the Join.
    • Check Cascade Delete Related Records on the Properties for the Join.
  2. Run the following code wherever appropriate:
    Expand|Select|Wrap|Line Numbers
    1. Dim strMsg As String
    2. Dim strSQL As String
    3.  
    4. strMsg = "Are you sure you want to delete this Topic along with " & _
    5.          "all related Attributes? You cannot undo a deletion."
    6.  
    7. If MsgBox(strMsg, vbYesNo) = vbYes Then
    8.   strSQL = "DELETE * FROM tblTopicAttributes WHERE tblTopicAttributes.top_id=" & _
    9.             Forms!frmTopics!nbrTopID & ";"
    10.   CurrentDb.Execute strSQL, dbFailOnError
    11.   DoCmd.GoToRecord , , acNewRec
    12. Else
    13.   'do absolutely nothing
    14. End If
  3. The Topic Record, as indicated by the nbrTopID, as well as all related Records, if any, will now be DELETED. You will then advance to a New Record in your Main Form.
  4. Any questions, please feel free to ask.
Sep 20 '08 #3

WyvsEyeView
P: 46
Thanks to both of you! Linq, you were right in that I was deleting the main record, then trying to delete the related records. Duh. ADezii, I'm always nervous about cascading deletes because it's so easy for a user to delete more than they mean to, and frankly (though this may be lazy of me) too intricate for me to then programmatically make sure they don't. So I didn't make that change but I did take your other suggestions and now have the code working perfectly. Thanks again!
Sep 22 '08 #4

ADezii
Expert 5K+
P: 8,619
Thanks to both of you! Linq, you were right in that I was deleting the main record, then trying to delete the related records. Duh. ADezii, I'm always nervous about cascading deletes because it's so easy for a user to delete more than they mean to, and frankly (though this may be lazy of me) too intricate for me to then programmatically make sure they don't. So I didn't make that change but I did take your other suggestions and now have the code working perfectly. Thanks again!
I'm always nervous about cascading deletes because it's so easy for a user to delete more than they mean to
Understood, but aren't you currently in a situation where you will be leaving Orphaned Records?
Sep 22 '08 #5

WyvsEyeView
P: 46
Before making these changes, I was leaving orphaned records, which is why I implemented this approach. Unless I have missed something, my current Delete button code will delete the main record and any related records, thus avoiding orphaned records. All my tests seem to indicate that is what is happening.
Sep 23 '08 #6

Post your reply

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