ACCESS FEED: RE: [MS_AccessPros] Re: Record not deleting AARGH!!!

Senin, 19 September 2011

RE: [MS_AccessPros] Re: Record not deleting AARGH!!!

Connie,
We typically refer to Parent and Child tables. Apparently you want to delete the related child records which would be code like

Dim strSQL as String
strSQL = "DELETE * FROM <<Child Table>> WHERE <<Foreign Key Field>> = <<Some Value>>"
CurrentDb.Execute strSQL, dbFailOnError

Duane Hookom
MS Access MVP

From: no_reply@yahoogroups.com

In this case I'm not deleting from the parent table but from the table that uses the ParentID as a foreign key (what is the term for that table?--Baby?? ;-)). And I was trying to delete it using the code behind the form that had the Baby table in an inner join with the parent table as its record source.

Connie

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> Connie,
>
> When I attempt to delete a record from a parent table when the tables have referential integrity enforced but not cascade, I get a message that states I can't delete the record from the parent table since there are related records in the related table.
>
> Are you suggesting your experience is not the same?
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: no_reply@yahoogroups.com
> Date: Mon, 19 Sep 2011 20:09:22 +0000
> Subject: [MS_AccessPros] Re: Record not deleting AARGH!!!
>
>
>
>
>
>
> Duane,
> I meant that the record source of the Sales form was the table Sales and Listings based on an inner join. My Sales and Listings tables have referential integrity enforced but do not have cascade delete or update selected.
>
> Sorry I was unclear
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >
> >
> > Connie,
> >
> > I'm not sure how "forms" can be inner joined. Tables are typically joined and there can be properties/constraints to cascade deletes. If this is set, any parent record deletion will automatically delete related child records.
> >
> > Duane
> >
> > To: MS_Access_Professionals@yahoogroups.com
> > From: no_reply@yahoogroups.com
> > Date: Mon, 19 Sep 2011 18:46:07 +0000
> > Subject: [MS_AccessPros] Re: Record not deleting AARGH!!!
> >
> >
> > thanks duane! It's working now--woo hoo! I would like to understand though. On the Sales form the Sales and Listings forms were inner joined. Does that kind of joining make the Listings record be deleted in addition to the Sales record when a DoCmd...DeleteRecord is done?
> >
> > Why would I not have had a problem with this for many months and now that the Sales tables record source is cleaner, have it now?
> >
> > I also appreciate your mention that you usually have a status and do not delete records. Wish I had done that in the beginning. Will hopefully remember that idea the next time I create a db.
> >
> > I'd love to understand.
> > Conie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > >
> > >
> > > You can use a delete query in code. Are you suggesting that after removing the extra tables, the code you had didn't work?
> > >
> > > Dim strSQL As String
> > > strSQL = "DELETE * FROM <<table Name>> WHERE <<PK Field>> = <<Some Value>>"
> > > Currentdb.Execute strSQL, dbFailOnError
> > >
> > > Duane Hookom
> > > MS Access MVP
>
>
> [Non-text portions of this message have been removed]
>