Help with write access error.

[jpatchak]

Hi,

I have a form with a delete button. Each record is stored with a list name (e.g. "October" or "November"). Each record within a list name is consecutively numbered from 1 to n. This number is called the "prospect number" I have a button on my form that deletes the current record and then renumbers the records that share the deleted record's list name. The problem is, the current record's prospect number is displayed in a bound textbox control on the form, so when I attempt to navigate through the records after deleting one, I get the Write-access error that tells me another user is edited the record since it was last saved. If I "x" out of the error message, everything is fine. I don't want the message to appear, though. I have tried manipulating the controlsource of the textbox to no avail. Below is my code. Please help.

strlist = cbListFilter.Value
iresponse = MsgBox("Are you sure? If you click yes, data for this prospect will be unrecoverable!", vbYesNo, "Confirm Delete")
If iresponse = vbNo Then Exit Sub
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Dim db As DAO.Database
Dim IQrst As DAO.Recordset
Dim IQstrSQL As String
Dim i As Integer
IQstrSQL = "Select * FROM [PR Only Info] WHERE[List Name] = """ & strlist & """ ORDER BY [Prospect Number]"
Set db = CurrentDb
Set IQrst = db.OpenRecordset(IQstrSQL, dbOpenDynaset)
IQrst.MoveFirst
i = 1
Do
IQrst.Edit
IQrst![Prospect Number] = i
IQrst.Update
IQrst.MoveNext
i = i + 1
Loop Until IQrst.EOF
IQrst.Close
Set IQrst = Nothing
Set db = Nothing

Exit_Delete_Current_Record_Click:
Exit Sub
Err_Delete_Current_Record_Click:
MsgBox "Deletion was not successful."
Resume Exit_Delete_Current_Record_Click

[NeoPa]

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

I think this all hinges on the two lines of code here.
Unfortunately, the only documentation I can find about them says don't use this method.
You use parameters 8 & 6 which are not in the documentation, and you haven't put any comments in your code to explain what you're trying to do.
I would guess some type of requery is required after whichever one does the delete, but it's hard to confirm.

[jpatchak]

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

I took these lines of code out of a macro generated by the command button wizard for deleting records. Is there a better way to delete the current record programatically? Also, I've tried doing a requery on the form and the control itself, but I get the same error message. The lines of code listed above do delete the record as desired. The problem seems to be after the renumbering. The record's old Prospect Number is still displayed in the textbox, so when I move to a different record, there is a conflict between what my form is trying to save in the text box and what my VB code did.

[NeoPa]

I took these lines of code out of a macro generated by the command button wizard for deleting records.

That is so typical of MS.
All the recommendations they publish of how to code clearly, and standards that should be used - and they completely ignore that and their own help file info.
I'm afraid, while I can look at this sort of code and spot problems (sometimes) I'm not very experienced in processing through recordsets in code.
I had experience many years ago now, that indicated recordsets processed via code ran many many times more slowly than simply executing the SQL itself, so I steer clear when I can.

[MMcCarthy]

You could try putting in On Error Continue somewhere in the loop to catch the error. You could also try a DoEvents command to see if this will solve the problem. If either of these don't solve the problem let me know and I'll look at it again.

Do
On Error Continue
IQrst.Edit
IQrst![Prospect Number] = i
IQrst.Update
DoEvents
IQrst.MoveNext
On Error Continue
i = i + 1
Loop Until IQrst.EOF

[jpatchak]

Thanks, but this part of the code is working correctly and I'm already trapping errors on this sub. The problem is after the code runs. The code runs and everything is fine. The deleted record is gone from the table and form. However, once the record is deleted, the record that now shows in my form is showing its OLD prospect number. Thus, when I try to move off this record with the form, I get the write conflict error. It's trying to save the number in the textbox (which is the old number) when the recordset has already been modified. I thought a refresh or requery should do the trick but it doesn't.

[MMcCarthy]

Thanks, but this part of the code is working correctly and I'm already trapping errors on this sub. The problem is after the code runs. The code runs and everything is fine. The deleted record is gone from the table and form. However, once the record is deleted, the record that now shows in my form is showing its OLD prospect number. Thus, when I try to move off this record with the form, I get the write conflict error. It's trying to save the number in the textbox (which is the old number) when the recordset has already been modified. I thought a refresh or requery should do the trick but it doesn't.

What is the control source of the ProspectNumber

[jpatchak]

The control source of that text box is the field that was updated when the delete/renumber code ran. I tried setting it to "" before the renumbering and then resetting it to [Prospect Number] after the renumbering but that didn't work either.

[MMcCarthy]

The control source of that text box is the field that was updated when the delete/renumber code ran. I tried setting it to "" before the renumbering and then resetting it to [Prospect Number] after the renumbering but that didn't work either.

Run a save before opening the recordset. If that doesn't work put Me.Requery in the same spot. The problem I think you're having is you're trying to open a recordset before the change is committed.

If either of these doesn't work let me know as there are some other things we could try.


strlist = cbListFilter.Value
iresponse = MsgBox("Are you sure? If you click yes, data for this prospect will be unrecoverable!", vbYesNo, "Confirm Delete")
If iresponse = vbNo Then Exit Sub
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim db As DAO.Database
Dim IQrst As DAO.Recordset
Dim IQstrSQL As String
Dim i As Integer
IQstrSQL = "Select * FROM [PR Only Info] WHERE[List Name] = """ & strlist & """ ORDER BY [Prospect Number]"
Set db = CurrentDb
Set IQrst = db.OpenRecordset(IQstrSQL, dbOpenDynaset)
IQrst.MoveFirst
i = 1
Do
IQrst.Edit
IQrst![Prospect Number] = i
IQrst.Update
IQrst.MoveNext
i = i + 1
Loop Until IQrst.EOF
IQrst.Close
Set IQrst = Nothing
Set db = Nothing

Exit_Delete_Current_Record_Click:
Exit Sub
Err_Delete_Current_Record_Click:
MsgBox "Deletion was not successful."
Resume Exit_Delete_Current_Record_Click

[jpatchak]

Beautiful! thanks!

[MMcCarthy]

Beautiful! thanks!

No problem.

Glad it worked.