Write Conflict when closing a form where data has changed 1

[jfcj]

Hello and helpppppppppppppp!!!!!!
I am having a problem when I try to change a data field on a form and have tried most of the fixes contained in the write conflict keyword search, but none seems to work.
I open a form to see the rows in a table using the query as the record source:
SELECT * FROM ACCCODES;
There are two fields in the table: NUMBER and DESCRIPT
Then I click a button on the form to modify records which opens another form with a combo box showing all available records. I select a record either by clicking on it or with the up/down arrow keys and the enter key. As I open the modify form, I close the combo box form and the original form and set focus to the DESCRIPT field on the modify form. I change the DESCRIPT field, click the save record button and
Here is my code:

Private Sub Form_AfterUpdate()

Dim db As Database
Dim rec As DAO.Recordset
Dim strOldDescript As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("SELECT * FROM ACCCODES WHERE [NUMBER] = '" & _
Me![ACCCODES.NUMBER] & "'", dbOpenDynaset)

strOldDescript = rec![DESCRIPT]

With rec
.Edit
![DESCRIPT] = strDescript.Value
.Update
Debug.Print ![NUMBER]
Debug.Print ![DESCRIPT]
.Bookmark = .LastModified
.Close
End With


PREVIOUS ATTEMPTS AT CORRECTING THIS PROBLEM:

'DoCmd.RunCommand acCmdSaveRecord
'DoCmd.Close 'Editing form
'Forms!MainForm.Requery
'Me.Recalc
'Me.Refresh


strSave = "YES"

Set rec = Nothing
db.Close
Set db = Nothing

'Forms![ACCOUNT_CODES_MODIFY].Visible = False


This is where I have the problem of WRITE CONFLICT with the three choices:


DoCmd.CloseForm acForm, "ACCOUNT_CODES_MODIFY"


DoCmd.OpenForm "ACCOUNT CODES"

End Sub

Please can anyone suggest something else to try!
Thanks
Francis

 

[PHV]

If your form is bounded to the table you don't need to save the modification with a recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jfcj]

Hello PH,
Thanks for the help!
For the record source, I am using a SQL statement SELECT * FROM ACCCODES. ACCCODES has two field names NUMBER AND DESCRIPT. Are you saying to put the table name ACCCODES on the record source line in the properties? Also, should I not use a recordset and the .edit and .update in my VBA code? I will try that and see what happens.
Thanks,
Francis

 

[mc14702]

I am having the same problem but need to change the record source of the unbound form depending on the criteria the user puts in to search for a record. Some have a where statement and some don't. I am using an .edit and .addnew and when I add a new record it is fine, but when I go back to edit a record and click save, it saves it but then gives the write conflict error when I navigate or click on another button on the main form (versus the subform where the problem is). I have been looking at this for almost a month. Please help!!! I am desparate!

 

[jfcj]

Hello PH,
Thanks for the help!
I changed all the record sources to tables and the write conflict was no more. But what happens if you need to use a SQL query as the record source like I was doing initially. Do you still not use .edit and .update, but how does the record get changed and how do you get around the write conflict error then? Could you provide some suggestions?
Thanks,
Francis

Hello mc14702. Thanks for the post. I had the same experience that you had. When I added records there was no problem but when data was changed I got the write conflict error. PH helped me by suggesting to use a table as the record source, but it sounds like your problem does not allow you to do this! I am hopeful that PH will have some other ideas. I bet he does!!
Thanks again,
Francis

 

[Zion7]

Hello, jfcj & mc14702, if I understand correctly, like PHV said, when it is a bound form (with a recordsource), you do not need to Add.New or .Update. There is no need to open a recordset, it's redundant and as you see, problematic.

This applies to any source, as your recordsource, tables, queries, SQL statements. Once a form is bound, it's virtually, working right from the table, but in a more, user friendly interface. Creating a recordset basically, creates another "user", trying to access the table & hence, the write conflict.
Again, a bound form, "is a table". Any changes you make, are saved automatically. Any queries or other recordsources, using that table, or record, will be changed also. That simple! Basically, only use recordsets in unbound forms, or on unopened tables.

To ensure your edit gets saved, or at least, pronto, try
DoCmd.Save
Me.Refresh
Me.Repaint
Me.Requery

Good Luck!

 

[lewie]

I am also getting a write conflict . I have a main form and a subform. when the main form changes i programmically change the values in the subform on all records. so if i have a main form and 3 records on the subform i change all 4 records. when i click on the main form i am fine. as soon as I click on the subform I get the write conflict.
I use this to document total number of records like 1 of 3, 2 of 3, 3 of 3. so if they want to edit to 4 entries i need to change to 1 of 4, 2 of 4 ect.
If I can't do this can u think of an alternative method for me to maintain a series.
Thanks.
lewie

 

[Zion7]

What are you using?
DAO, ADODB, Docmd.RunSQL?

Possibly a DAO.RecordsetClone may suffer no interference?

 

[lewie]

I am using an update query in the update event of the text box that contains the number of triggers. when it is changed it kicks off the query. I had tried ado with the same result. The only way to avoid this before was to use unbound controls but then i had to jump through hoops cause i used this info to sort my output reports. You would think i could do a save or something to satisfy it.
Lewie

 

[Zion7]

Yes, I agree, you'd think savinging the record, removing the focus, requering or something, would overide this issue.

again, maybe a recordsetclone may work?

it worked for me recently, with active data (had the focus), no writing conflicts.

Maybe worth a try, if you haven't already.