Deleting records in main form and subform

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

This question refers to a main form with a continuous form subform.

After an error occurs after entering several records in the subform, how can I
delete all the data in the main form and all the records in the subform? I have
tried undoing both the main form and the subform and I have tried deleting the
record in the main form.

Thanks!

Mark
 
This question refers to a main form with a continuous form subform.

After an error occurs after entering several records in the subform, how can I
delete all the data in the main form and all the records in the subform? I have
tried undoing both the main form and the subform and I have tried deleting the
record in the main form.
Click to expand...

The first thing to be aware of is that you are NOT storing data in the
form. The form is just a tool to let you update data in the Tables -
and it's from the Tables that you need to delete the data!

Secondly, the way a Subform works is: as soon as you set focus to the
subform, the mainform record is written to its table. As you move off
of each subform record, *that* record is written to its table; when
you leave the subform, the last record on the subform is likewise
written to disk. It's too late to "undo" anything, it's already been
done.

What you will need to do is run a Delete query to explicitly delete
all the records in the table bound to your subform; or, go into the
Relationships window and select (or create, if it's not there!) the
relationship between the mainform's table and the subform's table.
Check the "Enforce Referential Integrity" checkbox if it's not already
checked, and check the "Enable Cascade Deletes" checkbox. Now when you
delete a record on the mainform, it will warn you once, and then
permanently and irrevokably delete that record *AND* all the related
records.
 
Mark said:
This question refers to a main form with a continuous form subform.

After an error occurs after entering several records in the subform, how can I
delete all the data in the main form and all the records in the subform? I have
tried undoing both the main form and the subform and I have tried deleting the
record in the main form.

Thanks!

Mark
Click to expand...

I suppose the easiest way would be to set referential integrity.

I had a situation yesterday where I had a main table and a few child tables. I
asked, in the OnDelete, for confirmation on deleting. If No, I canceled. If yes,
I deleted. My MainForm was using a LeftJoin.....select all records from main table
and if there are records in another table that match the id, use some of those
fields....as the recordsource. I deleted all child table records and expected all
records to go away....that almost happened except the main record would "go away"
but if I opened the form again, there was the record I just deleted.

What I did....I set a global variable at the top of the code window.
Dim lngID as Long

Then if the delete was confirmed I did something like
lngID = Me.MainFormID

Then in the AfterDelete event I then ran a bunch of queries to delete the child
record for that record (as I don't have relational integrity set on those tables)
then I ran a delete query for the current record for just the table....not the
Recordsource that had a Left Join. And it worked.

So....you can delete the child records in the OnDelete event....if that causes
problems then use the AfterDelete event.
 
Back
Top