I have a query that updates a field on a table with the value in another field.

When I run the query from the Access control panel, the query does exactly what it should do, 100% perfect. When I call the query using DoCmd.OpenQuery in VBA in an OnClick function on a form, it does not work properly, only appending certain amounts of information, leaving some fields blank.

Can anyone shed some light on this.

the SQL for the query is:

UPDATE tblHolding SET tblHolding.CostGRV = [tblHolding]![OrderCost]
WHERE ((([tblHolding].[Item Code])=[tblHolding]![Item Code]));

It populates another field in the same record in the same table as itself.

tblHolding is populated by a Subform on the Form that has the button that calls this query when clicked.

Hope I am making sense.

Justing wondering if I could solve this problem by calling the SQL in VBA rather than, and then my obvious question, never done SQL in VBA, would I just paste the query SQL in as a srting, and call the string?

Maybe I'm missing something but the where clause in this query

UPDATE tblHolding SET tblHolding.CostGRV = [tblHolding]![OrderCost]
WHERE ((([tblHolding].[Item Code])=[tblHolding]![Item Code]));

seems to indicate update everything where [tblHolding].[Item Code] = itself...this would update every record in the table, if that is your intent then I would suggest removing the where clause all together.

Thanks, removed the redundant line.

Problem still there though. Query runs perfectly when run from the control screen. As soon as it is called from a routine on a form, it fails. Gives a "About to update 0 records".

Trying to figure out if it has anything to do with the table (tblHolding) being open in the subform of the form that calls the routine? If it is, I have no work around...clueless now....in uncharted terrority... and have wasted 8 hours in trying everything to get around this.