Write conflict when form is requeried after record is deleted.

[jpatchak]

Hello,

I have a main form with one subform. I have a command button on the main form to delete the current record. Below is my code. The tables on which the main form and subform are based cascade deletions and updates, so getting rid of the record in the table for the main form should delete any corresponding records in the table for the subform. Everything appears to be working correctly in this code from the standpoint of the tables. However, when I requery the form (or subform), I get write conflict errors, saying "This record has been changed by another user since you started editing..." I am offered the options of dropping the changes or copying to the clipboard. There is another button called "Save Record" that is disabled on this error message.

A couple of additional facts that might be relevant - the form is an .mdb file that is linked to tables hosted on SQL Server. Like I said, it appears that everything is working fine from the perspective of the tables, so I'm not sure that is relevant. Here is my code for the command button:

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
Dim iDelID As Long, iProspectNumber As Long, strListName As String
iProspectNumber = Me![Prospect Number]
strListName = Me![List Name]
iDelID = Me![ID]
 
'This section of code deletes the record from my main form.
Dim IQRST As New ADODB.Recordset
Dim IQSQL As String
IQSQL = "SELECT * FROM [dbo_PR Only Info] WHERE [ID] = " & iDelID
DoCmd.RunCommand acCmdSaveRecord
IQRST.Open IQSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
IQRST.Delete
IQRST.Close
Set IQRST = Nothing
 
'This section of code deletes records from a link table - this shouldn't
'come into play here.
IQSQL = "SELECT * FROM [dbo_tblTestLink] WHERE [ProspectList] = '" & strListName & _
        "' AND [ProspectNumber] = " & iProspectNumber
IQRST.Open IQSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If IQRST.RecordCount = 0 Then GoTo Exit_Delete_Current_Record_Click
IQRST.MoveFirst
Do
IQRST.Delete
IQRST.MoveNext
Loop Until IQRST.EOF = True
 
Exit_Delete_Current_Record_Click:
IQRST.Close
Set IQRST = Nothing
ReNumber strList
'------------------------------------------------------------------------------
'Everything works fine up until this point.  If either of these requery lines
'are run, I get a write conflict error.
Me.Form.Requery
'Me.Final_Action.Form.Requery
Exit Sub

Thanks,
Josh

[NeoPa]

I think that's all relevant information.
The cascaded delete done on the SQL server is recognised as another user...
What I don't understand is why it thinks doing a requery would try to update any data in the first place :s.

[jpatchak]

I think that's all relevant information.
The cascaded delete done on the SQL server is recognised as another user...
What I don't understand is why it thinks doing a requery would try to update any data in the first place :s.

Do you think this is something I should take over to the SQL Server forum?

[NeoPa]

You could try, but on balance I would guess this is an Access issue.
I'm not sure that it's one that can be resolved remotely though I'm afraid. Too many ponderables and too much info you would never think to post. One of those problems that you wouldn't do if you realised what it was you were doing sort of thing.

[MMcCarthy]

You could try putting

Docmd.Save

Before

Me.Requery ' replaces Me.Form.Requery as Me actually means the form.
 

Does this make any difference?

Mary

[jpatchak]

You could try putting

Docmd.Save

Before

Me.Requery ' replaces Me.Form.Requery as Me actually means the form.
 

Does this make any difference?

Mary

Sorry, Mary. That makes no difference. Any other ideas?

[jpatchak]

Temporary fix - I remove the LinkChildFields and LinkMasterField s properties of the subform before running the ADODB code. Then, just before the main form is requeried, I add them again. This seems to be working - but it's kind of lame.