DLookup not working

[Lou_Reed]

The db file that I have attached to this post has a form called frmemail. In it I am trying to match an email with a person's name. I am using the DLookup function as the control source for that box.

It is not working and I am not sure why. I put the DLookup as the control source, but when I run it - no luck.

What am I doing wrong?

Any help appreciated. thanks in advance.



Respectfully,

Lou Reed

[ranman256]

put the names and emails in a query.
the query will pull: name, email
put this query in a list box w 2 columns,(lstEmail), then bind the listbox to the email.
select item in list, THIS is the email that will be sent. No Dlookups.

docmd.SendObject acSendReport ,"rptName",acFormatPDF,lstEmail,,,sSubj,sMsg,fa lse

[Lou_Reed]

What you say would probably work. But what is wrong with what I already have done here. It seems that DLookup should work.

I will try what you said, but what is wrong with what I already have. I just want to know.

Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I am not understanding the following sentence in your post.

put this query in a list box w 2 columns,(lstEmail), then bind the listbox to the email.

Especially, the last part "then bind the listbox to the email".

Please explain. I know it may be obvious to someone who has worked a lot with this. It is just not obvious to me.

Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

ThE listbox already has the data. There no need to do more lookups.
(sorry if my wording was unclear)

[Lou_Reed]

1. I guess that I am not understanding the last two instructions or steps in your explanation. The last part of this is what I do not understand.


"put this query in a list box w 2 columns,(lstEmail), then bind the listbox to the email."

I did create a listbox with the listbox wizard. I did put the email address first. Then bind the listbox to the email? I am not sure how to do that.

Also

2. I do not work with listboxes very often if at all. There are two columns in the listbox with the first the email address and the second the employee's name associated
with the listbox.

The db user should select the employee's name from the listbox and the email address for that employee is shown, then the email is used when to message is sent in the to column.


The user select employee's name and the email is selected from that. The email and the employee are in the same table.

Since this table is the record source for the form, why not just use email direct as a control source for the no listbox or DLookup?

Once you have the table as the record source, it seems that you can choose any field off of it as a control source for the textbox. I chose email.

Again eliminating listboxes and DLookups or anything else. As I said the employee name and the email are in the same table!

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Your DLookup fails because you need to refer to the combo, since the field isn't on the form. You also spelled the key field wrong. It works with:

=DLookUp("[email address]","tblPersonnel","PersonnelID=" & [Combo42])

I'd just use your existing combo and add the email field to it:

BaldyWeb - Autofill

[pbaldy]

Oh, and I had to add brackets due to the inadvisable space in the field name.

[Lou_Reed]

What is Combo42? I assume it is the combo box with the email address and full name. Thus is should be a two column combo email first then full name.

As far as the email address. I am unsure as to when I can use a space or not. A space adds readability and leaving one out eliminates the above error.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Combo42 is the name of the combo for Employee Name on frmEmail. It's a 2 column combo with PersonnelID and FullName.

You "can" use one any time. You "shouldn't" use one ever in an object name. I use camel case: EmailAddress. Others use underscores: Email_address. Again, you "can" use them, but it will cause you headaches like this.

[Lou_Reed]

One other question, what if name and email address are on the same table, then you should not need to use DLookup to find the email address once you have the full name.

It seems we are looking for an association between name and email address. If name and email address are on the same table then that association already exists.

Thus no DLookup is required.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

No, you don't need DLookup(). I'd just add it to that combo, ranman mentioned a listbox, same solution. Then you simply get it like I showed in the link, using the Column property of the combo/listbox.

[Lou_Reed]

So you only use DLookup when you are getting the email address that is not on the same table as the name?

Is this correct?

Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Okay, in this example which I have attached. I can select the names on the frmpersonnel and it shows the names in frmemail. However,
the email section shown right below names in forms email shows the employee ID not the employee email address.

What is going on here?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Well, not to sound trite, but you use DLookup() when it's the appropriate tool for the job. I wouldn't say it's a hard and fast rule that you'd only use it when it's in a different table. If for whatever reason I only had the user name and needed the email, I might use a DLookup(). It wouldn't really matter what the table structure was at that point, just "okay, here's what I have, here's what I need, what's the best way to get it?". I might just be able to add the field to a form. I might use a recordset because I need more than one field.

I can tell over time that you've been working really hard to learn Access, and I applaud that. I think it's less a matter of learning a rigid set of rules as it is learning what the available tools are, what their advantages and disadvantages are, so you can then apply that knowledge to make decisions in individual situations, because each situation is unique (how's that for a run-on sentence!). I've been at it a long time, and I still learn new ways of doing things.