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;

"Tara" <(E-Mail Removed)> wrote in message
news:565E5BDC-4B72-4428-8D4B-(E-Mail Removed)...
>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?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

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" wrote:

> "Tara" <(E-Mail Removed)> wrote in message
> news:565E5BDC-4B72-4428-8D4B-(E-Mail Removed)...
> >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?
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>

"Tara" <(E-Mail Removed)> wrote in message
news:04C571CC-EB91-440D-9A0A-(E-Mail Removed)...
> 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?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

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!

"Dirk Goldgar" wrote:

> "Tara" <(E-Mail Removed)> wrote in message
> news:04C571CC-EB91-440D-9A0A-(E-Mail Removed)...
> > 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?
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>

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?

"mie via AccessMonster.com" wrote:

> 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.
>
>
> Tara wrote:
> >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?
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>

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

"Tara" wrote:

> 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?
>
> "mie via AccessMonster.com" wrote:
>
> > 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.
> >
> >
> > Tara wrote:
> > >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?
> >
> > --
> > Message posted via http://www.accessmonster.com
> >
> > .
> >

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

Thanks!

"mie via AccessMonster.com" wrote:

> 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.
>
>
> Tara wrote:
> >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?
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>

"mie via AccessMonster.com" <u58255@uwe> wrote in message
news:a7ea4a5a83c8d@uwe...
> 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, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Tara" <(E-Mail Removed)> wrote in message
news:E409FA4A-5E16-46B0-867E-(E-Mail Removed)...
> 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?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)