Help with Delete Coding

[JohnLute]

This is a HUGE request! I'm in need of some delete coding help. Please allow me to lay out my design:

tblProfilesAssociations
This is an intermediate table that stores records of associated profiles from tblProfiles. It has two fields:
ProfilesAssociations (PK)
txtProfileID (PK)
It has a one-to-many, cascading relationship with tblProfiles using txtProfileID.

sfrmProfilesAssociationsTEMPLATE
This is the subform used to select associated profiles. It has one combo box: cbProfilesAssociations
It's RowSource is: SELECT tblProfiles.txtProfileID, tblProfiles.Description, tblProfiles.Type FROM tblProfiles ORDER BY tblProfiles.txtProfileID, tblProfiles.Type;

This also has an OnChange Event Procedure:
Private Sub cbProfilesAssociations_Change()
Dim v_parent_id As String
Dim v_child_id As String
Dim v_Str_SQL As String

v_parent_id = Forms!frmPKProfilesTEMPLATEcbtype.Form!sfrmProfilesAssociationsTEMPLATE!cbProfilesAssociations
v_child_id = Forms!frmPKProfilesTEMPLATEcbtype!txtProfileID

DoCmd.SetWarnings False
v_Str_SQL = "Insert Into tblProfilesAssociations Values ('"
v_Str_SQL = v_Str_SQL & v_child_id & "','" & v_parent_id & "')"
DoCmd.RunSQL (v_Str_SQL)
DoCmd.SetWarnings True

End Sub

HUGE Request 1:
Need help with coding to delete child if parent is deleted
then present the sql needed to execute when the parent is
deleted. How can this code be put into the 'after del confirm'
event of the parent? Is this the best place to do this?

HUGE request 2:
Need help coding delete of child with swapped fields and coding a statement like this:
delete from tblProfilesAssociations where txtProfileID = the form's ProfilesAssociations field and ProfilesAssociations = the form's txtProfileID field

YIKES! I'm terrible at explainging these things clearly. Does this make sense to anyone?

 

[Chris Nebinger]

Request 1:


You have a one-many relationship set up, with cascading
updates? What about cascading deletes?

A Cascade Delete relationship will automatically delete a
child record when the parent is deleted.



Request 2:

CurrentDB.Execute "delete from tblProfilesAssociations
where txtProfileID = " & Me.ProfilesAssociations & " and
ProfilesAssociations = " & Me.txtProfileID



Chris Nebinger

-----Original Message-----
This is a HUGE request! I'm in need of some delete coding

help. Please allow me to lay out my design:

tblProfilesAssociations
This is an intermediate table that stores records of

associated profiles from tblProfiles. It has two fields:

ProfilesAssociations (PK)
txtProfileID (PK)
It has a one-to-many, cascading relationship with

tblProfiles using txtProfileID.

sfrmProfilesAssociationsTEMPLATE
This is the subform used to select associated profiles.

It has one combo box: cbProfilesAssociations

It's RowSource is: SELECT tblProfiles.txtProfileID,

tblProfiles.Description, tblProfiles.Type FROM tblProfiles
ORDER BY tblProfiles.txtProfileID, tblProfiles.Type;

This also has an OnChange Event Procedure:
Private Sub cbProfilesAssociations_Change()
Dim v_parent_id As String
Dim v_child_id As String
Dim v_Str_SQL As String

v_parent_id = Forms!frmPKProfilesTEMPLATEcbtype.Form! sfrmProfilesAssociationsTEMPLATE!cbProfilesAssociations
v_child_id = Forms!frmPKProfilesTEMPLATEcbtype! txtProfileID

DoCmd.SetWarnings False
v_Str_SQL = "Insert Into tblProfilesAssociations Values ('"
v_Str_SQL = v_Str_SQL & v_child_id & "','" & v_parent_id & "')"
DoCmd.RunSQL (v_Str_SQL)
DoCmd.SetWarnings True

End Sub

HUGE Request 1:
Need help with coding to delete child if parent is deleted
then present the sql needed to execute when the parent is
deleted. How can this code be put into the 'after del confirm'
event of the parent? Is this the best place to do this?

HUGE request 2:
Need help coding delete of child with swapped fields and coding a statement like this:
delete from tblProfilesAssociations where txtProfileID =

the form's ProfilesAssociations field and
ProfilesAssociations = the form's txtProfileID field

YIKES! I'm terrible at explainging these things clearly.

Does this make sense to anyone?