Help with "Record is Deleted" message

[jpatchak]

Hello all,

I have an annoying message popping up every time I close a form. Due to performance issues, I am caching data from a linked table on a SQL Server into a temporary local table. The user makes changes to data that are stored in the local table. Then, when the user closes the form, I run a couple of quick queries to update the data back on the server and delete any data in the temp table. After the code runs in the Close event of the form, I get a popup message saying "Record is Deleted" and nothing more. Here is the code from my Form_Close event:

Private Sub Form_Close()
'Display a form letting the user know that the changes are being saved to the server
DoCmd.OpenForm "Saving_Form", acNormal
[Forms]![Saving_Form].Repaint
 
DoCmd.SetWarnings False
'Delete all records in the server that need updating
DoCmd.RunSQL "DELETE * FROM [dbo_tblMaintSteps] WHERE [ProspectKey] IN (SELECT [ProspectKey] FROM TEMP_MAINT_STEPS)"
'append replacement records to the server
DoCmd.RunSQL "INSERT INTO [dbo_tblMaintSteps] SELECT * FROM TEMP_MAINT_STEPS"
'Delete all records in the temporary table
DoCmd.RunSQL "DELETE * FROM [TEMP_MAINT_STEPS]"
DoCmd.Close acForm, "Saving_Form"
DoCmd.SetWarnings True
End Sub
 

Any suggestions? The error message appears after the last line of code here runs. Even if I keep SetWarnings set to False, I get the error message.

[puppydogbuddy]

Hello all,

I have an annoying message popping up every time I close a form. Due to performance issues, I am caching data from a linked table on a SQL Server into a temporary local table. The user makes changes to data that are stored in the local table. Then, when the user closes the form, I run a couple of quick queries to update the data back on the server and delete any data in the temp table. After the code runs in the Close event of the form, I get a popup message saying "Record is Deleted" and nothing more. Here is the code from my Form_Close event:

Private Sub Form_Close()
'Display a form letting the user know that the changes are being saved to the server
DoCmd.OpenForm "Saving_Form", acNormal
[Forms]![Saving_Form].Repaint
 
DoCmd.SetWarnings False
'Delete all records in the server that need updating
DoCmd.RunSQL "DELETE * FROM [dbo_tblMaintSteps] WHERE [ProspectKey] IN (SELECT [ProspectKey] FROM TEMP_MAINT_STEPS)"
'append replacement records to the server
DoCmd.RunSQL "INSERT INTO [dbo_tblMaintSteps] SELECT * FROM TEMP_MAINT_STEPS"
'Delete all records in the temporary table
DoCmd.RunSQL "DELETE * FROM [TEMP_MAINT_STEPS]"
DoCmd.Close acForm, "Saving_Form"
DoCmd.SetWarnings True
End Sub
 

Any suggestions? The error message appears after the last line of code here runs. Even if I keep SetWarnings set to False, I get the error message.

If this message is related to deleting the Access temporary table, you can try the following:

1. Go to the Access command menu
Select Tools>Options> Edit/Find Tab > uncheck confirm doc deletions

[jpatchak]

Thanks for the thought, but that doesn't work. What I'm getting is more of an alert than a confirmation. It's a msgbox with the title "Microsoft Office Access." The content is just "Record is deleted." and there is an OK and a help button on it.

[puppydogbuddy]

Thanks for the thought, but that doesn't work. What I'm getting is more of an alert than a confirmation. It's a msgbox with the title "Microsoft Office Access." The content is just "Record is deleted." and there is an OK and a help button on it.

It is not an alert, otherwise it would be supressed by your Set Warnings code. Based on the message text, I think it is error #3167. If it is, you need to add error handling code to trap/intercept it and display your own custom message.

[jpatchak]

I tried trapping the error by adding the line

Msgbox Err.Description

at the end of the close event of the form. The msgbox I created displayed nothing, but I still get that message from Access. Also tried adding

On Error Resume Next

at the beginning of the event, but I still get the message from Access. I dont' think I can trap this.

Do you know what executes after the form closes? Is there another event that fires or something? If I put breaks in everyline of the code and step through, the error does not appear until after the last line in this event fires. Interestingly, it makes no difference what that line of code is.

[Nathan H]

I tried trapping the error by adding the line

Msgbox Err.Description

at the end of the close event of the form. The msgbox I created displayed nothing, but I still get that message from Access. Also tried adding

On Error Resume Next

at the beginning of the event, but I still get the message from Access. I dont' think I can trap this.

Do you know what executes after the form closes? Is there another event that fires or something? If I put breaks in everyline of the code and step through, the error does not appear until after the last line in this event fires. Interestingly, it makes no difference what that line of code is.

Could the alert be passed through from the SQL server? Out of my knowledge base, so it may be impossible to work that way, but it has to be coming from somewhere right?

[jpatchak]

I'm not sure. All work on the SQL server is done on lines 7-10 above

 'Delete all records in the server that need updating
DoCmd.RunSQL "DELETE * FROM [dbo_tblMaintSteps] WHERE [ProspectKey] IN (SELECT [ProspectKey] FROM TEMP_MAINT_STEPS)"
'append replacement records to the server
DoCmd.RunSQL "INSERT INTO [dbo_tblMaintSteps] SELECT * FROM TEMP_MAINT_STEPS"

I would think that if the server was going to throw an error it would be after one of those lines runs. The error doesn't come until after the form closes. The record source of the form is the local table - not the table on the server. If I remove the line that deletes the records from the local table

DoCmd.RunSQL "DELETE * FROM [TEMP_MAINT_STEPS]"

then I don't get the error. It's almost like the form is trying to save a change to the temporary table after the form closes (maybe)? I really want to leave that line of code in just in case the user has to end task on Access instead of closing out of the form. Then when the user starts the form back up, if there are records in that table, I can give the user the option of restoring the session.

[Denburt]

It's almost like the form is trying to save a change to the temporary table after the form closes (maybe)? I really want to leave that line of code in just in case the user has to end task on Access instead of closing out of the form. Then when the user starts the form back up, if there are records in that table, I can give the user the option of restoring the session.

It sounds as though you are probably on the right track. Your problem also might be in the following lines if the insert does not complete before the delete runs...

DoCmd.RunSQL "INSERT INTO [dbo_tblMaintSteps] SELECT * FROM TEMP_MAINT_STEPS"
'Delete all records in the temporary table
DoCmd.RunSQL "DELETE * FROM [TEMP_MAINT_STEPS]"

Try adding DBEngine.Idle or DoEvents between the two commands. It could be a possible solution if that is the issue

[puppydogbuddy]

OK, I think I found out why the error is occurring. It happens because one user deletes a record while the other is using the navigation buttons to move between records, and in the process, passes over the deleted record before the form can be requeried.

You can prevent this error by running Access in exclusive mode. If that is not feasible, you can try the following and see if it works.

Exit_Continue:
Exit Sub
 
Error_Handler:
    If Err.Number = 3167 Then             'record is deleted error 
    Err.Clear
    Me.Requery    
        Resume Exit_Continue
    Else
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
    End If

[jpatchak]

OK, I think I found out why the error is occurring. It happens because one user deletes a record while the other is using the navigation buttons to move between records, and in the process, passes over the deleted record before the form can be requeried.

You can prevent this error by running Access in exclusive mode. If that is not feasible, you can try the following and see if it works.

Exit_Continue:
Exit Sub
 
Error_Handler:
    If Err.Number = 3167 Then             'record is deleted error 
    Err.Clear
    Me.Requery    
        Resume Exit_Continue
    Else
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
    End If

I tried trapping the error and the Err object is empty.

[puppydogbuddy]

Ok, then try this modification to your code :

'Delete all records in the temporary table
DoCmd.RunSQL "DELETE * FROM [TEMP_MAINT_STEPS]"
Me.Requery '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
DoCmd.Close acForm, "Saving_Form"

[Nathan H]

I tried trapping the error and the Err object is empty.

Looking at other threads here, does anything strange happen to the table [TEMP_MAINT_STEPS], like errors in the actual text #error (or something of the sorts) after the code runs?

[jpatchak]

Ok, then try this modification to your code :

'Delete all records in the temporary table
DoCmd.RunSQL "DELETE * FROM [TEMP_MAINT_STEPS]"
Me.Requery '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
DoCmd.Close acForm, "Saving_Form"

This didn't work, but caused an error to be thrown in a line of code in a subform's current event. Apparently what was happening was that when the main form was closed, the subform's current event hit after the temp table data was deleted. There was a line in that particular subform's current event that saves data in a field. When it tried to save from that subform, the underlying recordsource's data was gone (the temp table) and thus threw the error. Thank you for the suggestion - it helped me trouble shoot where the error was actually coming from! I basically just cleared the source object and link fields in the subform and requeried before running the delete query on the temp table and that solved it. Thanks again.