DLookup not working

[Nylex]

I am using the following function but it does not get the correct answer
----
Private Sub UnitPrice_Enter()

Dim CostPrice As Currency
Dim RecNo As Double
Dim Grde As String
Dim CostGroup As String

'customers grade
RecNo = [Forms]![Orders]![CustomerID]
Me![testone] = RecNo

Grde = DLookup("Grade", "Customers", RecNo)
Grde = Trim(Grde)
CostGroup = "RRP"

If Grde = "A" Then CostGroup = "GradeA"
If Grde = "B" Then CostGroup = "GradeB"
If Grde = "C" Then CostGroup = "GradeC"
If Grde = "D" Then CostGroup = "GradeD"

RecNo = Me![ProductID]
Me![testtwo] = RecNo

CostPrice = DLookup(CostGroup, "Products", RecNo)


UnitPrice = CostPrice


End Sub
________
In the first time RecNo =30
In the 2nd time RecNo= 1849

The lookup function gets record 1 (one) in the table's both times - not the
one it should be getting so wrong answers

Any help would be appreciated

 

[UpRider]

You should be able to do something like this:

Grde = DLookup("Grade", "Customers", "CustomerID= " &
Forms!Orders!CustomerID)

CostPrice = DLookup(CostGroup, "Products", "ProductID= " & Me.ProductID)

This assumes the PK in the Customers table is CustomerID and
the PK in Products table is ProductID.

HTH, UpRider

 

[Nylex]

Tks - that worked perfectly

You should be able to do something like this:

Grde = DLookup("Grade", "Customers", "CustomerID= " &
Forms!Orders!CustomerID)

CostPrice = DLookup(CostGroup, "Products", "ProductID= " & Me.ProductID)

This assumes the PK in the Customers table is CustomerID and
the PK in Products table is ProductID.

HTH, UpRider

I am using the following function but it does not get the correct answer
----
Private Sub UnitPrice_Enter()

Dim CostPrice As Currency
Dim RecNo As Double
Dim Grde As String
Dim CostGroup As String

'customers grade
RecNo = [Forms]![Orders]![CustomerID]
Me![testone] = RecNo

Grde = DLookup("Grade", "Customers", RecNo)
Grde = Trim(Grde)
CostGroup = "RRP"

If Grde = "A" Then CostGroup = "GradeA"
If Grde = "B" Then CostGroup = "GradeB"
If Grde = "C" Then CostGroup = "GradeC"
If Grde = "D" Then CostGroup = "GradeD"

RecNo = Me![ProductID]
Me![testtwo] = RecNo

CostPrice = DLookup(CostGroup, "Products", RecNo)


UnitPrice = CostPrice


End Sub
________
In the first time RecNo =30
In the 2nd time RecNo= 1849

The lookup function gets record 1 (one) in the table's both times - not
the
one it should be getting so wrong answers

Any help would be appreciated