"Access 2007" Displaying the result of a query in a text box

Posted on 2007-10-13
Medium Priority
Last Modified: 2013-11-28
I have a form (formCalls) based on a table (tblCalls), which has a field called AgentID.  I want to display in a text box, (txtAgentName), the agent's FirstName and LastName concatenated together. tblPeople has this name information and AgentID is the index into tblPeople.

I have created a query (query_GetNameOfAPerson):
SELECT [FirstName] & " " & [LastName] AS FullName, tblPeople.ID, tblPeople.FirstName, tblPeople.LastName
FROM tblPeople
WHERE (((tblPeople.ID) Like [Forms]![formCalls]![AgentID]));

The txtAgentName's control source is =[query_GetNameOfAPerson]![FullName]

When the form executes all I get is #Name? in txtAgentName.  Why?

Thanks for you help
Question by:SheahaST
  • 2
  • 2
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20070908
Hello SheahaST,

>>When the form executes all I get is #Name? in txtAgentName.  Why?

Because you are not allowed to reference the value that way.

Instead, I would set the controlsource to something like:

=DLookup("[FirstName]", "[tblPeople]", "[ID] = " & [AgentID]) & " " & DLookup("[LastName]", "[tblPeople]", "[ID] = " & [AgentID])

If that ID field is text:

=DLookup("[FirstName]", "[tblPeople]", "[ID] = '" & [AgentID] & "'") & " " & DLookup("[LastName]", "[tblPeople]", "[ID] = '" & [AgentID] & "'")



Author Comment

ID: 20071088
Thanks a lot Patrick.  That works great on existing records, however when it is a new record I get #Error in the field since [AgentID] is Null.  I can live with that but is there away around it?

Thanks again, Steve

Author Comment

ID: 20071112
Another question.  What part of my first solution is faulty?  I used the Expression Builder to create the control source string (=[query_GetNameOfAPerson]![FullName]
).  Or is the problem in the SQL statement referencing Forms]![formCalls]![AgentID]?

Just trying to get a better understanding.

Thanks again
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 20071506
=IIf(IsNull([AgentID], "", DLookup("[FirstName]", "[tblPeople]", "[ID] = " & [AgentID]) & " " & DLookup("[LastName]", "[tblPeople]", "[ID] = " & [AgentID]))

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question