By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Write conflict when form is requeried after record is deleted.

P: 76
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:

Expand|Select|Wrap|Line Numbers
  1. strList = cbListFilter.Value
  2. iresponse = MsgBox("Are you sure?  If you click yes, data for this prospect will be unrecoverable!", vbYesNo, "Confirm Delete")
  3. If iresponse = vbNo Then Exit Sub
  4. Dim iDelID As Long, iProspectNumber As Long, strListName As String
  5. iProspectNumber = Me![Prospect Number]
  6. strListName = Me![List Name]
  7. iDelID = Me![ID]
  8.  
  9. 'This section of code deletes the record from my main form.
  10. Dim IQRST As New ADODB.Recordset
  11. Dim IQSQL As String
  12. IQSQL = "SELECT * FROM [dbo_PR Only Info] WHERE [ID] = " & iDelID
  13. DoCmd.RunCommand acCmdSaveRecord
  14. IQRST.Open IQSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  15. IQRST.Delete
  16. IQRST.Close
  17. Set IQRST = Nothing
  18.  
  19. 'This section of code deletes records from a link table - this shouldn't
  20. 'come into play here.
  21. IQSQL = "SELECT * FROM [dbo_tblTestLink] WHERE [ProspectList] = '" & strListName & _
  22.         "' AND [ProspectNumber] = " & iProspectNumber
  23. IQRST.Open IQSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  24. If IQRST.RecordCount = 0 Then GoTo Exit_Delete_Current_Record_Click
  25. IQRST.MoveFirst
  26. Do
  27. IQRST.Delete
  28. IQRST.MoveNext
  29. Loop Until IQRST.EOF = True
  30.  
  31. Exit_Delete_Current_Record_Click:
  32. IQRST.Close
  33. Set IQRST = Nothing
  34. ReNumber strList
  35. '------------------------------------------------------------------------------
  36. 'Everything works fine up until this point.  If either of these requery lines
  37. 'are run, I get a write conflict error.
  38. Me.Form.Requery
  39. 'Me.Final_Action.Form.Requery
  40. Exit Sub
Thanks,
Josh
Dec 28 '06 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 30 '06 #2

P: 76
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?
Dec 31 '06 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 31 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You could try putting

Expand|Select|Wrap|Line Numbers
  1. Docmd.Save
Before

Expand|Select|Wrap|Line Numbers
  1. Me.Requery ' replaces Me.Form.Requery as Me actually means the form.
  2.  
Does this make any difference?

Mary
Jan 1 '07 #5

P: 76
You could try putting

Expand|Select|Wrap|Line Numbers
  1. Docmd.Save
Before

Expand|Select|Wrap|Line Numbers
  1. Me.Requery ' replaces Me.Form.Requery as Me actually means the form.
  2.  
Does this make any difference?

Mary
Sorry, Mary. That makes no difference. Any other ideas?
Jan 2 '07 #6

P: 76
Temporary fix - I remove the LinkChildFields and LinkMasterFields 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.
Jan 2 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.