Results 1 to 7 of 7

DLOOKUP in form to show related field - need to display lookup value not ID--

  1. #1
    sixfoursgirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    4

    DLOOKUP in form to show related field - need to display lookup value not ID--

    Hi,

    I'm just learning Access, so sorry if this is a beginner question...



    I successfully added a DLookUp to my form to show the TeamID from a related table. But this field is a number that displays the teamname in the Consultants table (via a lookup). I want the text to display on my form, not the TeamID. Somehow I'm guessing I have to join in another table (there is a Team table that defines Team/TeamID).

    Hope this makes sense! And thank you in advance!

    =DLookUp("[TeamID]","Consultants","[ConsultantID]=" & [Forms]![Adjustments]![ConsultantID])

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    You can create a query with both tables involved that will give you the team TeamIDs, TeamNames,and ConsultantIDs.Then, instead of basing your DLookup on the Consultants table, you base it off this query.

  3. #3
    sixfoursgirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    4
    Thanks... I will have to investigate this as I'm not quite sure how to start with it (I've created queries/reports but haven't combined forms/queries or really anything with queries yet!). Hopefully my book will cover this later on, I'm on page 421... =)

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Open a blank query in SQL view. Type in:

    Code:
    SELECT Team.TeamID, Team.TeamName, Consultants.ConsultantID
    FROM Team INNER JOIN Consultants ON Team.TeamID = Consultants.TeamID
    Save this query as qryVwConsultantTeams (Note: I have a habit of starting all my query names with "qry", following them with the type, "Vw" (meaning View) for SELECT queries, followed by a description. You'll notice that many follow this form)

    Go back to your DLookup and replace:
    =DLookUp("[TeamID]","Consultants","[ConsultantID]=" & [Forms]![Adjustments]![ConsultantID])
    With:
    =DLookUp("[TeamName]","qryVwConsultantTeams","[ConsultantID]=" & [Forms]![Adjustments]![ConsultantID])

  5. #5
    sixfoursgirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    4
    THANK YOU!!!!!!! this worked and it really helped me understand!

    I see that you cannot filter by form using this new field - is there another way to do this?

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    What do you mean by "filter by form"?

  7. #7
    sixfoursgirl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    4
    On sort & filter in the ribbon, if you click on Advanced... filter by form, you can then filter with as many criteria (and/or) as you like. Perhaps there are easier/better ways of doing this is - designing for the purpose of others using the form (which is my ultimate goal) but for now this is all I know!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Lookup to show related values
    By mjhopler in forum Access
    Replies: 1
    Last Post: 08-19-2011, 01:35 PM
  2. Replies: 2
    Last Post: 07-16-2011, 07:56 PM
  3. Show related data on a form
    By Accessgrasshopper in forum Forms
    Replies: 4
    Last Post: 03-17-2011, 07:53 PM
  4. Replies: 4
    Last Post: 01-03-2011, 10:54 PM
  5. Lookup or list field: how Do I not show all records
    By scott munkirs in forum Forms
    Replies: 4
    Last Post: 04-13-2006, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums