Lookup value calculations

[LeizladNeb]

I use the entry into [tblItem]![MouldingID] in a form to lookup a field called [tblMoulding]![MouldingPriceBand]. The field that does this lookup should use the returned value of [tblMoulding]![MouldingPriceBand] in a multiplication, but it only seems to return the first record from the table, even when i change the [tblItem]![MouldingID] and re-calculate, the total remains the same as what it they were with the first mouldingID. guessing its a simple mistake, so any ideas...
This is the code I have:

Private Sub MouldingValue_Enter()
[MouldingValue] = (2 * ([DimWidth] + [DimHeight])) * DLookup("[MouldingPriceBand]", "tblMoulding", "[tblMoulding]![MouldingID]=[MouldingID]&quot
End Sub

Thanks

 

[Trendsetter]

As a rule I am not a lover of DlookUp or any domain function as they are very slow and very cumbersome.

In this case I would assume that each mouldingID has a different PriceBand and that the problem is that the code in use is not finding the correct PriceBand in the table.
I would tend to establish the correct PriceBand on the form in a textbox prior to doing the calculation - this makes sure that you are finding the correct moulding >> Price and it is mush easier to reference a textbox on a form than a figure from a table.
Private Sub MouldingValue_Enter()
DLookup("[MouldingPriceBand]", "tblMoulding", "[tblMoulding]![MouldingID]=[MouldingID]&quot
End Sub
If the MouldingID referenced at the end of the DLookUp is on the form then add the MouldingPriceBand to the qyuery that feeds the form and put in a bound textbox.