dlookup key violation error 3075

[BobbyDazzler]

tblproduct (simplified)
productid(pk)
productcode
productcost
productprice
warrantycode

any warranty claim is invoiced out as the original productcode but
with a w in front e.g. original code 3005015TPR becomes W3005015TPR
(both of which are in [productcode]) the warrantycode field is only
used where the productcode starts with a W then three digits and is
exactly the same as the productcode

this gives two records, the original product with [productcode] as
3005015TPR and [warrantycode] as null, and the warranty product with
[productcode] as W3005015TPR and [warrantycode] as 3005015TPR.

when the warranty product is invoiced I want to use dlookup to find
the cost of the original product so something like

dlookup([productcost],"tblproduct", productcode = warrantycode)

i.e. lookup the cost of the original product.

keep getting errors.

really want to use this in an update query which will select all the
products begining W### and update the productcost of the warranty
product with the productcost of the original product

thanks in advance

[bobh]

On Jan 21, 1:03*pm, BobbyDazzler <[e-mail]:

tblproduct (simplified)
productid(pk)
productcode
productcost
productprice
warrantycode

any warranty claim is invoiced out as the original productcode but
with a w in front e.g. original code 3005015TPR becomes W3005015TPR
(both of which are in [productcode]) *the warrantycode field is only
used where the productcode starts with a W then three digits and is
exactly the same as the productcode

this gives two records, the original product with [productcode] as
3005015TPR and [warrantycode] as null, and the warranty product with
[productcode] as W3005015TPR and [warrantycode] as 3005015TPR.

when the warranty product is invoiced I want to use dlookup to find
the cost of the original product so something like

dlookup([productcost],"tblproduct", productcode = warrantycode)

i.e. lookup the cost of the original product.

keep getting errors.

really want to use this in an update query which will select all the
products begining W### and update the productcost of the warranty
product with the productcost of the original product

thanks in advance

If your warrantycode field is "text" then you have to enclose your
dlookup in quot marks;
dlookup([productcost],"tblproduct","productcode='" & warrantycode &
"'")

hope that helps
bobh.