DLookup Help

[Gib]

I am new to this so please bare with me. I have a form
with a Combobox that has an After Update [Event Procedure]
as follows:

'Find the record that matches the control.
Dim rs As Object
Dim varX, TotReq As Variant
Dim PlntID, PlntName, LvlCdeID, LevCdDespt As Fields
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")
PlntName = DLookup("[PlantName]", "tblPlant", "[PlantID]
='PlntID'")
PlntID is always correct.
PlntName is always 'Null'.

Any Sugestions?

Thanks

 

[Ken Snell]

You need to concatenate the value of a variable into the string for the
WHERE clause. Your syntax is telling the DLookup to find the literal value
PIntID.

Try this:

PlntID = DLookup("[PlantID]", "tblMasterList", "[CommodityID] = CommNo")
PlntName = DLookup("[PlantName]", "tblPlant", "[PlantID] ='" & PlntID & "'")

Note that you can include CommNo inside the string because Jet will properly
identify it as a control / field on your form. But you can't do that with
variables.

I also see that your Dim statements are incorrect. You must expressly
declare each variable as a type -- you cannot "group" them on one line and
then just have the type once at the end. When you don't expressly declare
the type for a variable, it is assigned the Variant type. Change your Dim
statements to these:

Dim rs As Object
Dim varX As Variant, TotReq As Variant
Dim PlntID As Field, PlntName As Field, LvlCdeID As Field, LevCdDespt As
Field


Now, with the above, you are not setting the PlntID , etc. variables to any
fields. So, I am assuming that these are the names of fields in the form's
recordsource? If so, you don't need to dim them at all. They already exist
as fields in the form's recordsource.

 

[Gib]

That did it!

Thanks
Gib

-----Original Message-----
You need to concatenate the value of a variable into the string for the
WHERE clause. Your syntax is telling the DLookup to find the literal value
PIntID.

Try this:

PlntID = DLookup

("[PlantID]", "tblMasterList", "[CommodityID] = CommNo")

PlntName = DLookup("[PlantName]", "tblPlant", "[PlantID] ='" & PlntID & "'")

Note that you can include CommNo inside the string because Jet will properly
identify it as a control / field on your form. But you can't do that with
variables.

I also see that your Dim statements are incorrect. You must expressly
declare each variable as a type -- you cannot "group" them on one line and
then just have the type once at the end. When you don't expressly declare
the type for a variable, it is assigned the Variant type. Change your Dim
statements to these:

Dim rs As Object
Dim varX As Variant, TotReq As Variant
Dim PlntID As Field, PlntName As Field, LvlCdeID As Field, LevCdDespt As
Field


Now, with the above, you are not setting the PlntID , etc. variables to any
fields. So, I am assuming that these are the names of fields in the form's
recordsource? If so, you don't need to dim them at all. They already exist
as fields in the form's recordsource.
--
Ken Snell
<MS ACCESS MVP>

I am new to this so please bare with me. I have a form
with a Combobox that has an After Update [Event Procedure]
as follows:

'Find the record that matches the control.
Dim rs As Object
Dim varX, TotReq As Variant
Dim PlntID, PlntName, LvlCdeID, LevCdDespt As Fields
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")
PlntName = DLookup ("[PlantName]", "tblPlant", "[PlantID]
='PlntID'")
PlntID is always correct.
PlntName is always 'Null'.

Any Sugestions?

Thanks

.