Dlookup one more time...

[Gib]

I made the changes recommended before and it solved one
problem and another occured. Code is as follows:

'Find the record that matches the control.
Dim rs As Object
Dim TotReq As Variant
Dim PlntID As Variant, PlntName As Variant, LvlCdeID
As Variant, LevCdDespt As Variant
Set rs = Me.Recordset.Clone
rs.FindFirst "[CommodityID] = '" & Me![CommNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'Get Variables
PlntID = DLookup
("[PlantID]", "tblMasterList", "[CommodityID] =
CommNo") 'Works (Value is "I0008")
LvlCdeID = DLookup
("[LevelCodeID]", "tblMasterList", "[CommodityID] =
CommNo") 'Works (Value is 3)
PlntName = DLookup
("[PlantName]", "tblPlant", "[PlantID] ='" & PlntID
& "'") 'Works (Value is "Navigation")

'**********************************PROBLEM*************
***********************
'Note: With LvlCdeID =3, Value of LevCdDespt should
be "Inventory to redress 1 tool"

LevCdDespt = DLookup
("[LeveCodeDescription]", "tblLevelCode", "[LevelCodeID]
=LvlCdeID") 'Run Time Error '2001': You Cancled the
Previous Operation
LevCdDespt = DLookup
("[LeveCodeDescription]", "tblLevelCode", "[LevelCodeID]
='" & LvlCdeID & "'") 'Run Time Error '3464' ata Type
Mismatch in Criteria Expression

'******************************************************
***********************

Once again, sugestions are appreciated.

Gib

 

[Roger Carlson]

You've got your text files formatted right in the DLookup, but not your
numeric. They should be something like this:

PlntID = DLookup("[PlantID]", "tblMasterList", "[CommodityID] = " & CommNo)

Notice that the quote mark is just after the (=) and there is none after the
variable name.