Delete record command not working

[Tara]

I have a continuous form with a command button next to each record to delete
that record if desired. Due to an addition to the database, I recently
changed the record source of the form slightly. Since then, the delete code
isn't working. It appears to the user that it has deleted the record but
it's actually still in the table. Unless the user closes the form and then
reopens it for some reason, and sees that the record has "reappeared", they
have no clue the record still exists. I'm not sure how to get the code
working again, so I'm hoping someone can point out the problem. I'm sure it
has to do with changing the record source, so I've posted that here:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.PostedDate,
tblContact.ServiceDate, tblContact.FaceToFace, tblContact.TeamMtg,
tblContact.CrisesPC, tblContact.CC, tblContact.StaffMtg, tblContact.Admin,
tblContact.DS, tblContact.DSTypeID, tblContact.OC, tblContact.OCTypeID,
tblContact.NoShow, tblContact.Court, tblContact.Training, tblContact.Travel,
tblContact.Notes, tblContact.numID, tblAmendedDates.AmendID FROM tblContact
LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;

 

[Dirk Goldgar]

I have a continuous form with a command button next to each record to
delete
that record if desired. Due to an addition to the database, I recently
changed the record source of the form slightly. Since then, the delete
code
isn't working. It appears to the user that it has deleted the record but
it's actually still in the table. Unless the user closes the form and
then
reopens it for some reason, and sees that the record has "reappeared",
they
have no clue the record still exists. I'm not sure how to get the code
working again, so I'm hoping someone can point out the problem. I'm sure
it
has to do with changing the record source, so I've posted that here:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.PostedDate,
tblContact.ServiceDate, tblContact.FaceToFace, tblContact.TeamMtg,
tblContact.CrisesPC, tblContact.CC, tblContact.StaffMtg, tblContact.Admin,
tblContact.DS, tblContact.DSTypeID, tblContact.OC, tblContact.OCTypeID,
tblContact.NoShow, tblContact.Court, tblContact.Training,
tblContact.Travel,
tblContact.Notes, tblContact.numID, tblAmendedDates.AmendID FROM
tblContact
LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;

Was your change to the recordsource the addition of the left join to
tblAmendedDates? Was it originally just tblContact, or was it an inner join
to tblAmendedDates, now changed to a left join?

With that recordsource, a "delete record" operation will delete the record
(if any) in tblAmendedDates, but not the record in tblContact. Then, when
you open the form again and the recordsource is requeried, the record shows
up again, because the record has not been deleted from tblContact.

Is it your intention to delete the record from tblContact and all matching
records in tblAmendedDates? Or something else?

 

[Tara]

Thanks for looking at this Dirk. Yes, the addition was the left join. I
knew that was the issue, I just wasn't sure how to fix it. And yes, I need
it to delete the record in tblContact as well as the corresponding record in
tblAmendedDates if one exists.

 

[Dirk Goldgar]

Thanks for looking at this Dirk. Yes, the addition was the left join. I
knew that was the issue, I just wasn't sure how to fix it. And yes, I
need
it to delete the record in tblContact as well as the corresponding record
in
tblAmendedDates if one exists.

I believe this can be done, but it's a bit tricky. Before attempting to
code it, I'd like to make sure that this is really the best approach to
take. Please pardon me if I ask a few more questions.

What is the nature of the relationship between tblContact and
tblAmendedDates?

You mention "the corresponding record in tblAmendedDates". Can there be
more than one record in tblAmendedDates for any given tblContact record? Is
this a one-to-many or one-to-one relationship? Judging by the fields in
your SQL statement, it *looks* like its implemented as a one-to-many
relationship (one tblContact record to potentially many tblAmendedDates
records), even if you intend for there to be only one child record.

If there are more than one tblAmendedDate record for a given ContactID, what
do you want to have happen to the tblContact record if the user deletes only
one of the records?

Could the relationship between these tables be better represented with a
main form/subform arrangment, the main form based on tblContact and the
subform based on tblAmendedDates?

Is your form being displayed in single form view or in continuous forms
view?

 

[Tara]

Dirk, sorry it took me so long to get back with you on this.

The relationship between tblContacts and tblAmendedDates is basically
parent/child. TblAmendedDates is used to add and/or alter the existing data
in the tblContacts parent record to which it is attached.

The relationship is set up as a one-to-many due to the (highly unlikely)
chance that he user amends a record and for some reason, at a later date,
needs to amend the amendment...if that makes sense. I highly doubt it will
ever happen, but better to be prepared for the possibility I guess.

If a user deletes the "parent" record so-to-speak in tblContacts, I want any
and all associated records in tblAmendedDates to be deleted as well.

The form is currently being displayed in continuous forms view.

Thanks for all your help!

 

[mie via AccessMonster.com]

Edit Relationships to :
1) Enforce Referential Integrity
2) Cascade Delete Related Records

When record from parent deleted, all child records associated will be deleted
as well.

Dirk, sorry it took me so long to get back with you on this.

The relationship between tblContacts and tblAmendedDates is basically
parent/child. TblAmendedDates is used to add and/or alter the existing data
in the tblContacts parent record to which it is attached.

The relationship is set up as a one-to-many due to the (highly unlikely)
chance that he user amends a record and for some reason, at a later date,
needs to amend the amendment...if that makes sense. I highly doubt it will
ever happen, but better to be prepared for the possibility I guess.

If a user deletes the "parent" record so-to-speak in tblContacts, I want any
and all associated records in tblAmendedDates to be deleted as well.

The form is currently being displayed in continuous forms view.

Thanks for all your help!
[quoted text clipped - 27 lines]

Is your form being displayed in single form view or in continuous forms
view?

 

[Tara]

I tried to edit the relationship, but the option to enforce referential
integrity is greyed out. I've looked for orphans that might prevent me from
setting referential integrity, but there aren't any, so I'm confused as to
why it won't let me. Any thoughts?

Edit Relationships to :
1) Enforce Referential Integrity
2) Cascade Delete Related Records

When record from parent deleted, all child records associated will be deleted
as well.

Dirk, sorry it took me so long to get back with you on this.

The relationship between tblContacts and tblAmendedDates is basically
parent/child. TblAmendedDates is used to add and/or alter the existing data
in the tblContacts parent record to which it is attached.

The relationship is set up as a one-to-many due to the (highly unlikely)
chance that he user amends a record and for some reason, at a later date,
needs to amend the amendment...if that makes sense. I highly doubt it will
ever happen, but better to be prepared for the possibility I guess.

If a user deletes the "parent" record so-to-speak in tblContacts, I want any
and all associated records in tblAmendedDates to be deleted as well.

The form is currently being displayed in continuous forms view.

Thanks for all your help!

Thanks for looking at this Dirk. Yes, the addition was the left join. I
knew that was the issue, I just wasn't sure how to fix it. And yes, I

[quoted text clipped - 27 lines]

Is your form being displayed in single form view or in continuous forms
view?

 

[Tara]

Never mind...I closed and reopened and I was able to set it.

I tried to edit the relationship, but the option to enforce referential
integrity is greyed out. I've looked for orphans that might prevent me from
setting referential integrity, but there aren't any, so I'm confused as to
why it won't let me. Any thoughts?

Edit Relationships to :
1) Enforce Referential Integrity
2) Cascade Delete Related Records

When record from parent deleted, all child records associated will be deleted
as well.

Dirk, sorry it took me so long to get back with you on this.

The relationship between tblContacts and tblAmendedDates is basically
parent/child. TblAmendedDates is used to add and/or alter the existing data
in the tblContacts parent record to which it is attached.

The relationship is set up as a one-to-many due to the (highly unlikely)
chance that he user amends a record and for some reason, at a later date,
needs to amend the amendment...if that makes sense. I highly doubt it will
ever happen, but better to be prepared for the possibility I guess.

If a user deletes the "parent" record so-to-speak in tblContacts, I want any
and all associated records in tblAmendedDates to be deleted as well.

The form is currently being displayed in continuous forms view.

Thanks for all your help!

Thanks for looking at this Dirk. Yes, the addition was the left join. I
knew that was the issue, I just wasn't sure how to fix it. And yes, I
[quoted text clipped - 27 lines]
Is your form being displayed in single form view or in continuous forms
view?

 

[Tara]

Setting referential integrity didn't affect the ability to delete the record,
unfortunately. Do you have any other thoughts?

Thanks!

Edit Relationships to :
1) Enforce Referential Integrity
2) Cascade Delete Related Records

When record from parent deleted, all child records associated will be deleted
as well.

Dirk, sorry it took me so long to get back with you on this.

The relationship between tblContacts and tblAmendedDates is basically
parent/child. TblAmendedDates is used to add and/or alter the existing data
in the tblContacts parent record to which it is attached.

The relationship is set up as a one-to-many due to the (highly unlikely)
chance that he user amends a record and for some reason, at a later date,
needs to amend the amendment...if that makes sense. I highly doubt it will
ever happen, but better to be prepared for the possibility I guess.

If a user deletes the "parent" record so-to-speak in tblContacts, I want any
and all associated records in tblAmendedDates to be deleted as well.

The form is currently being displayed in continuous forms view.

Thanks for all your help!

Thanks for looking at this Dirk. Yes, the addition was the left join. I
knew that was the issue, I just wasn't sure how to fix it. And yes, I

[quoted text clipped - 27 lines]

Is your form being displayed in single form view or in continuous forms
view?

 

[Dirk Goldgar]

Edit Relationships to :
1) Enforce Referential Integrity
2) Cascade Delete Related Records

When record from parent deleted, all child records associated will be
deleted
as well.

That won't solve Tara's problem, because she is not deleting directly from
the parent table, but rather is deleting from a query that joins the tables
one-many. In that case, only the many-side record will be deleted.

 

[Dirk Goldgar]

Dirk, sorry it took me so long to get back with you on this.

The relationship between tblContacts and tblAmendedDates is basically
parent/child. TblAmendedDates is used to add and/or alter the existing
data
in the tblContacts parent record to which it is attached.

The relationship is set up as a one-to-many due to the (highly unlikely)
chance that he user amends a record and for some reason, at a later date,
needs to amend the amendment...if that makes sense. I highly doubt it
will
ever happen, but better to be prepared for the possibility I guess.

If a user deletes the "parent" record so-to-speak in tblContacts, I want
any
and all associated records in tblAmendedDates to be deleted as well.

With Cascade Deletes enforced, that would work -- *IF* you were deleting the
parent record from tblContacts. But, as I explained in my earlier post,
that's not what you are doing. Because your form is based on a query that
joins the two tables, when you delete from the form, only the record from
tblAmendedDates is being deleted.

If your form were in single form view, you could base it on tblContacts
alone, and use a subform to show the amended-dates. Then a simple delete
from the main form would work. But you can't do that in continuous forms
view.

Probably the easiest way to solve this problem is to set the form's Allow
Deletions property to No, but put a command button on the form to delete the
record. The code for that command button would execute a delete query to
delete the current record directly from tblContacts -- relying on the
relationship with Cascade Deletes to delete the records from
tblAmendedDates, and then requery the form.

Is that something you can figure out how to do by yourself, or do you need
help with the code?

 

[Tara]

Thanks so much for looking at this again Dirk. What you said makes perfect
sense and I *think* I can do it on my own from here. Would you mind checking
the thread again in an hour or so though, just in case?

I appreciate all of your help with this!

 

[Dirk Goldgar]

Thanks so much for looking at this again Dirk. What you said makes
perfect
sense and I *think* I can do it on my own from here. Would you mind
checking
the thread again in an hour or so though, just in case?

Sure. I keep an eye on all threads I've been involved in.

I appreciate all of your help with this!

I'm glad to help.

 

[Tara]

It worked beautifully Dirk! I honestly don't know what I'd do if it weren't
for the help that you and others on this board offer day in and day
out...Thanks so much for investing some of your time here.

 

[Dirk Goldgar]

It worked beautifully Dirk!
Excellent!

Thanks so much for investing some of your time here.

You're very welcome.