Where have I gone wrong with this DLOOKUP?

[Big Pat]

I have a control named PI on my subform which looks up values from a table called "PI Names" (principal investigators of clinical studies, not private eyes!!) This works OK and allows me to select an entry.

Now I want to have the PIs email address, which is stored in the same table, displayed on the form. This is what I have come up with.

=DLookUp("[PI Email Address]","PI Names","[Name]=[Forms]![frm01Study]![frm02 Site]![PI]")

But when I open the form, it just flashes #Error really quickly.

I know I shouldn't really have spaces in the field names, but rather than normal tables, my database links to Sharepoint lists and I can't change the list settings as several other things would be affected. Is it somthing to do with quotes and/or brackets, I wonder?

Thanks,

 

[Big Pat]

I have a control named PI on my subform which looks up values from a table called "PI Names" (principal investigators of clinical studies, not private eyes!!) This works OK and allows me to select an entry.

Now I want to have the PIs email address, which is stored in the same table, displayed on the form. This is what I have come up with.

=DLookUp("[PI Email Address]","PI Names","[Name]=[Forms]![frm01Study]![frm02 Site]![PI]")

But when I open the form, it just flashes #Error really quickly.

I know I shouldn't really have spaces in the field names, but rather than normal tables, my database links to Sharepoint lists and I can't change the list settings as several other things would be affected. Is it somthing to do with quotes and/or brackets, I wonder?

Thanks,

Sorry...I'm an idiot. I didn't need to refer to the form at all.

=DLookUp("[PI Email Address]","PI Names","[Name]=[PI]") works.

I engaged keyboard before brain again

 

[Simon_MT]

Here's a tip:

=DLookUp("[Employee Name]","[Employees]","[Employee]= '" & [Forms]![Clients Sales Enquiry]![Client Sale Employee] & "'")

If you change Clients Sales Employee (string value) then so does this label.

Simon