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
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.
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 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.
You could try putting
Before -
Me.Requery ' replaces Me.Form.Requery as Me actually means the form.
-
Does this make any difference?
Mary
You could try putting
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?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |