DLookup not working

N

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
 
U

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
 
N

Nylex

Tks - that worked perfectly

UpRider said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top