Results 1 to 7 of 7
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Deleting record from main form and sub form

    My app has more than 100 forms that have sub forms. In most of them there is not a main query and sub query, the sub form just returns a list on the form to view. There are the 5 main documents that consist of a main query linked to a sub query. They have parent records and child records.
    1. The preference is to almost never delete records, just mark them as inactive and store information on who deleted the record.


    2. But the app must still have the ability to delete a record.
    3. The forms that do not have a sub query is no problem and the VBA in blue delete a record as expected.
    Private Sub btn16DeleteOne_Click()
    If MsgBox(Prompt:="Are you sure to delete this record?", Buttons:=vbYesNo, Title:="From PERFAC") = vbYes Then
    'On Error Resume Next
    DoCmd.RunCommand acCmdDeleteRecord
    If Err.Number = 0 Then
    MsgBox Prompt:="Deleted", Buttons:=vbOKOnly, Title:="From Perfac"
    Else
    MsgBox Prompt:="There is no record to delete!", Buttons:=vbOKOnly, Title:="From PERFAC"
    End If
    Else
    MsgBox Prompt:="Not Deleted", Buttons:=vbOKOnly, Title:="From PERFAC"
    End If
    End Sub

    4. The app has 5 forms that are the main documents, and they have main queries linked to sub queries, and the referential integrity is set, see image.
    5. The code in blue does not result in deleting a record on these 5 forms. The same delete button and VBA that works well on so many forms, does something on these 5 forms I find very strange. I googled and searched but no success yet.
    Click image for larger version. 

Name:	230511a.png 
Views:	10 
Size:	42.2 KB 
ID:	50225
    6. I would like to learn how to set up a module that delete a record on these 5 forms that have a linked sub form. Then call the delete action from these 5 documents.
    7. Or if it is better, just learn what VBA will delete a parent record and child record, on the local form. Of course, if I learn the technique on one form, I will know how to do it on all of them, since there are a few more.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    If you set your table relationships up correctly, this would all be handled for you when you delete the parent.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    What else should I do to set up table relationships correctly?

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Google Cascade Deletes and Referential Integrity
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. Over time I downloaded many more than 500 video clips, and when applicable I watch them again. I have watched three clips on referential integrity today and read some stuff to see what I am missing. It does not seem complicated. I believe I understand Cascade deletes, and cascade update related fields. I learnt a bit more, but right now I do not know how to improve relationships of the tables.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Then that is the correct relationships, if you have them set?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    There is some stuff on the internet on the subject with which I do not connect immediately, quite lengthy VBA. I will continue until I get it right. I thought this is very common, most developers have to delete parent records that have children in sub forms. I assume you can see on the image to see the infinity sign means something was done right in the tables. Access will not allow the link if the fields was not the same type. When Access gives an error message, it states that the two tables should have the same number of fields??? The image is just an example, the tables are set up correct now.
    Click image for larger version. 

Name:	230511b.png 
Views:	8 
Size:	22.2 KB 
ID:	50226

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 01-30-2020, 10:40 PM
  2. Replies: 7
    Last Post: 11-06-2017, 02:45 AM
  3. Replies: 10
    Last Post: 07-05-2017, 11:31 AM
  4. Replies: 4
    Last Post: 07-21-2015, 02:50 PM
  5. Deleting The Sub-Form Record From Main Form
    By vdanelia in forum Forms
    Replies: 0
    Last Post: 11-08-2011, 04:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums