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
"Boyd L. Colglazier" <(E-Mail Removed)> wrote in message
news:%xvgc.11611$(E-Mail Removed)...
> 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
>
>
|
Similar Threads
