Dlookup a value in a table then multiply it with a value in form field X = value in the form field Y

Question

Hello I am using MS Access 2007

I have a table called Extraction Line it is similar to an order line table , within this table are the two fields of concern:

Field: Tonnage:Number data type

Field: worth:Currency data type

What I want to happen is

As soon a user enters the tonnage value

In the afterupdate event for the tonnage field a Dlookup will look at the Resource table and find the Price field (currency data type) It then multiplies the Resource table Price value with the tonnage amount field (e.g) $50 x 5 etc, the $250 value is then added to the worth field.

So far I cannot even get success on using dlookup to get a value from the resource table

My code is in the extraction line form at the afterupdate event for the tonnage field:

Private Sub Tonnage_AfterUpdate()

Dim X As Currency


X = DLookup("[Price]", "[Resource]", "Atomic Number" = "076")  

'X= Nz(DLookup("[Price]", "[Resource]", "Atomic Number" = "076"), 0)

'X = DLookup("[Price]", "[Resource]", "[Atomic number] = Form![Atomic number]") * [Worth]

I wanted to test the simple part of get a value first before doing the multiply and the rest of the requirements but keep getting Null value errors.

I think I am getting Null value errors due to this explanation Null Value is due to...

But of course there will be no value or nothing there as I want dlookup to go get the value first then carry out the maths.

Prior to this way I tried various validation rules within the Extraction Line form but received ?Name errors.

What is the best way to achieve the above. If I can get help to point me in the right direction I can go away and attempt this.

Added this to the control source of the worth form field:=DLookUp("[Price]","[Resource]") . That pulls the first record price value from the resource table. Now working on telling the dlookup that the value must be where the atomic number = extraction line ID and extraction line ID = atomic number as each extraction line has 2 primary keys the atomic number + and extraction id this one failed due to error expected list separator or ) . =DLookUp("[Price]","[Resource]","[Atomic Number] ="'[Extraction ID] And [Extraction ID] = [Atomic Number]'")*[tonnage] — user1655231

Fionnuala Fionnuala · Accepted Answer · 2012-10-20T18:49:56

How about:

Private Sub Tonnage_AfterUpdate()
''It is probably best not to use Currency when you do not know what will
''be returned
Dim varX As Variant

varX = Nz(DLookup("[Price]", "[Resource]", "[Atomic Number] = 076"),0) * [Worth]

If you want to refer to the look-up as a field or control and [Atomic Number] is a numeric field:

varX = Nz(DLookup("[Price]", "[Resource]", "[Atomic Number] = " _
          & MyNumber),0) * [Worth]

In parts:

varX = DLookup("[Price]", "[Resource]", "[Atomic Number] = " & MyNumber)

If varX > 0 then
    MyAnswer = VarX * Me.Worth
End If

Dlookup a value in a table then multiply it with a value in form field X = value in the form field Y

1 Answers