title: Dlookup a value in a table then multiply it with a value in form field X = value in the form field Y
body: <p>Hello I am using MS Access 2007 </p>
  
  <p>I have a table called Extraction Line it is similar to an order line table , within this table are the two fields of concern:</p>
  
  <p>Field: Tonnage:Number data type </p>
  
  <p>Field: worth:Currency data type</p>
  
  <p>What I want to happen is </p>
  
  <p>As soon a user enters the tonnage value </p>
  
  <p>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.</p>
  
  <p>So far I cannot even get success on using dlookup to get a value from the resource table </p>
  
  <p>My code is in the extraction line form at the afterupdate event for the tonnage field:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>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.</p>
  
  <p>I think I am getting Null value errors due to this explanation
  <a href="http://www.vbforums.com/showthread.php?401834-Database-Why-do-I-get-the-error-quot-Invalid-use-of-Null-quot-when-using-field-values" rel="nofollow">Null Value is due to...</a></p>
  
  <p>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.</p>
  
  <p>Prior to this way I tried various validation rules within the Extraction Line form but received ?Name errors.</p>
  
  <p>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.</p>