Delete record in continuous form

[Guest]

I have a continuous form which displays certain fields of info from two
tables; the form's record source is a straight-forward, simple select query.
At the bottom of this form, I have a delete command button.
DoCmd.RunCommand acCmdDeleteRecord

When clicked it deletes the record from the subform and its tables, but not
the primary or parent form or table.

Would appreciate some assistance as to what I've got wrong.
Thanks.

 

[John Vinson]

DoCmd.RunCommand acCmdDeleteRecord

When clicked it deletes the record from the subform and its tables, but not
the primary or parent form or table.

Would appreciate some assistance as to what I've got wrong.

The button's doing exactly and precisely what you're telling it to do:
delete the currently selected record from the form that is displaying
it (the subform in this case).

If you want to delete the record from that OTHER table, the one bound
to the mainform, you'll need to a) be sure that Cascade Deletes is set
on the relationship between the mainform's table and the subform's
table; and b) delete the record from the mainform rather than from the
subform.

John W. Vinson[MVP]

 

[Guest]

The problem you describe is a pain to deal with. Honestly the safest, best
way is to do a CurrentDB.Execute "DELETE QUERY" selecting by the index and
the requery the subform. I have tried other methods and time after time
somehow an important record gets deleted

 

[Guest]

Thanks for your help. I put the delete button in a different form, and it
deletes all the records as I had hoped. Don't know why it works in one form
and not the other, but I don't program professionally, and therefore, am a
bit of a hacker! Thanks again!

 

[John Vinson]

Thanks for your help. I put the delete button in a different form, and it
deletes all the records as I had hoped. Don't know why it works in one form
and not the other, but I don't program professionally, and therefore, am a
bit of a hacker! Thanks again!

It works in the Form that the button is on. Maybe you're missing the
point that a Subform IS A FORM - and a delete button on a Subform
deletes a record from the subform, whereas a button on the mainform
deletes a record from *that* form.

John W. Vinson[MVP]

 

[Guest]

If you feel like helping me one more time, I would appreciate it; I'm really
trying to understand this.

My continuous form is based on a query. The query is pretty
straight-forward with two tables joined so that I can use fields from each of
the tables on my form. Each record has its appropriate data displayed in the
correct fields; some data coming from one table and some data coming from the
'many' (or secondary) table.
On this continuous form, I have a delete button in the footer. If I click
delete, the record seems to go away in its entirety. However, when I close
the form and then re-open it, the record is back with its data from the
primary table. The data from the secondary table is gone.

I have moved the delete button to a form that displays one record at a time.
In this form, the data for a particular record comes from three tables. If
I click delete, the delete cascades properly, removing all the records in all
tables that pertain to the record I am looking at.

So, while I completely understand your point, I don't think it applies to
this situation. Any/all help is greatly appreciated.

Thank you.

 

[John Vinson]

If you feel like helping me one more time, I would appreciate it; I'm really
trying to understand this.

My continuous form is based on a query. The query is pretty
straight-forward with two tables joined so that I can use fields from each of
the tables on my form. Each record has its appropriate data displayed in the
correct fields; some data coming from one table and some data coming from the
'many' (or secondary) table.
On this continuous form, I have a delete button in the footer. If I click
delete, the record seems to go away in its entirety. However, when I close
the form and then re-open it, the record is back with its data from the
primary table. The data from the secondary table is gone.

Correct. By default, if you delete from a multitable query, only the
"manyest" table record is deleted. The assumption is that the subform
is displaying *one* active (selected) record from the "many" side
table, on the current row; and that it is *that* record you wish to
delete. Generally, if you had (say) an Orders form with 34 items on
the OrderDetails subform, a user would expect a button on a row of the
subform to delete THAT ITEM, not to delete the Order, all its
information, and all 34 items. It seems you wish for the button on the
subform to do just that - ok, so your needs are a bit unusual it
seems!

I have moved the delete button to a form that displays one record at a time.
In this form, the data for a particular record comes from three tables. If
I click delete, the delete cascades properly, removing all the records in all
tables that pertain to the record I am looking at.

Why not just move the button from the subform to the mainform, if it's
the mainform record that you're trying to delete? Or, you could use
the Click event of the button to execute a Delete query to delete
whatever it is that you want deleted.


John W. Vinson[MVP]

 

[Guest]

Thanks again for your perseverance!

When you say "Why not just move the button from the subform to the mainform,
if it's
the mainform record that you're trying to delete?", I understand what you
are saying but I don't think it applies to my form. The continuous form does
not have any subforms on it. This form is based on my query which uses two
tables, but there aren't any subforms within this frmContacts.

I tried moving my delete button from the footer to the detail section, and I
had a delete button under every record. But, when I clicked it, the same
things happened as before i.e. the data from the 'many' table was deleted,
but not the data from the 'one' table.

 

[John Vinson]

Thanks again for your perseverance!

When you say "Why not just move the button from the subform to the mainform,
if it's
the mainform record that you're trying to delete?", I understand what you
are saying but I don't think it applies to my form. The continuous form does
not have any subforms on it. This form is based on my query which uses two
tables, but there aren't any subforms within this frmContacts.

I tried moving my delete button from the footer to the detail section, and I
had a delete button under every record. But, when I clicked it, the same
things happened as before i.e. the data from the 'many' table was deleted,
but not the data from the 'one' table.

ok... sorry I misunderstood. It's unusual to base a form on a joined
query; normally if you have a one to many relationship one would in
fact use a Form for the "one" and a subform for the "many".

In this case you'll need to change the code of the delete button to
actually run a delete Query deleting the record from the "one" table.

John W. Vinson[MVP]