Can’t delete record

[Eljefegeneo]

Report A is a find duplicates report that shows the duplicate entry of two records on two different forms from two different tables, tblMain & frmMain and tblNew and frmNew. The report has an OnClick action for the tblMain ID number to bring up the record in frmMain and frmNew that are duplicates. When I have frmNew open, and I click on delete the record using: DoCmd.RunCommand acCmdDeleteRecord, nothing seems to happen, that is, the record on frmNew is not deleted. If I click on the tab for frmMain, there is no record any more, as if it is deleted. But it still exists.
I tried the following for the delete record button on frmNew:
DoCmd.Close acForm, "frmMain"
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

That deletes the record, but why should I have to do this? Is it because the report is still open and thus the underlying query open which prevents the record from being deleted?
I am just trying to understand the reasoning behind the various codes and actions so that don’t spend hours again trying to solve a problem which really has a simple answer. Thanks.

 

[Cronk]

After the delete, requery the form, as in Me.Requery or forms!Yourformname.requery

 

[Eljefegeneo]

Thanks for your suggestion. I tried it today but to no avail. The code I used was:
Private Sub cmdDelete_Click()
DoCmd.RunCommand acCmdDeleteRecord
Forms!frmNewNames.Requery
End Sub

Nothing was deleted. Even if I add the line: DoCmd.RunCommand acCmdSelectRecord, nothing is deleted.

However, when I use the following it does.
DoCmd.Close acForm, "frmMain"
DoCmd.Close acReport, "rptFindDupesMainVsNew"
DoCmd.RunCommand acCmdSelectRecord
Cmd.RunCommand acCmdDeleteRecord'On Error Resume Next
'Will not open report if no data
'DoCmd.OpenReport "rptFindDupesMainVsNew", acViewReport

End Sub

What I am trying to figure out is why I have to go through so many steps. Is it because when the report is open that the underlying query is open and thus nothing can be deleted? Or am I doing something else wrong?

 

[TJPoorman]

Is your query an editable query? That could be the problem. Try opening the query directly and deleting a record there.

 

[Eljefegeneo]

The problem isn't that I can't ever delete the record; I can delete it from a query or from a table or even the form. The query behind the report is just a find duplicates query. From that I can bring up the duplicate records in frmMain and frmNewNames to compare them, see if they truly are duplicates, make any change to the record in frmMain with new data, and then go to the record on frmNewNames and delete it. However, to delete the record, I have to close the report and close frmMain. That is what I can't figure out; why I have to close the report and the other form, frmMain when I want to delete the record from frmNewNames. Of couse, if I am only using frmNewNames and want to delete a record, the delete command works fine. But when I go through the process of running the report, bringing up both records and then try to delete the record in frmNewNames, it won't.

 

[Cronk]

Post a sample of your database.

 

[Eljefegeneo]

Attached is my sample DB. I saw that I had not included Option Explicit and now the code won't work. And I am really confused. Thanks in advance for taking a look at it.
I have put the Rem mark in front of the code on the delete button in frmNewNames. If that code is present, it used to work ok. Now I just don't know.

 

[Cronk]

You have found the very reason for including Option Explicit - to find bugs a lot easier. And also to show up situations which might lead to funny results when running. You can have this automatically inserted in any new module through setting the Options in the code window ie Tools | options | Require variable declaration

Your problem is that you are trying to modify a built in Access event command syntax. The OnClick event gives the construct
Private Sub cmdDelete_Click()

You have modified this to
Private Sub cmdDelete_Click(Cancel as Boolean)

Some events eg OnOpen have a Cancel option but not OnClick

Remove the Cancel bit and replace the line after the Msgbox with
Exit sub

I haven't tested further but come back if problems continue.

 

[Eljefegeneo]

Thank you for your help. I did as you said, deleted the "Cancel as Integer" or "Cancel as Boolean", deleted the Cancel event and added Exit Sub and it worked fine. .
Lots of time spent on a simple thing, but it does add to my knowledge for which I am very grateful.

 

[Eljefegeneo]

FYI: I added Option Explicit to all the modules and found numerous mistakes. A lesson learned the hard way!