Dlookup parameter on a form?

[casey]

Does someone know the correct syntax for the Dlookup function when the “where” parameter is the value in a textbox on a form?

Here’s my syntax in the ControlSource property of the textbox...=DLookup("[Quantity]", "ComputerPartsDetail",
"[PartID] =" _ & Forms!SysBuildInvCat1Ver3!Text2).

I think that there may be the problem with the “where” part of the expression ("[PartID] =" _ & Forms!SysBuildInvCat1Ver3!Text2).

Thanks for any input.

 

[Jack Cowley]

DLookup("[Quantity]", "ComputerPartsDetail",
"[PartID] = " & Me![Text2]). This is assuming that Text2 is on the form that is using DLookup. If Text2 is on another open form then try: DLookup("[Quantity]", "ComputerPartsDetail",
"[PartID] = " & Forms!SysBuildInvCat1Ver3!Text2). DLookup should all be on one line...

[This message has been edited by Jack Cowley (edited 12-07-2000).]

 

[casey]

Thank you for your response, Jack.

When I copied your expression into the ControlSource of the textbox, I got a message stating that "the object does not support the Me! object". Have you ever encountered this error? If so is there anything I can do to fix this?

 

[Jack Cowley]

I am not sure why you got that error message. Me is shorthand for [Forms]![FormName] so I do not know why you are getting the error. Try using the explicit syntax which would be [Forms]![FormName]![Text2] instead of Me![Text2] where FormName is the name of the form that Text2 is a part of. If that does not work then something else is going on as that code should do the trick.

 

[rohdem]

The problem lies in the fact that you are not enclosing your text value in single quotes: try
...=DLookup("[Quantity]", "ComputerPartsDetail", "[PartID] = '" & Forms!SysBuildInvCat1Ver3!Text2 & "'"

This way, your criteria string reads:
[PartID] = 'XXXX'

 

[adamcort]

I always find domain aggregate functions to be troublesome (although at times they are just to useful)

Your dlookup should be like this;

=DLookup("[Quantity]", "ComputerPartsDetail",
"[PartID] = _ & [Forms]![SysBuildInvCat1Ver3]![Text2]")

Hope it works.

 

[casey]

Sorry for this late response. Thanks for your input! I did get the DLookup function to work using the full name. Thanks to you all for your help.