Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an Orders form with an Orders subform. I want to have a delete button on the form that will delete the current record along with its subform record. For example, if you try to delete an order, you will get an error message telling you that there are related records in the subform (or order details table, etc.). I would like to know how to add code to a button that will delete the "current order" (record) along with its matching subform details. Sometimes the person who is inputting the orders makes a mistake and realizes later that they do not need that order they inputted yesterday. I do not want them to go into the tables, besides they would not know where to look, etc. I want to make it easy for them to delete the Order along with its order details. The matching primary ID key is called OrderID in the main form and its subform. Thanks in advance for your help.

    Kind Regards,

    Tina

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The easiest way to do this is to set the relationship between the Orders and Order Details tables to "Cascade Delete Related Records":
    - From the database window, select Tools | Relationships... or click Relationships on the toolbar.
    - There should be a relationship between the Orders and Order Details tables.
    - If so, double click the join line (you have to click precisely on the line).
    - If not, create one by dragging the OrderID field from Orders to the related field in Order Details.
    - Make sure that "Enforce Referential Integrity" and "Cascade Delete Related Records" are ticked.
    - Click OK.

    [attachment=85306:x.png]

    When you delete an order, Access will display a warning that you're about to delete a record and all related records.
    Attached Images Attached Images
    • File Type: png x.png (5.8 KB, 2 views)

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    Ah, but of course. That works perfectly instead of trying to figure out how to set the focus on the subform.

    A tiny question which I know I could figure out, but if you know a quick answer, this could save me some time which would be much appreciated. I would like a message warning the user that they are about to delete a record and are you sure? I know I can use the MsgBox function and I probably have to SetWarnings to False and then back to True, can you just enlighten me and give me a quick solution?

    Thanks so much Hans,

    Tina

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the BeforeDelConfirm event of the form. The following example is adapted from the Access VBA help for this event:

    Code:
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer) 
      ' Suppress default Delete Confirm dialog box. 
      Response = acDataErrContinue
      ' Display custom dialog box. 
      If MsgBox("Are you sure that you want to delete this record and " & _
    	  "all corresponding records from the subform?", vbOKCancel) = vbCancel Then 
    	Cancel = True	 
      End If 
    End Sub

  5. #5
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    Thanks so much for all of your help. Much appreciated.

    Cheers!

    Tina

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •