DELETE Code Error

[ytorres]

Hi!

I have a delete code that runs on the OnClick of a command button. For some reason, in my computer, the application is freezes and then closes when i am running this code...

I have no idea of what could be causining this. I tried the application at my home computer and there if worked fine.

I am using Access 2000 and the delete routine is the following:


Dim qdfDelete As QueryDef
Dim strDelete As String
Dim rstCAPAs As DAO.Recordset

Set rstCAPAs = CurrentDb.OpenRecordset("SELECT [Corrective and Preventive Actions].InvestigationNo, " & _
"[Corrective and Preventive Actions].ActionCode, [Corrective and Preventive Actions].ActionNo " & _
"From [Corrective and Preventive Actions] " & _
"WHERE ((([Corrective and Preventive Actions].InvestigationNo)= """ & [Forms]![sfrmCorrectiveActions]![txtInvestigationNo] & """) AND (([Corrective and Preventive Actions].ActionCode)= """ & [Forms]![sfrmCorrectiveActions]![txtActionCode] & """) AND (([Corrective and Preventive Actions].ActionNo)=" & [Forms]![sfrmCorrectiveActions]![txtActionNo] & "));")

If rstCAPAs.RecordCount <> 0 Then
strDelete = "DELETE * " & _
"FROM [Corrective and Preventive Actions] " & _
"WHERE ((([Corrective and Preventive Actions].InvestigationNo)=""" & [Forms]![sfrmCorrectiveActions]![txtInvestigationNo] & """) AND (([Corrective and Preventive Actions].ActionCode)=""" & [Forms]![sfrmCorrectiveActions]![txtActionCode] & """) AND (([Corrective and Preventive Actions].ActionNo)=" & [Forms]![sfrmCorrectiveActions]![txtActionNo] & "));"

'Create new query definition
Set qdfDelete = CurrentDb.CreateQueryDef("", strDelete)
'Execute delete query
qdfDelete.Execute
[Forms]![sfrmCorrectiveActions].Requery
End If

PLEASE HELP ME!!

Yolanda

 

[PHV]

Why not just launch the delete unconditonnally, like this ?
DoCmd.RunSQL "DELETE * " & _
"FROM [Corrective and Preventive Actions] " & _
"WHERE ((([Corrective and Preventive Actions].InvestigationNo)=""" & [Forms]![sfrmCorrectiveActions]![txtInvestigationNo] & """) AND (([Corrective and Preventive Actions].ActionCode)=""" & [Forms]![sfrmCorrectiveActions]![txtActionCode] & """) AND (([Corrective and Preventive Actions].ActionNo)=" & [Forms]![sfrmCorrectiveActions]![txtActionNo] & "));"
[Forms]![sfrmCorrectiveActions].Requery

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ytorres]

Hi!

Thanks so much!

Your suggestion worked! BUT... after the delete action is completed, it still freezes and then the application closes completely...

Any suggestions as to what could cause a database to close by itself?

Thanks!

Yolanda

 

[PHV]

Have you tried to repair the database ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ytorres]

Yes, I just ran Compact and Repair Database.

Th OnCurrent of the form where the delete is ocurring only does certain field checks in order to enable or disable fields...

It also contains a subform but I disabled the OnCurrent code on that one and the problem still occured.

Any other suggestion?

Thank you so much,

Yolanda

 

[PHV]

Have you tried to put a break-point in your code and then single step them to figure out what happens ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ytorres]

Yes, but it won't let me debug... if freezes, perhaps i am setting the break point in the incorrect part of the code.

I just deactivated the OnCurrent code of the form and the Delete worked perfectly.

It seems like there is something there that is causing the application to halt and crash.

The problem is that i need all those field checks to comply with user requirements...

Thanks again so much for your time and help.

Yolanda

 

[PHV]

Put your break-point on the DoCmd.RunSQL "DELETE..." line

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ytorres]

OK, I placed the break-point where you suggested. I now know that when after the delete occurs and then the form requeries it first goes to the OnCurrent event of the subform.

There it freezes. I tried eliminating some of the conditions that the application checks for in that event and this error popped:

Run-time Error '2424'

The expression you entered has a field, control or property that Investigation Application can't find.

Thanks again!

Yolanda

 

[ytorres]

Now the error i mentioned on the previous post is not coming up, added some code to prevent the application from verifying a field that at the time was being deleted...

When i run it step by step, after the delete runs and its the turn of the form requerey it goes first to the subform Current event there it kind of freezes and it takes a looooooong time to move from sentence to sentence... i mean a loooooong time. It even takes forever to move from the End If to the End Sub and to get out of the End Sub.

When it is done, the screen comes up clean but as soon as i click on any field in the form that contains the subform, the record selector indicator changes to:
-2147467262 of -2417467262

And then if I click on Close (which is the button to close the form only), the whole database closes.

Please help me!

Yolanda