Help Coding Delete Child/Parent Records

[JohnLute]

I've designed an intermediate table (tblProfilesAssociations) that supports a bi-directional many-to-many relationship with individual entities in a table (tblProfiles).

tblProfiles
txtProfileID (PK)

tblProfilesAssociations
txtProfilesAssociations
txtProfileID (Establishes one-to-many relationship w/ tblProfiles)
(Both fields are a compound PK)

I use sfrmProfilesAssociations.cbProfilesAssociations to create Parent/Child records in tblProfileAssociations. I now need to develop a code to delete Parent/Child records.

Specifically, I need help coding child delete if the parent is deleted and vice-versa.

I know more info is needed and I'll be happy to provide it.

I appreciate any help received!

(Dirk: if you're reading this please check your mail early next week)

 

[Albert D. Kallal]

If you setup the relations, and check the "cascade" delete options, then
child records are automatically deleted.

So, in your form, if you delete the main record, then all child records will
be deleted for you automatically (no code is needed).

And, if you delete a child record, I don't see any need to do anything else,
or delete any other records.

 

[JohnLute]

Thanks, Albert.

Your suggestion is understood. However, I've got a bit of a different design.

Mine is an intermediate table that supports a bi-directional many-to-many relationship with individual entities in a table.

This intermediate table (tblProfilesAssociations) contains two fields:
txtProfilesAssociations
txtProfileID
(Both fields are a compound PK)

In this design, both fields can contain Parents and Children.

I use sfrmProfilesAssociations.cbProfilesAssociations to create Parent/Child records in tblProfileAssociations. I'd also like to be able to delete Parent/Child records through the same sfrm.

Specifically, I need help coding child delete if the parent is deleted and vice-versa.

Thanks for your help!

 

[JohnLute]

Thanks, Albert!

Your suggestion is understood. However, I have a bit of a different design. Mine is an intermediate table that supports a bi-directional many-to-many relationship with individual entities in a table.

The intermediate table (tblProfilesAssociations) contains two fields:
txtProfilesAssociations
txtProfileID
(Both fields are a compound PK)

In this manner either field can store a Parent or a Child record.

I use sfrmProfilesAssociations.cbProfilesAssociations to create Parent/Child records in tblProfilesAssociations. In other words, if I create the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 is automatically created. This function works fine.

What I'd like to do now is delete Parent/Child records in tblProfilesAssociations. In other words, if I delete the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 needs automatically deleted.

I hope this better clarifies the design. Any guidance you can give would be greatly appreciated!

 

[JohnLute]

Thanks, Albert!

Your suggestion is understood. However, I have a bit of a different design. Mine is an intermediate table that supports a bi-directional many-to-many relationship with individual entities in a table.

The intermediate table (tblProfilesAssociations) contains two fields:
txtProfilesAssociations
txtProfileID
(Both fields are a compound PK)

In this manner either field can store a Parent or a Child record.

I use sfrmProfilesAssociations.cbProfilesAssociations to create Parent/Child records in tblProfilesAssociations. In other words, if I create the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 is automatically created. This function works fine.

What I'd like to do now is delete Parent/Child records in tblProfilesAssociations. In other words, if I delete the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 needs automatically deleted.

I hope this better clarifies the design. Any guidance you can give would be greatly appreciated!

 

[JohnLute]

Thanks, Albert!

Your suggestion is understood. However, I have a bit of a different design. Mine is an intermediate table that supports a bi-directional many-to-many relationship with individual entities in a table.

The intermediate table (tblProfilesAssociations) contains two fields:
txtProfilesAssociations
txtProfileID
(Both fields are a compound PK)

In this manner either field can store a Parent or a Child record.

I use sfrmProfilesAssociations.cbProfilesAssociations to create Parent/Child records in tblProfilesAssociations. In other words, if I create the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 is automatically created. This function works fine.

What I'd like to do now is delete Parent/Child records in tblProfilesAssociations. In other words, if I delete the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 needs automatically deleted.

I hope this better clarifies the design. Any guidance you can give would be greatly appreciated!

 

[JohnLute]

Thanks, Albert!

Your suggestion is understood. However, I have a bit of a different design. Mine is an intermediate table that supports a bi-directional many-to-many relationship with individual entities in a table.

The intermediate table (tblProfilesAssociations) contains two fields:
txtProfilesAssociations
txtProfileID
(Both fields are a compound PK)

In this manner either field can store a Parent or a Child record.

I use sfrmProfilesAssociations.cbProfilesAssociations to create Parent/Child records in tblProfilesAssociations. In other words, if I create the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 is automatically created. This function works fine.

What I'd like to do now is delete Parent/Child records in tblProfilesAssociations. In other words, if I delete the record of 205055 as a Child to 12345 then the record of 12345 as a Child to 205055 needs automatically deleted.

I hope this better clarifies the design. Any guidance you can give would be greatly appreciated!

 

[Albert D. Kallal]

Not 100% sure why you have to create the reverse link record (sql could be
used here). Anyway, lets just leave that issue for another day.

I assume you have some delete button, or have a custom delete menu option
for this record when viewing it (and, if you don't, then simply set the
forms allow deleting to no, and code your own button anyway). You could also
simply put your deleting code in the after delete event of the form if you
don't want to build your down delete buttons(s) for the form. (you might
have to save the current id)

Thus, whichever approach you use, simply write out your deleting code.

dim strSql as string
dim strID as string

strID = ??? (set this to the 1245..or whatever)

strSql = "delete * from whatevertable where ID = '" & strID & "'"
currentdb.Execute strSql

' now the child table reocrd
strSql = "delete * from tblProfilesAssociations where
txtProfilesAssociations = '" & strID & "'"
currentdb.Execute strSql

docmd.close ' I as a habit generall close the form after doing a
deleting, but this is up to you

 

[JohnLute]

Thanks, Albert.

I think maybe the reason for creating the reverse link record is needed now. This functions as a "mini" BOM. Please allow me to explain.

The heart of my database is tblProfiles. This table stores basic info for a multitude of entity types which can be anything: raw ingredients; glass bottles; film; corrugated; etc. Any of these entity types have the potential to relate to each other.

Each entity type has it's own form. Each form employs sfrmProfilesAssociations as a means of selecting and recording related entity types. Let's say I have frmFinishedGoods opened to record 12345. From sfrmProfilesAssociations I select 205055 as being related. The reverse link needs to be created so that when I open the form for 205055 (frmPKCorrugated) a record in sfrmProfilesAssociations will show that it's related to 12345.

I need to delete this reverse link so that when I open frmFinishedGoods to record 12345 and delete the record of 205055 the reverse link will be also be deleted.

I don't use delete buttons. Rather, I simply highlight the record in sfrmProfilesAssociations and use the delete buttons in the tool bar. Will the code you setup allow for this?

 

[Albert D. Kallal]

yes, just use the after delete event, or the after del confirm event....

 

[Albert D. Kallal]

Hi, Albert. I'm not very good with coding at all so please excuse my ignorance.

First off, I can't fid the After Delete Event. I can only find the After

Update Event. I put the following code in it but obviously, it doesn't work.

Could you guide me a bit?

Sure I can help. Also, you need to be careful here, as we are writing code
to delete data. So, yes...you very much do want to find the correct event!
(in fact, you better test on a copy of the data...)

I going to suggest you use a following code. And, in fact, I going to
suggest we use the before delConfirm event.

The reason why I am going to suggest to use this event is that you do NOT
have to place a custom delete button on the form, but can continue
to highlight the record selector, and then whack the delete button.


Further, since we will continue to let the system delete the record we are
looking at, then we ONLY need write the code to delete the child record(s)
in question:

Here is how the code should look:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

Dim strSql As String
Dim strID As String



If MsgBox("would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes
Then
Cancel = False
Response = acDataErrContinue
' you delete code goes here:
strID = ??? what field has the id value of the reocrd you need to
delete?
strSql = "delete * from tblProfilesAssociations where
txtProfilesAssociations = '" & strID & "'"
CurrentDb.Execute strSql
Else
Cancel = True
End If

End Sub

Now of course, the delete code above will have to be modified to suit your
needs.

Remember, to get the forms events, you need to select the form in design
mode
(either click in the square in the upper left hand corner, or go
edit->select form)
At this point you should be able to see the properties sheet, and see the
before del confirm events).

 

[JohnLute]

Thanks, Albert - we're getting close! Now I see that you were referring to the FORMS delete events. I was looking at the combo box delete events.

Here's how I've edited the code:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Dim strSql As String
Dim strID As String

If MsgBox("would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes
Then
Cancel = False
Response = acDataErrContinue
strID = ProfilesAssociations
strSql = "delete * from tblProfilesAssociations where "
txtProfileID = '" & strID & "'"
CurrentDb.Execute strSql
Else
Cancel = True
End If

End Sub

Immediately I get a Compile error: Syntax error with this:
If MsgBox("would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) =

I believe this typically means a spelling error but I don't see one.

Also, I see another Compile error: Expected: expression with this:
txtProfileID = '"

So far these are the only glitches. Any ideas?

Thanks a bunch!

 

[Albert D. Kallal]

There is some "wrapping" occurring when I posted the code..(sorry!).

Also:

Also, I see another Compile error: Expected: expression with this:
txtProfileID = '"

We are attempting to surround the valud of strID with quotes, so:

strID = "123"

msgbox strID

Will display 123

We want quotes around the id (ONLY need THIS if the field is a actual string
field)

strID = "'" & "123" & "'"
msgbox strID

Note that "'" is " ' " (but, spaces are removed).
The result of the msgbox will be:

Will display '123'

So, our code was:
strSql = "delete * from tblProfilesAssociations where "
txtProfileID = '" & strID & "'"

This should be all on one line:

strSql = "delete * from tblProfilesAssociations
where txtProfileID = '" & strID & "'"

As menteond, if ProfileID is a number TYPE field, then you need

strSql = "delete * from tblProfilesAssociations
where txtProfileID = " & strID

Note how I removed the single quotes that will surround the strID...
(I assume that txtProfileID is the name of the field in the
table...right...?)

To previent the issue of line wrap, I will re-write those above lines so
they
do NOT wrap...

You can use:

strSql = "delete * from tblProfilesAssociations " & _
" where txtProfileID = '" & strID & "'"

So, we get:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Dim strSql As String
Dim strID As String

If MsgBox("would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes Then
Cancel = False
Response = acDataErrContinue
strID = ProfilesAssociations
strSql = "delete * from tblProfilesAssociations " & _
" where txtProfileID = '" & strID & "'"
CurrentDb.Execute strSql
Else
Cancel = True
End If

End Sub

However, you did not set strID in the above code...I am leaving that up to
you to set it to the value (id) of what reocrd you want to delete...

 

[JohnLute]

Many thanks, Albert!

Something's still not quite right. I've corrected the code to this:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Dim strSql As String
Dim strID As String

If MsgBox("would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes Then
Cancel = False
Response = acDataErrContinue
strID = ProfilesAssociations
strSql = "delete * from tblPKProfilesAssociations " & _
" where txtProfileID = '" & strID & "'"
CurrentDb.Execute strSql
Else
Cancel = True
End If

End Sub

I'm fuzzy on how to set the strID. Could you explain the mechanics of this?

Also, when I try to delete the Run-time error '94': Invalid use of Null
appears and the debugger points to strID = ProfilesAssociations that it
=Null. Is this because I haven't set the strID yet?

Thanks for your time and patience.

 

[Albert D. Kallal]

I'm fuzzy on how to set the strID. Could you explain the mechanics of

this?

yes, just set strID to the value of the record you want to delete


strID = me!NameofFieldWithValueYouWantToDelete

As mentioned, if the field is number field (not text), then don't put quotes
around the value in the code.

So, just set strID to a field value. (txtProfileAsssocoiaons..or the other
field...I not sure which one you actually want to delete).

Also, you mentioned this is compound key...so in fact you might have to put
both conditions in to ensue you don't too delete too many records.

so,

strID = 1234

We get:

strSql = "delete * from tblProfilesAssociations " & _
" where txtProfileID = '" & strID & "'"
CurrentDb.Execute strSql

or, if as human, we translate the above:

delete * from tblProfilesAssociations where txtProfileID = '1234'

I just not 100% sure if txtProfileID is the field you need to delete by.
Obviously, the above will delete any record in tblProfiels with txtProfileID
= 1234 (I don't know if that is what you want,...but you get the idea).

If you need key by both fields, then

delete * from tblProfilesAssociations where txtProfileID = '1234'" and
txtProfilesAsscocations = bla bla bla

 

[JohnLute]

Thanks again, Albert.

I don't think I've been very clear. My objective is to select a record and
delete it and it's reversed record. I think things might be clearer if I give
you the code used to create reverse records:

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!frmPKProfilesTEMPLATE.Form!sfrmPKProfilesAssociations!cbProfilesAssociations
v_child_id = Forms!frmPKProfilesTEMPLATE!txtProfileID

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

End Sub

Does seeing this make my design more clear? Can the code you gave me be
edited in order to delete a record and it's reverse record?

Thanks in advance!