Problem with update query not changing all records.

[hawkingsright]

good morning! i have an update query that looks for a product number, updates the cost, and re-calculates the price in two columns based on the salesman's margins. the problem i am having is that it seems to be hit and miss on the first run. if you run it again, it runs the calculations on all the fields. (it has to check / recalculate 16,000 rows.) should this query have vba to make it loop thru the table, and if so, i sure could use some help to write it. i'm still fairly new to access, and want to make sure this is as solid as possible. thanks for your help!

here is the basic layout of the query:
table 1 is newproductq
table 2 is Products

Field: PriceIn
Table: newproductq
Update To: [Products].[Cost]

UNITS
newproductq
[PriceIn]/((100-[Margin])/100)

PIECES
newproductq
[PriceIn]/((100-[BrokenMargin]/100)

 

[MarkK]

A query doesn't necessarily raise an error in VBA if it fails. Make sure you use the dbFailOnError option, for instance, I have a table tTestData with a currency field called Dollars, and if I run this query, which should raise a division by zero error . . .

    dbs.Execute "UPDATE tTestData SET dollars = 1 / 0 WHERE ID = 26"

. . . it silently sets the Dollars field to Null for the given record. If I add the dbFailOnError option . . .

    dbs.Execute "UPDATE tTestData SET dollars = 1 / 0 WHERE ID = 26", [COLOR="DarkRed"]dbFailOnError[/COLOR]

. . . then DAO raises it's error into VBA and I can trap it, handle it, and the update doesn't occur.

Maybe your intermittent query performance is an error in DAO that you know nothing about.

hth

 

[hawkingsright]

thank you for the suggestion. i am not seeing an error though. i have to run the query twice to get it to complete the operation. small issue, but annoying non the less.

 

[MarkK]

So you added the dbFailOnError option in your code?

 

[Brianwarnock]

Personally I would not store the calculated fields in a table but calculate them as required for reports etc.

Brian