Using Array in Dlookup

[pauline]

Hi,
I'm trying to lookup a price (CstLB) that match the part number
(Part1-11)
on the form and assign it to variable "Cost". I have 11 txtR (txtR1,
txtR2,...) and 11 txtWeight (txtW1, txtW2....)
The result of "Cost" is multiplied by the appropiate txtWeight (1-11) .
First I asked only loop through the Part number that only contain data.
Then
perform the Dlookup, calculate the "FinalCost" and place the result in
the
appropiate textbox (txtBx1, txtBx2, txtBx6....) The code I tried below
is
not working.

Private Sub Calculate_Click()

Dim i As Integer
Dim cost As Currency
Dim PartNo as String
Dim Weight as double, FinalCost as double

PartNo = Me("Part" & CStr(i))
Weight=Me("Wt" & Cstr(i))

For i = 1 To 11
If Not IsNull(PartNo) then
Me("Part" & CStr(i)).SetFocus
Cost=DLookup("[CstLB]", "RawIngredients", "[ID]=PartNo")
FinalCost=Cost* Weight
Me("txtBx" & Cstr(i))=FinalCost
End If

Next i

End Sub
Thank you

PJ

[pietlinden]

Ouch that looks like a lot of work. Why not have a main form for your
bid/invoice whatever, and then put all the parts/raw ingredients in the
subform. then you can just use a domain or domain aggregate function
for your final cost...