#delete after code based delete

[Guest]

Hi everyone,

I am using Access 2007 on an Access 2003 database. I have a process - that
works - that when a certain type of record is added in a form that is a
form-continuous subform structure the AfterUpdate event fires and adds a
second related record to the table. Everything here is working okay. The
record is added, the related record is added and the form and subform display
the correct information.

What I am working now - and it almost works correctly - is when the record
of a certain type is deleted. I have the process that deletes the related
record working. I used the Delete event, passed the required information to a
module, developed and executed a delete query via DoCmd.RunSQL.

I have tried every way from Sunday to get the subform to "refresh" itself
and remove the #Deleted reference from the deleted related record. I have
tried a Me.Requery, Me.Refresh and Me.Repaint in the form, subform and
module. No go.

Any suggestions will be greatly appreciated.

Bruce

 

[Guest]

First of all, the best way to handle deletion of related records is to set
Referential Integrity and Cascade Deletes on the relationship between those
tables. If you do that, when you delete the master record, the child
record(s) will be deleted automatically.

Secondly, don't use DoCmd.RunSQL it is unreliable and replaced with
CurrentDb.Execute strSQL - look up the method in the help...
Incidentally, the above will work for the current database but you can also
pass a different database in the first argument.

I'm not certain what is going on with the #Deleted reference since deleting
the master record should moe the form to another record and presumably the
subform would show different related records?

What is the relationship between your form/subform?

Steve

 

[Guest]

Steve,

Thanks for the reply. I will look at the CurrentDb.Execute strSQL and
convert it over.

The records are in the same table not classicly related tables. I didn't
explain it very well. When a record is added that meets certain criteria I
programmatically add another record to the same table to make the process of
data entry easier - have to keep the users happy. When the record is later
deleted I, also for the same reason, need to delete the second record. My
fault for the confusion. I shouldn't have described them as related when they
are in the same table. Paired would have maybe been a better word. Where
appropriate in this database I already have Referential Integrity enforced.

The relationship between the main form and the subform is an ID for each
person.

Bruce