Data Has Been Changed Issue

[Boyd L. Colglazier]

I am using command buttons to call code from modules. The first command
button deletes all records from a table. The second command button imports
data into a table, processes it and exports an Excel file. The table used by
the second command button is not related to the table used by the first
command button. The first command button works fine. When a user clicks on
the second command button, a dialog box appears with the following message
"The data has been changed. Another user edited this record and saved the
changes before you attempted to save your changes. Re-edit the record." If
you click on the OK button on the dialog box and click the second command
button again, it works fine. The dialog box is more of an annoyance than
anything, since all else works. I have used SetWarnings False and tried to
trap the error so I could ignore it but neither worked. Any thoughts would
be greatly appreciated. The following is the code behind the command
buttons:

Command button 1

Public Sub DeleteChargeOffRecords()
'Delete Records from Charge Offs & Recoveries Table

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("Charge Offs & Recoveries")

DoCmd.Hourglass True

Do
rec.Edit
rec.Delete
rec.MoveNext
Loop Until rec.EOF

DoCmd.Hourglass False

MsgBox "Records Have Been Deleted From Charge Offs & Recoveries Table"

End Sub


Command button 2

Public Sub CreateCloseChgOffFile()
'Create Closed Charge Off File

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()


DoCmd.TransferSpreadsheet acImport, 8, "Closed Chg Offs Temporary Table", _
"H:\Month End Work Folder\CloseChgOff", True, ""
DoCmd.TransferSpreadsheet acExport, 8, "Closed Chg Offs Temp", _
"H:\Month End Work Folder\Closed Chg Offs MMYY", True, ""

Set rec = db.OpenRecordset("Closed Chg Offs Temporary Table")

Do
rec.Edit
rec.Delete
rec.MoveNext
Loop Until rec.EOF

MsgBox " Closed Charge Off File Has Been Created"

End Sub

 

[TC]

Might not answer your question, but:

(1) Database & recordset variables should be desassigned before you exit
each sub:
set db = ...
set rs = ...
...
SET RS = NOTHING
SET DB = NOTHING

(2) The looping method is a terrible way of deleting records from a table!
Do this instead:
set db = currentdb()
db.execute "DELETE * FROM TheTable", dbfailonerror
...
set db = nothing

HTH,
TC