Odd query problem giving me grey hairs.

[BrianE]

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.

 

[BrianE]

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?

 

[DJkarl]

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.

 

[BrianE]

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.