Prev: Quote or referral
Next: Mail database
From: SoggyCashew on 2 Feb 2010 11:55
Hello, I have a form frmVWI and a subform named frmVWISubfor. I have a button
on my form that deletes the record but not my subforms record. there is a
field that is the same on both tables can my record be deleted thatway? My
forms table tblVWI has a field named VWI and it is a PK. My subforms table
tblJobSteps has a field named VWInum. I want to delete records from these to
field from a button on my form. How is this done? Thanks!
--
Thanks,
Chad
From: Dirk Goldgar on 2 Feb 2010 12:05
"SoggyCashew" <SoggyCashew(a)discussions.microsoft.com> wrote in message
news:0601FE06-6B8C-437D-902F-5ED1D0DCA458(a)microsoft.com...
> Hello, I have a form frmVWI and a subform named frmVWISubfor. I have a
> button
> on my form that deletes the record but not my subforms record. there is a
> field that is the same on both tables can my record be deleted thatway? My
> forms table tblVWI has a field named VWI and it is a PK. My subforms table
> tblJobSteps has a field named VWInum. I want to delete records from these
> to
> field from a button on my form. How is this done? Thanks!


The simplest way is to define the relationship between the tables (if you
haven't done so already), and specify that the relationship is to "Cascade
Deletes". That way, whenever a record is deleted from tblVWI, any related
records in tblJobSteps will automatically be deleted, no matter how the
tblVWI record is deleted, on this form or elsewhere. No code is required.

If for some reason you can't use an enforced relationship here, and you must
do it in code, you can capture the value of VWI before you delete that
record, and then use that value in a delete query to delete all matching
records in tblJobSteps. But beware: then you have to allow for the
possibility that the user might back out of the original delete (if you
allow the warning prompt to be displayed), and you won't want to delete the
child records in that case. This is not hard to do, but it is important to
get it right. I strongly recommend you just use the relationship & Cascade
Deletes to let the database engine handle it.

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

(please reply to the newsgroup)

From: SoggyCashew on 2 Feb 2010 12:23
Thans you it worked perfectly... I didnt know that was there
--
Thanks,
Chad


"Dirk Goldgar" wrote:

> "SoggyCashew" <SoggyCashew(a)discussions.microsoft.com> wrote in message
> news:0601FE06-6B8C-437D-902F-5ED1D0DCA458(a)microsoft.com...
> > Hello, I have a form frmVWI and a subform named frmVWISubfor. I have a
> > button
> > on my form that deletes the record but not my subforms record. there is a
> > field that is the same on both tables can my record be deleted thatway? My
> > forms table tblVWI has a field named VWI and it is a PK. My subforms table
> > tblJobSteps has a field named VWInum. I want to delete records from these
> > to
> > field from a button on my form. How is this done? Thanks!
>
>
> The simplest way is to define the relationship between the tables (if you
> haven't done so already), and specify that the relationship is to "Cascade
> Deletes". That way, whenever a record is deleted from tblVWI, any related
> records in tblJobSteps will automatically be deleted, no matter how the
> tblVWI record is deleted, on this form or elsewhere. No code is required.
>
> If for some reason you can't use an enforced relationship here, and you must
> do it in code, you can capture the value of VWI before you delete that
> record, and then use that value in a delete query to delete all matching
> records in tblJobSteps. But beware: then you have to allow for the
> possibility that the user might back out of the original delete (if you
> allow the warning prompt to be displayed), and you won't want to delete the
> child records in that case. This is not hard to do, but it is important to
> get it right. I strongly recommend you just use the relationship & Cascade
> Deletes to let the database engine handle it.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
 | 
Pages: 1
Prev: Quote or referral
Next: Mail database