MS Access #Error on DLOOKUP

1
votes

Thanks in advance for any help!

MS Access 2007, trying to use the below DLOOKUP statement and receiving the "#ERROR".

=DLookUp("[Description]","UP Desc Contact Website Table","[Ultimate Parent Name]='" & [Forms]![Ultimate Parent Master List]![List8].[Column](0) & "'")

Confirmed that just the expression & domain return a random value.
Confirmed that "[Ultimate Parent Name]='STRING'" returns the correct value and confirmed that [Forms] returns a string. [Forms] is from outside the parameter of "UP Desc Contact Website Table", but I'm unclear whether that matters.

Putting this formula into a text box on a form. Any idea whats going on?

1
ms-access
Is Forms a textbox or other type of control on your form? It might be that Access is getting mixing up the control name with the fully qualified control naming convention of [Forms]![<formname>]![<controlname>]. Might try renaming the textbox/control to txtForms. – John Mo
That just looks weird - what are you trying to lookup? Are you trying to look for the string "[Forms]" in your table's field? If so you need to eliminate the concatenation "&" and just put it in between the quotes. If not what exactly are you trying to get back from "Forms" - that's a collection not a string in Access. If it IS a textbox - than follow @JohnMo and his advice above – dbmitch
You also need to wrap "UP Desc Contact Website Table" in brackets - [UP Desc Contact Website Table]. – Kostas K.
Not sure why the whole thing wasn't coming through before, but changed the format to code to show the whole bit after [Forms] – N.J.
Try putting =[Forms]![Ultimate Parent Master List]![List8].[Column](0) in the Control Source property of a text box to see what is being fed to you DLookup function's WHERE parameter. – John Mo

1 Answers

0
votes

Assuming textbox and listbox are both on the form [Ultimate Parent Master List] and [Ultimate Parent Name] field is a text type, remove the form reference so simply:

=DLookUp("[Description]", "UP Desc Contact Website Table", "[Ultimate Parent Name]='" & [List8].[Column](0) & "'")

As soon as item selected in listbox, the textbox calculates.

How large is Description field? Maybe eliminate DLookup by including the Description field in a column of Listbox. The column can be hidden. If Description is in second column, its index will be 1: =[List8].[Column](1). Be sure to set ColumnCount and ColumnWidths properties to handle the multiple fields returned by RowSource.

The table/query reference argument will work without brackets, a weird quirk of domain aggregate functions, but certainly won't hurt to use.