473,320 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Write conflict when form is requeried after record is deleted.

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
6 4278
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
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
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

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

Similar topics

6
by: Max | last post by:
Hi, I have SqlServer 2000 as back end and Access 2000 as front-end. All tables from Sqlserver are linked to Access 2000. I am having write conflict problem with one of my form which is bound to...
2
by: RC | last post by:
I am getting the following error message. "Write Conflict this record has been changed by another user since you started edting it. If you save the record, you will overwrite the changes...." I...
9
by: Paradigm | last post by:
I am using an Access2K front end to a MYSQL database. If I enter a new record in a continuous form the record appears as #deleted as soon as I move to a different record in the form until I requery...
5
by: Simon | last post by:
Dear reader, I have two Forms they are both working with dada from the same tables. By typing in some changes in Form-B the changes are also visible in Form-A. There is no record lock...
4
by: crystal | last post by:
I've checked the threads but haven't been able to come up with a solution to my issue. Help...... I have a simple form based on a table. Within the form is a subform that is also, through a Q,...
1
by: lorirobn | last post by:
Hi, I have a query that I have been using as a record source for a form with no problems. I just created a new "addnew" form, and added 20 records to the table with this form. The problem I...
8
by: christianlott1 | last post by:
After searching the group and the net, I just can't believe after all this work I'm going to have to sit through three Write Conflict messages when I want to roll back and delete a record. The...
1
by: stuart | last post by:
I have a list of records in a subform that a user can either edit or delete. This is an unbound form. If the user deletes a record, I want to refresh the form, and then position the cursor on the...
1
by: MerlinS | last post by:
Using Access 2003. I have a form and a subform. On the main form, one of the fields is a lost box, which lists line numbers ie 1, 2, 3 etc. Then the list box is clicked, the subform corresponding...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.