Cascade Delete

[lmcc007]

I have two tables Events and Image. Image table will store all the path names to pictures.

I set the form up for Events that if the EventType is Attachment, it will open the Image form for me to enter a path name. It is a one-to-one relationship because each event will have a different EventID.

Problem: When I view the Image form and delete an image it does not delete the event even though I have Cascade Delete set up. But, when I delete the Event, it deletes the image. The Cascade Delete works.

What am I doing wrong?

 

[boblarson]

Not doing anything wrong. One side is the main and the other is the child (even in a one-to-one relationship). You delete the main and the child/children goes away.

 

[lmcc007]

Not doing anything wrong. One side is the main and the other is the child (even in a one-to-one relationship). You delete the main and the child/children goes away.

Since Event is the main, how do I tell it on the form to delete that EventID it is related to? Meaning, when I view my pictures, and I decide to delete that picture, I need it to delete the EventID for that attachment.

To add an Attachment, I do the following:

1. Go to Event form.
2. EventType is Attachment
3. Then the Image forms opens so I can add the path name.

 

[boblarson]

You could run a delete query which has the criteria of the EventID.

Dim strSQL As String

strSQL = "Delete * FROM TableNameHere WHERE EventID =" & Me!EventID

CurrentDb.Execute strSQL, dbFailOnError

 

[Galaxiom]

Like water cascading down a stream the Access cascade is one direction only. The table on the left of the relationship is the master while the one on the right is a child.

Since you have a one to one relationship there is no reason to separate the records into two tables anyway. Problem avoided.

 

[lmcc007]

Like water cascading down a stream the Access cascade is one direction only. The table on the left of the relationship is the master while the one on the right is a child.

Since you have a one to one relationship there is no reason to separate the records into two tables anyway. Problem avoided.

I am a little confuse here. The form is set up where if you enter Attachment as your EventType, then it opens the Image form. If the EventType is Send, then no form is open.

 

[lmcc007]

You could run a delete query which has the criteria of the EventID.

Dim strSQL As String

strSQL = "Delete * FROM TableNameHere WHERE EventID =" & Me!EventID

CurrentDb.Execute strSQL, dbFailOnError

Thanks Bob,

Do you think I should reconsider my table struture to and put all the fields in one table? Like:

tblEvent
EventID
CompanyID
Date
TimeIn
TimeOut
EventTypeID
AttachmentPath
Notes

 

[boblarson]

I might store the notes in another table if it is a memo field (keeps corruption from happening so much). But the rest should be fine together as long as there will only be one attachment path per record.

 

[Galaxiom]

I am a little confuse here. The form is set up where if you enter Attachment as your EventType, then it opens the Image form. If the EventType is Send, then no form is open.

The way the forms are set up is quite independent of the data structure itself. Just include an attachment path field in the main table. There is absolutely no point to placing this field in another table with a one to one relationship.

Include a delete button in the image attachment form to delete the entire record. No cascading required.

 

[boblarson]

There is absolutely no point to placing this field in another table with a one to one relationship.

True, if it is a one-to-one. If there can be multiple attachments then it would be different.

 

[lmcc007]

I might store the notes in another table if it is a memo field (keeps corruption from happening so much). But the rest should be fine together as long as there will only be one attachment path per record.

Okay, I didn't think about doing a separate table for notes; there will be many notes and some may be lengthy. Oh, boy I thought I was almost through.

 

[lmcc007]

True, if it is a one-to-one. If there can be multiple attachments then it would be different.

Some companies will have many attachments.

 

[lmcc007]

The way the forms are set up is quite independent of the data structure itself. Just include an attachment path field in the main table. There is absolutely no point to placing this field in another table with a one to one relationship.

Include a delete button in the image attachment form to delete the entire record. No cascading required.

I set the table up this way because depending on the EventType there may be additional questions that need to be answered that most EventTypes will not have to answer. Such as:

If EventType is SendMedia, then a form opens and ask the type of media (e.g., Brochure, adv, DVD and so on).

 

[lmcc007]

I might store the notes in another table if it is a memo field (keeps corruption from happening so much). But the rest should be fine together as long as there will only be one attachment path per record.

Hey Bob,

Since I am pulling Notes field out of the Event table, do I need to add CompanyID to the EventNote table? This is what I have so far:

EventNoteID Autonumber PK
EventID Number Event realted to
EventNote Text Memo field

 

[boblarson]

No just eventid.

 

[lmcc007]

No just eventid.

Got it, just wanted to make sure because when doing on hurry you always forget something.

Thanks!

 

[lmcc007]

Hey Bob,

How would you or most professional set the table up when you answer one question, it may open more questions? I'm going back to the way my table is set up--that is depending on the EventType there may be additional questions that need to be answered that most EventTypes will not have to answer. Such as: If EventType is SendMedia, then a form opens and ask the type of media (e.g., Brochure, adv, DVD and so on).

Is the correct way in do it is one big table?

 

[Galaxiom]

There is no real harm in having multiple null fields for many records. Access uses very little extra overhead beyond the space occupied by the data.

However I would think it very likely that the user might want to add more classifications later. Under your current structure the designer would have to add fields to the table and controls to the forms.

Instead, the linked table can have three fields. One is the FK to the main record. The next is an FK to a table of classification types (currently stored as fields in your design). The third holds the value (or a normalisation key value) of that classification.

The user can then add new classifications by adding records to the classifications table.

The trick with displaying this structure is to use a Continuous subform. As many records appear as the classifications recorded for the main record.

 

[lmcc007]

There is no real harm in having multiple null fields for many records. Access uses very little extra overhead beyond the space occupied by the data.

However I would think it very likely that the user might want to add more classifications later. Under your current structure the designer would have to add fields to the table and controls to the forms.

Instead, the linked table can have three fields. One is the FK to the main record. The next is an FK to a table of classification types (currently stored as fields in your design). The third holds the value (or a normalisation key value) of that classification.

The user can then add new classifications by adding records to the classifications table.

The trick with displaying this structure is to use a Continuous subform. As many records appear as the classifications recorded for the main record.

I am not following you here on the table setup. One big table? Or separate tables like I have now? Event, Image, and Media are separate tables.

Event table ask questions that lead up to the path name for the Image and the type of Media sent.

 

[lmcc007]

The way the forms are set up is quite independent of the data structure itself. Just include an attachment path field in the main table. There is absolutely no point to placing this field in another table with a one to one relationship.

Include a delete button in the image attachment form to delete the entire record. No cascading required.

Hey Galaxion,

I finally got what you were saying. I made it one table and used the forms to display it the way needed. I kept remembering in class that each subject should have its own table, so I was thinking how the media was sent was a separate table even though the EventType was Send.

Now I have it where if the EventType is Send, it will display a form asking how it was sent and so on.

Thanks!