Writing Conflict’

[Bidhan]

Hi,
I have a table (Stock) important field are partsNo., des, qty, pPrice, sPrice, qtyBuy. I make a query based on the table and has one more field SAB(qty+qtyBuy) . I made a form based on the query. User can enter data in any field. When I enter data in ‘qtyBuy’ field I can see the added value by SAB field.
My obligation is to set this value into stock table in qty field. To perform this action I run an action query in a macro in qty field after update event on the form. But when I run the macro, I receive a message ‘Writing Conflict’. If I press ‘save record’ all data are saved expect running macro but if I press copy to clipboard only macro related field(qty in stock table) is updated other fields don’t update.
Please give me a guide what can I do?

Query code is:
“SELECT Stock.partsNo, Stock.des, Stock.qty, Stock.pPrice, Stock.sPrice, Stock.qtyBuy, nz([Stock]![qty])+nz([Stock]![qtyBuy]) AS SAB
FROM Stock;”

Macro code is:
“UPDATE Stock SET Stock.qty = [Forms]![Add Parts]![SAB]
WHERE (((Stock.partsN o)=[Forms]![Add Parts]![partsNo]))”

[NeoPa]

Bidhan,

With your form open, you are basically running a query which locks the underlying tables (at least the records), so any action query on the same tables (as this is) would be likely to clash.
As I suspect that BuyQty is a temporary variable, it should probably not be part of the form but as a separate form-only field. When the operator enters a value in this field (or when the form moves away from the current record) your code should update the Qty field by adding the BuyQty. You could also include the SAB field (Form only - not bound to table) if preferred.

[PEB]

Maybe it should be better to update your fields using VBA in your form instaed using Update Action query?

[NeoPa]

I would always recommend the SQL action query myself, if it can be made to work (My experience is that it performs more efficiently - faster).