DLookUp Field from Table based on Variable Text Box (1 Viewer)

[malxvc]

-Operating in MS Access 2003-

On a Form, I have a Text Box (txtName) that is populated with a name selected from an unbound List Box.

I would like a separate Text Box (txtReport) to perform a DLookUp to return a value from a table based on the string displayed in txtName.

DLookUp(Field, Table, Criteria)

Field: REPORT_TITLE
Table: Incentive_TEAM_NAME
- Two columns of interest in this table: REPORT_TITLE & TEAM_NAME
Criteria: txtName = TEAM_NAME

Ideally, txtReport will display the value in "REPORT_TITLE" that is in the same row in the table 'Incentive_TEAM_NAME' as the value in "TEAM_NAME".

ex: in the Incentive_TEAM_NAME table...

TEAM_NAME| |REPORT_TITLE
Jones/Smith| |Jones-Smith​

Within the List Box, the user will have selected Jones/Smith. Jones/Smith will display in the txtName Text Box. The use of the DLookUp in txtReport will allow the Text Box to display Jones-Smith.​

Entering the following in the Control Source of txtReport results in a #Name? error:

=DLookUp("Incentive_TEAM_NAME!REPORT_TITLE","Incentive_TEAM_NAME"," [ " & me.txtName & " ] " & " = 'Incentive_TEAM_NAME![TEAM_NAME]'")

I am not sure where the problem lies...perhaps a misplaced quotation mark? Am I entering this formula in the wrong section of the txtReport properties?

I am very new to Access, any help at all would be much appreciated.

Thanks in advance.

 

[boblarson]

Here you go - you don't need the table name with the field name as you are giving it the table name in the second parameter. And since it is text you need to encapsulate with quotes so I use Chr(34) for that. And you had the parts of team name and me.txt name on the wrong side of the equation.

=DLookUp("REPORT_TITLE","Incentive_TEAM_NAME","[TEAM_NAME]=" & Chr(34) & me.txtName & Chr(34))

 

[malxvc]

Still getting the #Name? error using your solution, Bob.

Scouring the internet looking for a solution, I was seeing instances where it was necessary to use a single quote ( ' ) when performing a DLookUp on a string generated in a Text Box. Is that the case here?

 

[boblarson]

Are you sure that the text box name is really txt.Name. And, do your fields and table names actually have the underscores in them or are they really spaces?

 

[malxvc]

The text box name is txtName - both fields and tables do have underscores, not just spaces.

Removing the "me." that preceeded txtName seems to have solved the issue.

Thanks for your help, Bob.