Dlookup Function- Using a Parameter in the Criteria

[BlueRhinos]

Within a query, I'd like to add a field using the dlookup function. Instead of using static values in the criteria part of the function, I'd like to use a parameter to keep it dynamic. The online example suggests preceding the field value with Form! to tell Access that the field reference, "Person ID," comes from the current form.

When I use this syntax, i get the following error message: "MS Access can't find the name
Form![Person ID] you entered in the expression.

I've experimented by replacing Form! with:
(1) Query!
(2) [query name].[Person ID]

Here's a visual:
Record:
Person Person ID Manager Manager ID NEW DLoopfield (Manager's Manager)
Dave 1 Scott 2 Donna
Scott 2 Donna 3 Erin

so, my function says give me Manager from Table A where the Person ID = Manager ID from this table.

Can someone help me solve...I can't think of any other intuitive syntax.

Thanks!
BlueRhinos

 

[boblarson]

If you are doing this in a query, then you can get the appropriate record for each row by using
MyNewFieldName:Dlookup("YourFieldYouWantReturnedInTheTable", "YourTableName", "[PersonID]=" & [ManagerID])
If the ID field is text then you would need to use quotes on either side:
MyNewFieldName:Dlookup("YourFieldYouWantReturnedInTheTable", "YourTableName", "[PersonID]='" & [ManagerID] & "'")

 

[BlueRhinos]

Bob, this worked!! Thanks for the quick and easy to understand feedback. It was much appreciated.

BlueRhinos