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

Rabu, 14 September 2011

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

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]
>