Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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

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
SteveL13
Asked:
SteveL13
  • 4
  • 4
  • 3
  • +2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
don't see any code...

Jim.
0
 
SteveL13Author Commented:
Sorry...  Forgot to paste it...

=DLookUp("[FullName]","tblPersonnel","[ID] = " & [Forms]![subfrmIssueDS]![txtOpenedBy])
0
 
Eric ShermanAccountant/DeveloperCommented:
If [FullName] is a Text field in your table then this should work ...

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

ET
0
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!

 
SteveL13Author Commented:
FullName is text but ID is a number
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
SteveL13Author Commented:
Still not working.  I'll have to strip sensitive "stuff" out and attach a database.
0
 
SteveL13Author Commented:
Sample attached.  The problem is with the form...  frmIssueDSheader

(Plus the search "thing" isn't working but that's another issue).
Example.accdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
PatHartmanCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
Sorry, I misread your Dlookup statement ... I thought you were looking for FullName.

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
I opened your sample DB ... Your DLookup statement seems to be working.  Can you explain further??

Sample Form
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
etsherman: That was the one I uploaded, after fixing the DLookup issue.
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok .... Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now