email program fails with invlaide use of null

[Lou_Reed]

The attached file contains a db, when I go to form frmEmail select an employee and try to send an email to the Employee that is selected from the combo box at the top of the form it gives an "Invalid use of null" error. It fails to set the value of Strwho in the VBA code, I am not sure why. I am asking it to read the name from the first column. It has worked before, but now if fails with an "invalid use of null".

The who is set equal to combobox column 1 and the email address is set to combo column 2. It worked before but it fails now with an error, invalid use of null. How can I repair this error? It also worked n the form just using the combo box set to their respective columns.

Any help appreciated. Thanks in advance.



Respectfully,

Lou Reed

[pbaldy]

No file attached.

[Lou_Reed]

The file that should have been attached in the first post is now attached in the second post. Sorry about that.

Respectfully,

Lou Reed

[pbaldy]

I'm getting a data type mismatch error. It comes from the DLookup(); PersonnelID has a numeric data type so you wouldn't want the apostrophes. You're also grabbing the name instead of the ID from the combo, so you're comparing apples to oranges.

[Lou_Reed]

Okay, thanks for you help. I will look into it. This was working just fine until it failed today. I tried to use frmEmail after not using it for awhile, about 1 month. I am surprised that it stopped working.

I did not think that anything that I did in the interim would have affected it. It must have however.

Thanks for you help an I will give it a try.


Respectfully,

Lou Reed

[pbaldy]

No problem, post back if you're still stuck.

[Lou_Reed]

I have to ask again. I am sorry to be redundant. In MS access 2010 combo boxes is the first column 0 or 1. Do we initiate
columns 0, 1, 2, 3, 4 ... or 1, 2, 3, 4, 5? It is important in my debugging this program's combo box.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[jwhite]

In VBA code it is zero-based - the first column would be .Column(0)

[Lou_Reed]

Does that apply to this combo box? I know that in MS Access 2010 some column numbering starts at 0 and some starts at 1. It just is confusing to me.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

It starts at zero, which is why you're getting name instead of ID.

[jwhite]

VBA - 0 based
Combo Control properties - 1 based

There is no sometimes about. It is consistent.

[Lou_Reed]

I now have the bound column as 2 That is the employee's email address. That clearly is unique. Two employees may have the same name, but they will not have the same email address.

I am not even sure now why I put in PersonnelID at all. It seems redundant; PersonnelID is unique, but so is email address.

I still have Personnel ID in there. Why I do not know.

So how to fix this?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Long ago I suggested including the email address in the combo row source and simply getting it from there. Like:

varTO = Me.cboEmployeeName.Column(x)

where x is the appropriate column.

[Lou_Reed]

Here is my current row source:

SELECT [tblPersonnel].[PersonnelID], [FirstName] & " " & [LastName] AS FullName, emailaddress FROM tblPersonnel ORDER BY [FirstName] & " " & [LastName];

This was suggested to me by another helpful colleague on the forum.

I would like to keep it there as it is now. I do not wish to change it if it is at all avoidable.

Is there another way to fix this error?

By the way, I have four columns in this combo box and the bound column is 2. I assume they are enumerated as 0, 1, 2, 3. The first column, column 0 is PersonalID, the rest are last name first name and email address. The last name and first name are concatenated creating a full name, but the third column is column 2 and is email address. I have four columns and I enumerated them down as above, but
I am just not seeing how they pan out. It seems along the way two of the columns are collapsed into one.

It has been a few weeks since I created this combo box and I am not remembering what I did. There is no way that I can dissect this combo other than recreating it and that seems unnecessary. I thought that the last column in the combo box was the email address column and that is column three not two. That is the reason for this post.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

Respectfully,

Lou Reed

[CJ_London]

It seems along the way two of the columns are collapsed into one.

you are presumably referring to what you have done here

[FirstName] & " " & [LastName] AS FullName

which is one column