Solved

Trying to populate a text field on a form with a DLookup

Posted on 2014-10-23
13
231 Views
Last Modified: 2014-10-29
I'm using this code in the control source property of a textbox on a form and all I get is #Name?

The form is a datasheet view form if that matters.

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 57
ID: 40400412
don't see any code...

Jim.
0
 

Author Comment

by:SteveL13
ID: 40400416
Sorry...  Forgot to paste it...

=DLookUp("[FullName]","tblPersonnel","[ID] = " & [Forms]![subfrmIssueDS]![txtOpenedBy])
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40400440
If [FullName] is a Text field in your table then this should work ...

=DLookUp("[FullName]","tblPersonnel","[ID] = '" & [Forms]![subfrmIssueDS]![txtOpenedBy] & "'")

ET
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:SteveL13
ID: 40400445
FullName is text but ID is a number
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40400517
Often that means something is misspelled. Be sure your field, table, form and control names are right.

Also, I'm not sure you need square brackets around the names:

=DLookUp("FullName","tblPersonnel","ID =" & [Forms]![subfrmIssueDS]![txtOpenedBy])

You might also need to trap for NULL values:

=Nz(DLookUp("FullName","tblPersonnel","ID =" & [Forms]![subfrmIssueDS]![txtOpenedBy]), "No Value")
0
 

Author Comment

by:SteveL13
ID: 40400525
Still not working.  I'll have to strip sensitive "stuff" out and attach a database.
0
 

Author Comment

by:SteveL13
ID: 40400535
Sample attached.  The problem is with the form...  frmIssueDSheader

(Plus the search "thing" isn't working but that's another issue).
Example.accdb
0
 
LVL 84
ID: 40400581
This works in the OpenedBy box in your subform:

=Nz(DLookUp("FullName","tblPersonnel","ID =" & [txtOpenedByN]),"No Value")

This works in the txtAssignedTo box in your subform:

=Nz(DLookUp("FullName","tblPersonnel","ID= " & [cboAssignedTo]),"No Value")

That said - you could just join tblPersonnel to tblIssues (twice), and use the values from there, instead of using a DLookup. I made those changes in the file and uploaded.

I'd also caution you against setting up combos and such on columns at the table level. There is absolutely no good reason to do so, and lots of very good reasons to NOT do so.
Example--1-.accdb
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40400591
I can't download the .accdb due to an IE error but #Nameis often caused because the control name is the name of a column in the form's RecordSource but the controlSource contains an expression (anything that starts with =) rather than the name of the bound column.

When you let a wizard build a form/report or when you drag a field on to a form/report from the field list, Access Assigns the ControlName to be the same as the ControlSource.  So if the column in the table/query is DeptName, then the ControlSource ends up being DeptName and so does the ControlName.  If you then change ControlSource to something like =[DeptNum] & " - " & [DeptName], you will get the #Name error.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40400681
Sorry, I misread your Dlookup statement ... I thought you were looking for FullName.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40400703
I opened your sample DB ... Your DLookup statement seems to be working.  Can you explain further??

Sample Form
0
 
LVL 84
ID: 40401879
etsherman: That was the one I uploaded, after fixing the DLookup issue.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40401917
Ok .... Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

751 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