Records which won't delete (1 Viewer)

Dumferling

Member
Local time
Today, 17:09
Joined
Apr 28, 2020
Messages
102
I cannot delete a record from a form or a query. I thought it might be at the query level so I opened the query and attempted from there. It appears to delete fine (asks for confirmation and the record disappears) but if I requery then the record reappears. I can add new records with the query so I understand that the query should allow me to delete. If I try to delete at the form level it runs a vba confirmation check that I want to and then doesn't actually delete. There are no error messages, just a failure to delete. Any suggestions on where to look to find out what the issue is? I can't find anything that indicates what I am doing wrong. I am using DoCmd.RunCommand acCmdDeleteRecord as the delete code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:09
Joined
Oct 29, 2018
Messages
21,359
Hi. Are you able to post a demo version of your db, so we can take a look?
 

Dumferling

Member
Local time
Today, 17:09
Joined
Apr 28, 2020
Messages
102
I am not sure that I can do that, partially because I never have and partially because it contains company information and partially because it is split into backend and frontend. Should I be deleting information and just posting a thinned down version?
 

Dumferling

Member
Local time
Today, 17:09
Joined
Apr 28, 2020
Messages
102
Ah. I can post the SQL of the query.
SELECT tblContracts.ID, tblContracts.ContractName, tblContracts.Description, tblContracts.ContractType, tblContracts.Status, tblContracts.ReviewDate, tblContracts.LastReviewDate, tblContracts.ReviewedBy, tblContracts.ReviewOutcome, tblContracts.StatusDate, tblContracts.StartDate, tblContracts.EndDate, tblContracts.EndDateCheck, tblContracts.AutoRenewal, tblContracts.Premiums, tblContracts.ActivityType, tblContracts.RecordDestructionDate, tblContracts.FileName, tblContracts.ContractDoc, tblContracts.DocumentLoaded, tblContracts.LegalOwner, tblContracts.BusinessOwner, tblContracts.BusinessUnit, tblContracts.Quantum, tblContracts.TerminationPeriod, tblContracts.TPCountedAs, tblContracts.LockInPeriod, tblContracts.LIPCountedAs, tblContracts.POPIAAffected, tblContracts.POPIACompliant, tblContracts.DataTransfer, tblContracts.DataWhen, tblContracts.DataPurpose, tblContracts.DataWhat, tblContracts.DateHow, tblContracts.CounterpartyOperatorStatus, tblContracts.MMHOperatorStatus, tblContracts.RiskRegContract, tblContracts.RiskLiabillity, tblContracts.RiskPenalty, tblContracts.RiskTermination, tblContracts.RiskDOASigned, tblContracts.RiskArbitration, tblContracts.RiskQuantum, tblContracts.RiskAutoRenewal, tblContracts.RiskLegalApproval, tblContracts.RiskResult, tblContracts.RiskResultName, tblContracts.RiskAssessed, tblMMIList.MMIDivision, tblContracts.StoredDrive, tblContracts.Report, tblContracts.DateAdded
FROM tblContracts LEFT JOIN tblMMIList ON tblContracts.ID = tblMMIList.IDMMICo
ORDER BY tblContracts.ContractName;
 

Dumferling

Member
Local time
Today, 17:09
Joined
Apr 28, 2020
Messages
102
1590508811309.png
 

cheekybuddha

AWF VIP
Local time
Today, 15:09
Joined
Jul 21, 2014
Messages
2,238
Do you have a relationship set up between tblContracts and tblMMIList? (not just a join in a query)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:09
Joined
Feb 28, 2001
Messages
27,003
Using DoCmd.RunCommand acCmdDeleteRecord should delete the current record for a bound form. However, if the record is open for another user this could fail. You don't want to requery, though. You want to navigate to the next record or the previous record.

This isn't the first time we have had a report of this problem.

The suggestion for that problem was to look at this link:
http://allenbrowne.com/ser-61.html

This case was caused by having inadvertantly "disconnected" the button-click event from its code.

This case is more interesting in that it becomes a matter of what has focus at the time. Here the problem was that the wrong thing had focus for deletion purposes so the wrong table lost its current record.
 

Isaac

Lifelong Learner
Local time
Today, 08:09
Joined
Mar 14, 2017
Messages
8,738
If I try to delete at the form level it runs a vba confirmation check that I want to and then doesn't actually delete. There are no error messages, just a failure to delete.
Please post that VBA code.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,985
When you have a query that joins tblA to tblB and you delete a record, the "child" record is the one that would get deleted and NOT the parent record. You have a left join so you are returning tblA records that have no corresponding tblB records so when you attempt to delete that tblA record, nothing happens because the tblA record would never have been deleted no matter what. Only the tblB record which doesn't exist would have been deleted.

If you want to delete tblA records that have no match in tblB, you need to use a subquery with a not Exists. Someone can post a sample if you need it. I'm running late.
 

Dumferling

Member
Local time
Today, 17:09
Joined
Apr 28, 2020
Messages
102
Please post that VBA code.
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
Me.Requery
Recordset.MoveNext
 

Dumferling

Member
Local time
Today, 17:09
Joined
Apr 28, 2020
Messages
102
Using DoCmd.RunCommand acCmdDeleteRecord should delete the current record for a bound form. However, if the record is open for another user this could fail. You don't want to requery, though. You want to navigate to the next record or the previous record.

This isn't the first time we have had a report of this problem.

The suggestion for that problem was to look at this link:
http://allenbrowne.com/ser-61.html

This case was caused by having inadvertantly "disconnected" the button-click event from its code.

This case is more interesting in that it becomes a matter of what has focus at the time. Here the problem was that the wrong thing had focus for deletion purposes so the wrong table lost its current record.
I tried the suggestion in the bottom link and it fixed the problem! I am not sure exactly why and I am sure that I didn't have the wrong focus. I checked through the Allen Browne list and didn't see any of those suggestions as an issue. Thanks for all the help and suggestions, great to have this issue fixed!
 

Isaac

Lifelong Learner
Local time
Today, 08:09
Joined
Mar 14, 2017
Messages
8,738
No offense meant at all, and I'm very glad you got it working, but this sounds like a fragile situation. I don't recommend using DoCmd.RunCommand at all when you can avoid it, but rather doing something like:
Code:
dim lngID as long
lngID = me.Controlname
CurrentDb.Execute "delete * from tablename where ID=" & lngID
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:09
Joined
Feb 28, 2001
Messages
27,003
Isaac, concur with using a form of DB.EXECUTE as being unequivocal and better as a long-term fix. Concur with the possibility of the link I offered leading to something that is a bit fragile. However, perhaps now there is the luxury of time for Dumferling to find (or force) a quiet moment to fix that code according to your suggestion and in the interim, catch up to other issues.
 

Users who are viewing this thread

Top Bottom