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

Posted on 2007-10-13
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
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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

    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

    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 92

    Accepted Solution

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

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now