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

Delete record from main form and related records in subform

mseo
100+
P: 181
hi,
I need to delete the record in form and all the related record from the subform
I don't know how to do so.
thanks
Mar 9 '10 #1
Share this Question
Share on Google+
3 Replies


100+
P: 106
Hi

Create a Delete Query based on the table used in your subform, and save it with a unique filename to suit like 'DelQry'

in my example, I will reference the tables thus: MainTable and SubTable
Primary key is referenced as RecordID.

Please substitute for your values.

Expand|Select|Wrap|Line Numbers
  1. DELETE MainTable.RecordID, SubTable.*
  2. FROM MainTable INNER JOIN SubTable ON MainTable.RecordID = SubTable.RecordID
  3. WHERE (((MainTable.RecordID)=[Forms]![MainForm]![RecordID]));
  4.  
Now create a delete button on your main form.
under the ONCLICK property, hopefully you will get

Expand|Select|Wrap|Line Numbers
  1. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  2. DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  3.  
just before these lines add

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "DelQry"
  2. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  3. DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  4.  
Hopefully this will do the trick.

let me know how you get on.
Mar 9 '10 #2

mseo
100+
P: 181
hi,
I tried to do this but when I click the button Delete the delete query is opening because of the line
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "DelQry" 
and this code delete one record each time and I want when I click delete in the main form the record in the first subform to be deleted and the all the related records in the second sub form
thanks really, I appreciate you help
Apr 11 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
There are two approaches you can use :
  1. Design the tables to include cascaded deletes, then simply delete the record from the main table. All related subrecords will be deleted as part of the process. Before taking this course ensure this is appropriate for all your requirements as it will effect the database as a whole and not just your form.
  2. Produce the Delete query in a SQL string and execute it. It is not necessary to save the query in these circumstances (not that it is necessarily a problem you understand).
Apr 12 '10 #4

Post your reply

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