DLookup not working

[pbaldy]

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?

Both the combo and the textbox are bound to the same field, "email address". You see the name in the combo because the ID column is hidden. You see the ID in the textbox because the ID column is the bound column of the combo. The combo should be bound to the ID field, because that's what it contains. You can use one of the methods in the link in post 7 to either display the email address, or copy it to that textbox.

Hint, the Column Count property of the combo needs to be fixed.

[Lou_Reed]

I have programmed for decades in many languages. You name the language and I have done it, stating with FORTRAN and ending with Python. VBA for MS Access has been ... well challenging.

Thanks for your input.

Respectfully,

Lou Reed

[pbaldy]

There's your problem...you're overqualified!

[Lou_Reed]

Explain the first and second part of this again.

Both the combo and the textbox are bound to the same field, "email address".  You see the name in the combo because the ID column is hidden.  You see the ID in the textbox because the ID column is the bound column of the combo.  The combo should be bound to the ID field, because that's what it contains.  You can use one of the methods in the link in post 7 to either display the email address, or copy it to that textbox.

 Hint, the Column Count property of the combo needs to be fixed.

I just go through the combo procedure and hope for the best. I am even now unsure what to expect when I select the combo box press F4.

I think I need a bit more learning on what exactly am I doing when I go through those steps and make the selections that I do.

I guess that I can go back and edit those after I press Finish.

It is easier to start again and just build a new combo box?

Also, you say that the column count property of the combo box needs to be fixed. How do I fix it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Look at the form in design view; both of those controls display the same field.

You fix the column count property by changing it to the number of columns returned by the row source SQL.

[Lou_Reed]

SELECT tblPersonnel.PersonnelID, tblPersonnel.FullName, tblPersonnel.[email address]
FROM tblPersonnel
ORDER BY tblPersonnel.[PersonnelID];


The SQL for the row source is shown above.


The statement:

"You fix the column count property by changing it to the number of columns returned by the row source SQL."

I am not sure what you mean. I did change the column count last week from the original value of 3. I got nothing. It did not help. I could have done it wrongly.

From what you said I must change the number (literally) from the column count which in my case is 3 to some other number and
the answer to that is in the SQL code. I just do not see the answer.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Okay, I got this one. I was not using the Query that I built for this and instead was using a table. There was my problem.

It works now.

Is there any website that expands further on the combo box wizard because I sure could use some reading up on it?

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[pbaldy]

I can't think of a site explaining the wizard offhand. In the earlier sample, the column count property was 2, so I was hinting that would need to be changed to 3 to access the third column's data.

[Lou_Reed]

Okay, it is working now at least in selecting the email for email textbox after I selected the employee's name in the combo box. However, there is one slight
problem.

When the combo box opens to show me the names to select from, the combo box also shows the person's email address. I guess it is understandable
since I put only two columns in the combo box and email address is the first column. I just think it is more formal, professional and appropriate to not
show the email in the combo box - just the names.

If that can be done, then how d I do it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Given the row source you posted, the column widths property of the combo should be

0;2;0

The zeros hide columns.

[Lou_Reed]

Okay, but why three entries when I only have two columns?

Respectfully,

Lou Reed

[pbaldy]

You said there were 3:

SELECT tblPersonnel.PersonnelID, tblPersonnel.FullName, tblPersonnel.[email address]
FROM tblPersonnel
ORDER BY tblPersonnel.[PersonnelID];


The SQL for the row source is shown above.

[Lou_Reed]

Okay, you are correct. I just saw no need for a Employee_ID column anymore and eliminated it. I went from 3 columns to 2. Is this a wise move? I am just not sure it seems that all
combo boxes have an autonumber ID column. All except mine.

Respectfully,

Lou Reed

[pbaldy]

Well, if you're using it to select an employee and you want to store the ID, then you need it. If you're just using the combo to select a person to send an email, then you don't.

[Lou_Reed]

I thought the ID was used no matter what. I guess it does not have to be.

Thanks for the advice.

Respectfully,

Lou Reed