Write Conflict

[Gismo]

Hi all,

Please could you assist?
I select a product from a drop down list, after update it should lookup a value from the same table and update my current table
for some reason it is not doing the update but when I manually click on the update query, I get the write conflict error

My code behind the after update is:

Private Sub Product_AfterUpdate()

DoCmd.SetWarnings False
DoCmd.Save
DoCmd.GoToControl "Qty"
DoCmd.OpenQuery "Update Purchase Request - UOM"
DoCmd.SetWarnings True
End Sub

Below is my update query

UPDATE [Transactions - Procurement - Temp] INNER JOIN Products ON [Transactions - Procurement - Temp].Product = Products.[Short Description] SET [Transactions - Procurement - Temp].UOM = [Products]![UOM]
WHERE ((([Transactions - Procurement - Temp].Product)=[Forms]![Purchase Request]![Product]));

 

[isladogs]

Its likely that the conflict is that you are trying to write to a bound control on a form.
See my article Write Conflict Errors (isladogs.co.uk) for other possible causes & solutions

 

[Gismo]

Its likely that the conflict is that you are trying to write to a bound control on a form.
See my article Write Conflict Errors (isladogs.co.uk) for other possible causes & solutions

The UOM control is a blank field on the temporary file I am updating
Also not sure why the update query does not run after update and only updates when I do a manual click on the query, and then the write conflict
I thought that saving after the update before the query runs would help but it has no effect
I can not really give it a default value

 

[Gismo]

I added zero as a default, the query still does not update but I dont get the write conflict error

When removing the update query when selecting the product and added to after update on the Qty, it works fine, even with no default value in the UOM control

 

[Minty]

If the value you are looking up could be brought into the combo you could simply set the other field to that combo column's value, and not need the update query at all.

Also DoCmd.Save in a form has nothing to do with saving the record, but saves any changes to the form design.

 

[ebs17]

for some reason it is not doing the update

DoCmd.SetWarnings False ... if you close your eyes, you can't see anything that's happening. But experience.

DoCmd.Save ... would save changes to the form definition made via design view. This is different than saving a record that has just been edited.
It is therefore possible that the data record is still being edited and is therefore locked. It's helpful to avoid doing much of this DoCmd stuff.

If.Me.Dirty then Me.Dirty = False

 

[Gismo]

If the value you are looking up could be brought into the combo you could simply set the other field to that combo column's value, and not need the update query at all.

Also DoCmd.Save in a form has nothing to do with saving the record, but saves any changes to the form design.

Assuming I add the UOM to the part number dropdown box, how to I get the information of the second column from the drop down box to the UOM control?

 

[arnelgp]

I select a product from a drop down list, after update it should lookup a value from the same table and update my current table

that is the Problem. Don't you see.
You have same Record (on same table) that is being edited, yet you are Updating it through SQL.
that is definitely a No, no.

include the UOM field in the form and update it on the form.

 

[Gismo]

that is the Problem. Don't you see.
You have same Record (on same table) that is being edited, yet you are Updating it through SQL.
that is definitely a No, no.

include the UOM field in the form and update it on the form.

Hi,

That is what I am doing, once I have selected the product, the UOM should be updated via a query but then I get the write conflict error even though i am not updating the UOM control

If I can include the UOM in the product dropdown box, how do I link the unbound control to the second value in my dropdown box?

 

[arnelgp]

are you Updating Same table that is in the Form?
if you are that is a Write Conflict, since your Form is Dirty but it "sense"
that you already updated it, before the record in the Form got saved.

 

[Gismo]

are you Updating Same table that is in the Form?
if you are that is a Write Conflict, since your Form is Dirty but it "sense"
that you already updated it, before the record in the Form got saved.

Yes I am updating the same table that is in the form

 

[Minty]

Assuming I add the UOM to the part number dropdown box, how to I get the information of the second column from the drop down box to the UOM control?

Simple - but firstly do you need to actually store it or simply display it?
If it's in the Product table anyway you probably shouldn't be storing it.

To display it in an unbound text box use something like

=CboProducts.Column(2)

Assuming the UOM is the third column in the combo rowsource.

If you need it stored then in the after-update event

Me.YourUOMControl = Me.CboProducts.Column(2)

 

[Gismo]

Simple - but firstly do you need to actually store it or simply display it?
If it's in the Product table anyway you probably shouldn't be storing it.

To display it in an unbound text box use something like

=CboProducts.Column(2)

Assuming the UOM is the third column in the combo rowsource.

If you need it stored then in the after-update event

Me.YourUOMControl = Me.CboProducts.Column(2)

Than you, works awesome

Is there a way to link an exchange from the web into access?

 

[Minty]

Than you, works awesome

Is there a way to link an exchange from the web into access?

I'm not sure what you mean?
Do you mean a web page update to access?

 

[Gismo]

I'm not sure what you mean?
Do you mean a web page update to access?

My purchase order form has Vendors from around the globe, if i select a vendor which is not my country, I want to be able to add the exchange rate of that day to the order to calculate the correct total cost
If this could be done automatically this would be great instead of having to look up on the web and then enter the exchange rate

 

[arnelgp]

there is an Access exchange rate in sample database.

 

[Gismo]

there is an Access exchange rate in sample database.

Please could you give me the likn to the sample DB

 

[arnelgp]

you "Search the Forum".

 

[Gismo]

you "Search the Forum".

I found the currency exchange sample DB
Will have a look at it in depth but I dont see a from currency to To currency and the exchange

 

[Pat Hartman]

NEVER, run an update query that will update the current record.
ALWAYS save the current record before opening a different form or report or running any query.