DLookUp Not Populating Field - Why ? 1

[StarScream]

DLookUp Not Populating Field - Why ?

I have a form ("Task Info&quot that populates all the fields in one of my tables. However, one of these fields is optional ("Personnel ID&quot. However, if the user selects a person, then I want this bit of code to retrieve that person's email address ("Email&quot from a second table ("Personnel&quot.

Dim AO_Email As Variant

AO_Email = DLookup("[Personnel][Email]", "[Personnel]", "[Personnel][Personnel_ID] = " & Forms!Task_Info!Personnel_ID)

Set objOutlookRecip = .Recipients.Add(AO_Email)

objOutlookRecip.Type = olTo

"Personnel ID" is the field that links to the both tables, but I want it to be optional. If I link my form to these two tables (using an INNER JOIN clause), then the user HAS to select a person (ie, must enter someone in "Personnel ID" field) in order for the form to save any of the data entered as a new record. I just want to make it an optional thing, so that if they do select a person, then the code will populate the variable AO_Email. Does this make sense ?



PJ

 

[Maquis]

Try this in the after_update event of Personnel Id

Dim AO_Email As string

AO_Email = DLookup("Email", "Personnel", "[Personnel_ID] = " & me!Personnel_ID)
me!email = AO_Email



This assumes that Personnel_ID is a number field in your table. Maq B-)
<insert witty signature here>

 

[StarScream]

Maq,

I tried this, but get an error saying it can't find the Personnel_ID field. It says &quot;

me!Personnel_ID =

Can't find...&quot;.

I checked and this is the right field (spelling, format type, etc.). I even replaced the &quot;

me!

&quot; with &quot;

forms!Task_Info!Personnel_ID

&quot; to see if that would help, but nothing.

I think this is on the right track though, if it would work...

PJ

 

[Maquis]

Is this bit of code being run from the task info form? If not, then you definitely need the forms!form!.... notation. Is the task info form open when the code is run? It needs to be.

Other than that, I can only suggest checking your spelling again. The fieldname on the task info form needs to be called Personnel_ID. The &quot;me!Personnel_ID&quot; is referencing the control on the form, not the field name in the table. Maq B-)
<insert witty signature here>

 

[StarScream]

I got it!

I renamed everything so it didn't have spaces in the field name and control names. Then it worked. And it also makes it so its not required to have anything in that field! Thanks a bunch Maq, two stars for you!

PJ