Write Conflict in recordset

[Carnafex]

I have this database that has a form and a subform. The main form governs the OrderID, and the subform contains the parts listing. Each order can have many parts, and a part can have many orders (many -many). Now I have to check for duplicates within the orders, meaning just after you enter an part number for an order, it checks through all the OrderID's to see if there are any duplicates. This I have working, but I get an error when I can between orders.
The code is below, Ill describe a bit more after it.


If (RecSet.EOF)
exit sub
end if

if (part = RecSet.fields("[Part No]")) then
RecSet.edit
RecSet.fields("[Duplicate]") = true
'other stuff
RecSet.update
else
'same but sets duplicate stuff to false
end if

RecSet.movenext


So the code runs quite nicely, but the check boxes governed by Duplicate dont update. However when I move between orderID's in the main form, the program chucks a Write Conflict error.
'This record has been changed by another user since you started...' and some other stuff. If I click Copy to Clipboard or Drop Changes, the fields are updated nicely.
The thing is, I'm the only person using the database at the moment, although it will be used by many people when completed.
I was wondering if anyone knew what this problem was and if there is any way to solve it.
Any help would be appreciated, and just post if you need any more info posted by me.

Cyas
Jason

 

[jon_sg]

I am not an expert but it seems that you are making a change on a form and creating a seperate recordset with the pointer on the same underlying record and specifying a change on this.

As the recordset has changed records on the underlying table or has changes stored in the buffer access sees two objects changing the same record.
I don't know how you specified the recordset on opening but you could try requerying the form or control after you update the recordset.

If you are using ADO it is better to use an update query / SQL statement as the commandtext of and ADODB command object than walking through the recordset.
(I think DAO uses Query Defs)
Then requery the control on the form.

Regards

Jon

 

[Carnafex]

Thanks for the reply.

I gave the requery a try on the form. I had it requery before editing the Record Set, and then ran DoCmd.runcommand accmdsaverecord (I think thats the command) and it now works perfectly.

Cyas
Jason