Dlookup not working

[Simon]

I have writen some code that will email customer with the products
they have ordered using tblOrderProducts

strItemsOrdered = strItemsOrdered & "Product CodeNotworking : " &
DLookup("[ProductCode]", "tblOrderProduct", "[OrderNumber] =" &
("OrderNumber")) & vbCrLf & "Product Code : " & CRS1.Fields
("ProductCode") & vbCrLf & "Product Name : " & CRS1.Fields
("ProductName") & vbCrLf & "Qty : " & CRS1.Fields
("Quantity") & vbCrLf & vbCrLf


The output is

Product CodeNotworking : T1GN
Product Code : C11
Product Name : LCD TV
Qty : 2


Product CodeNotworking : T1GN
Product Code : A8
Product Name : Digital Camera
Qty : 2

Product CodeNotworking : T1GN
Product Code : FREE
Product Name : Free Gift
Qty : 1

As u can seee Product CodeNotworking display T1GN on every item, this
should be the same as 'Product Code' also this customer has not
ordered a product with T1GN product code so not sure why its dispaying
this

Could your let me know what would be wrong

Thanks

 

[Clifford Bass]

Hi Simon,

Do you need a CRS1.Fields in front of the ("OrderNumber")?

Clifford Bass

 

[John W. Vinson]

I have writen some code that will email customer with the products
they have ordered using tblOrderProducts

strItemsOrdered = strItemsOrdered & "Product CodeNotworking : " &
DLookup("[ProductCode]", "tblOrderProduct", "[OrderNumber] =" &
("OrderNumber")) & vbCrLf & "Product Code : " & CRS1.Fields
("ProductCode") & vbCrLf & "Product Name : " & CRS1.Fields
("ProductName") & vbCrLf & "Qty : " & CRS1.Fields
("Quantity") & vbCrLf & vbCrLf

This is searching for the ProductCode for those records where OrderNumber
contains the text string "OrderNumber". Where is the actual order number that
you want to look up? In a VBA variable, on a Form control, or where? If CRS1
is a recordset, why not use the simpler syntax CRS1!ProductCode rather than
using the Fields() collection?