Value from another table

[DJN]

Hi all,
I am trying to put the answer to a calculation into an unbound text box. The problem is that part of the calculation involves a value from another table. I have the following calculation in the On Open event:- Me.txtTestLoad =([Load Required]*1.15)+([Drawer Weight]) Drawer Weight is in another table. I get the error message 'Microsoft cannot find the field Drawer Weight referred to in your expression.' Any help would be appreciated.

 

[jebry]

Hi!

Try the following:

Me.txtTestLoad =([Load Required]*1.15)+DLookUp("Drawer Weight","TableName&quot

If you need to specify a specific record in the table, you can add a clause to get:

DLookUp("Drawer Weight", "TableName", "TableID = somenumber&quot

hth
Jeff Bridgham

 

[Aivars]

Create query with fields of both tables like following:

Private sub Form_Load()
dim rst as recordset
dim strSQL as string

strSQL ="Select Table1.[Load Required]*1.15+Table2.[Drawer Weight] As CalcTestLoad From Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID Where Table1.ID =" & me.ID & ";"
set rst=currentdb.openrecordset(strSQL)
if not rst.eof then
Me.txtTestLoad =rst(0)
else
'........
end if
rst.close
set rst=nothing
end sub

Aivars