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

Senin, 19 September 2011

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

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

To: MS_Access_Professionals@yahoogroups.com
From: no_reply@yahoogroups.com
Date: Mon, 19 Sep 2011 15:35:31 +0000
Subject: [MS_AccessPros] Re: Record not deleting AARGH!!!

Thanks Duane. There was no good reason for all of those tables to be the record source. I changed some fields to comboboxes and wala--it's down to one! Just shows how little I knew when I was beginning. And I'm thinking that down the road I'll look at what I've done now and say the same thing. Oh well--that's how you learn, huh?

I really need to delete a record using code. If a listing was marked as sold and the sales info entered and then it's realized that the wrong property was marked as sold, the used needs to be able to go to that sales form for that record, and delete the info. Can I use a delete query in code? I've never used delete queries.

Thanks!
Connie

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> Connie,
>
> You have 4 tables in your record source. I wouldn't attempt to delete from this query. I typically don't delete records from tables as I prefer to use a status field that indicates whether a record is deleted.
>
> If you really want to delete a record, I would run a delete query filtered by the primary key field from the table.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: no_reply@yahoogroups.com
> Date: Wed, 14 Sep 2011 19:09:19 +0000
> Subject: Re: [MS_AccessPros] Record not deleting AARGH!!!
>
>
>
>
>
>
> Duane,
>
> I can add & edit, but not delete. What is that about??? When I delete from the query, it looks like it's deleted but when I reopen it, the record is still there. When I opened the Listings table and tried to delete one of the records, I got the message that I could not delete because the DropPrice table had related records. Never got that message when I tried to delete the same record. I created another record that would not pull that message and it would not delete from the form, although I could delete it from the table.
>
> After I sent the first message I realized that one of the changes I've made is that this form is opened with different recordsources for different purposes. So I took the recordsource that is in the code for the way I've been opening it; put the sql in a new query so I could open it and test it. The above paragraph was a result of this.
>
> Any ideas?
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >
> >
> > Connie,
> > Is the record source query updateable? When you open the datasheet can you edit/delete records?
> >
> > Duane Hookom
> > MS Access MPV
> >
> >
> >
> >
> > To: MS_Access_Professionals@yahoogroups.com
> > From: no_reply@yahoogroups.com
> > Date: Wed, 14 Sep 2011 18:44:14 +0000
> > Subject: [MS_AccessPros] Record not deleting AARGH!!!
> >
> >
> >
> >
> >
> >
> > I know this must be a simple thing and it was working, but I've done some changing and now when I click the delete button on the Listings form it is not deleted.
> >
> > I recently added a sub_Form based on ListingsHomeInfo table to the Listings form. It creates a one to many relationship between Listings and HomeInfo. The Listings table and ListingsHomeInfo table are in a cascade delete/update relationship. I do not think this could be the issue because even when I delete a Listing that has not entry in the ListingsHomeInfo table, it still does not delete.
> >
> > Thanks sooo much!
> > Connie
> >
> > The code for the delete button:
> >
> > On Error GoTo Proc_Err
> >
> > 'if new record then beep
> > If (Me.NewRecord And Not Me.Dirty) Then
> > Beep
> > Exit Sub
> > End If
> > 'if changes have been made, undo them
> > If Me.Dirty = True Then Me.Undo
> >
> > 'if not on a new record, then delete
> > If Not Me.NewRecord Then
> > DoCmd.RunCommand acCmdDeleteRecord
> > End If
> > Proc_Exit:
> > Exit Sub ' or Exit Function
> >
> > Proc_Err:
> >
> > Msgbox Err.Description, , _
> > "ERROR " & Err.Number _
> > & " btnDelete_Click"
> >
> > Resume Proc_Exit
> >
> > The RecordSource SQL:
> > SELECT qListings.*
> > FROM qListings
> > WHERE (((qListings.[CurrentListing])=-1));
> >
> > SQL for qListings:
> > SELECT DISTINCTROW Listings.ListID, Listings.HomeInfoID, Listings.PropertyName, Listings.ListDate, Listings.ExpDate, Listings.ListingAgency, Listings.OriginalListPrice, Listings.OccupancyID, Listings.LockboxNbr, Listings.LotSize, Listings.ZoningID, Listings.HouseTypeID, Listings.Beds, Listings.baths, Listings.Fullbaths, Listings.ThreeQuarterBaths, Listings.Partialbaths, Listings.SquareFoot, Listings.Notes, HomeInfo.ParcelNbr, Listings.TelCombo, Listings.XtraInfo, Listings.YearBuilt, Listings.Rural, Listings.CurrentListing
> > FROM (HomeInfo INNER JOIN (Listings LEFT JOIN ListingContacts ON Listings.[ListID] = ListingContacts.[ListID]) ON HomeInfo.HomeInfoID = Listings.HomeInfoID) LEFT JOIN PotentialListings ON Listings.ListID = PotentialListings.ListID;
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>