DELETE ALL RECORDS FROM RELATED RECORDS

[programmer67]

hello bro,
i want to delete records from main form.
3 table relationship with "ogrenci_id"
when pres DELETE
1- ogrencitakip record will delete
2- borctakip record will delete
3- derstakip record will delete

i hope understand me

 

[Ranman256]

cant you just select the record then press the keyboard Delete key?
or
you could add a field MARK (checkbox) ,the user checks all recs to delete
then press your delete button which runs a delete query: delete * from table where [mark]=true

 

[Gasman]

Delete the main record. As long as you have Cascade deletes, the rest will follow.

 

[Kamayo Ako]

hello bro,
i want to delete records from main form.
3 table relationship with "ogrenci_id"
when pres DELETE
1- ogrencitakip record will delete
2- borctakip record will delete
3- derstakip record will delete

i hope understand me

Try this brother:

Private Sub cmdDelete_Click()
Dim lngID As Long
Dim lngID1 As Long
Dim lngID2 As Long
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String



DoCmd.SetWarnings False
lngID = ogrenci_id.Value
lngID1 = ogrenci_id.Value
lngID2 = ogrenci_id.Value

DoCmd.runSQL "DELETE * FROM borctakip WHERE ogrenci_id= " & lngID
DoCmd.runSQL1 "DELETE * FROM derstakip WHERE ogrenci_id= " & lngID1
DoCmd.runSQL2 "DELETE * FROM ogrencitakip WHERE ogrenci_id= " & lngID2
Me.Requery
DoCmd.SetWarnings True
Else

End If

 

[Pat Hartman]

Did you create relationships using the Relationship window? If you didn't, then do it now. Select the enforce RI checkbox. If you want the delete to cascade, then check Cascade Delete on both relationships. Then you delete ONE record. The top record in the relationship and the database engine takes care of the rest. The code posted by @Kamayo Ako won't work unless there is only ONE row at each level.

 

[Kamayo Ako]

Did you create relationships using the Relationship window? If you didn't, then do it now. Select the enforce RI checkbox. If you want the delete to cascade, then check Cascade Delete on both relationships. Then you delete ONE record. The top record in the relationship and the database engine takes care of the rest. The code posted by @Kamayo Ako won't work unless there is only ONE row at each level.

Thank you sir so much for that sir @ Pat Hartman it's my pleasure to know that since im still in the level of learning process. I just share which working on my part for the sake that he maybe get some possible solution but not absolute.

 

[programmer67]

Try this brother:

Private Sub cmdDelete_Click()
Dim lngID As Long
Dim lngID1 As Long
Dim lngID2 As Long
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String



DoCmd.SetWarnings False
lngID = ogrenci_id.Value
lngID1 = ogrenci_id.Value
lngID2 = ogrenci_id.Value

DoCmd.runSQL "DELETE * FROM borctakip WHERE ogrenci_id= " & lngID
DoCmd.runSQL1 "DELETE * FROM derstakip WHERE ogrenci_id= " & lngID1
DoCmd.runSQL2 "DELETE * FROM ogrencitakip WHERE ogrenci_id= " & lngID2
Me.Requery
DoCmd.SetWarnings True
Else

End If

Thank you brother, really working now.

 

[Kamayo Ako]

Thank you brother, really working now.

You welcome brother. But you must also consider the opinion of sir @ Pat Hartman since he had a great experience and expertise concerning that issue.

 

[programmer67]

Did you create relationships using the Relationship window? If you didn't, then do it now. Select the enforce RI checkbox. If you want the delete to cascade, then check Cascade Delete on both relationships. Then you delete ONE record. The top record in the relationship and the database engine takes care of the rest. The code posted by @Kamayo Ako won't work unless there is only ONE row at each level.

Thank you brother,
i have one more table that "ogrenciler" mean students. I do relations In Relationship window. But ogrenciler.id -> borctakip.ogrenci_id, ogenciler.id -> derstakip.ogrenci_adi, ogrenciler.id -> ogrencitakip.ogrenci-id.

 

[Kamayo Ako]

Thank you brother,
i have one more table that "ogrenciler" mean students. I do relations In Relationship window. But ogrenciler.id -> borctakip.ogrenci_id, ogenciler.id -> derstakip.ogrenci_adi, ogrenciler.id -> ogrencitakip.ogrenci-id.

As long as that particular table record has a FK that bind them with Main transaction table PK like ogrenci-id then you can do same process but add another declaration.

 

[Minty]

Be careful with this.
I suspect you wouldn't want to delete a student if their class was removed or a study class if one student was removed.

Generally speaking, you shouldn't delete records, but mark them as Archived and exclude them from your queries based on that flag being set, unless you have physically run out of space.
How would you handle historic reporting with no old data?
Compare attendance levels from year to year... etc. etc.

 

[programmer67]

Be careful with this.
I suspect you wouldn't want to delete a student if their class was removed or a study class if one student was removed.

Generally speaking, you shouldn't delete records, but mark them as Archived and exclude them from your queries based on that flag being set, unless you have physically run out of space.
How would you handle historic reporting with no old data?
Compare attendance levels from year to year... etc. etc.

thank you brother but now i am not interested in classrooms.

 

[Minty]

Well, it was only an example. What about historic student records?
Regardless it's your data, or you are responsible for managing it.

 

[ebs17]

You have to distinguish:

Some use a database as a repository of information, and discarding information is not really an option.

For others, the database is a way station - data in, processing, throwing away.

 

[Pat Hartman]

Test very carefully. The method you are using will NOT work unless your table schema is wrong in which case ALL three lngID's are the same value. Even using the wrong schema and wrong method, you would not be using three different values in LngID

Why do you not want to use the standard method which uses referential integrity and lets the database engine do what it does best?

 

[NauticalGent]

sir @ Pat Hartman

For the record (not that she cares), Pat is a woman. Shouldn't change anything as far as your interaction with her, but for some reason I feel compelled to make corrections when gender is incorrectly assumed.

Maybe I should seek professional help...nah, I'm fine!

 

[Pat Hartman]

You seem to have caught the woke disease

 

[Gasman]

You seem to have caught the woke disease

No, I have mentioned it myself in the past, and I sure as hell do not have the woke disease.

 

[NauticalGent]

You seem to have caught the woke disease

They put it in my COVID "vaccination"...

 

[Pat Hartman]

I don't care what you call me. Just don't call me late for dinner