Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 4th, 2009, 12:35 PM
Registered User
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default DLookup Help

I have a textbox (txtSalesNotActive) on a form (frmProfile_person_history_subform) that produces a person key. I then have another textbox (txtSales) that I want to display the person's name from a table (person) based on the person key in (txtSalesNotActive)

I currently have this as the DLookup Function in txtSales:

=DLookUp("[last_name]","[person]","[person_key] = '" & [Forms]![frmProfile_person_history_subform]![txtSalesNotActive] & "'")

Person Key is listed in the person table as the primary key. When the form loads, it just displays #Error in txtSales.
Old February 5th, 2009, 02:47 AM
Authorized User
Join Date: Jul 2008
Posts: 38
Thanks: 1
Thanked 2 Times in 2 Posts

It sounds as if you are actually looking for information on a subform. You would need to reference it properly to get it to work.

First, you need to realize that you reference the subform CONTAINER (the control on the main form that houses the subform on the main form) and NOT the subform name itself (unless it is exactly the same as the subform container name, which it can be but isn't necessarily).

Second, you need to use the word FORM to tell Access you are referencing something on the subform and not the subform control.

So IF the code is on the main form you would refer to it like:


or if it is on a completely other form


Bob Larson
Access MVP (2008-2010, 2011)
Free Access tutorials, samples, tools
Old February 5th, 2009, 03:41 PM
Registered User
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

Bob, Thank you for your reply. The txtSalesNotActive value that I need to lookup the name from is not on a subform, that is just how the naming is. frmProfile_person_history_subform is a pop-up from a link on frmProfile
Old February 20th, 2009, 08:55 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

If txtSalesNotActive and txtSales are both on the same subform, then

Me.txtSales = DLookUp("[last_name]", "[person]", "[person_key] = '" & Me.txtSalesNotActive & "'")

If txtSalesNotActive is on the subform as you say, but txtSales is on the main form (or parent), then

Me.txtSales = DLookUp("[last_name]", "[person]", "[person_key] = '" & Me.frmProfile_person_history_subform.Form.txtSales NotActive & "'")
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Similar Threads
Thread Thread Starter Forum Replies Last Post
dlookup problem alemok Access 0 April 17th, 2008 02:50 AM
Problem with Dlookup JAdkins Access VBA 1 January 11th, 2008 06:52 PM
Dlookup problems jik VB How-To 0 February 15th, 2007 04:27 PM
Help with DLookup Devilboy13 Access VBA 4 August 30th, 2006 02:48 PM
DLookup Teqlump Access VBA 1 August 9th, 2006 11:48 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.