email program fails with invlaide use of null

[blakebaker008]

I was also facing the personal ID security problem, I was not interested in submitting my personal ID there. Now its all clear with your valuable ideas guys, thank you so much for your discussion.

[pbaldy]

Did you advance the code to the next line? In your sample, the variable is getting a value.

[Lou_Reed]

As I said the columns on the combo box query for the db are PersonnelID, Full_Name, and email_address. The strWho should obviously be connected with Full_Name. The strEmailaddress should be connected to emailaddress, I have not created a string emailaddress in the VBA code yet, but I will since I know I need it to connect . That is how I wish to do it. I obviously am not doing that now.

The line of code where the program chokes is: strWho = Me.cboEmployeeName.Column(1). It is reading the first or second column, I have forgotten. If strWho = Me.cboEmployeeName.Column(1) is correct then,
the next line would be: Stremailaddress = Me.cboEmployeeName.Column(2) or the next column over from fullname. This clearly dependent on whether the first line is correct.

This is my approach. I hope that you think it is correct.

Respectfully,

Lou Reed

[pbaldy]

In your sample, these lines of code:

strWho = Me.cboEmployeeName.Column(1)
Debug.Print strWho
Debug.Print Me.cboEmployeeName.Column(2)

produce this result:

Bela Lugosi
Bela_Lugosi@yahoo.com

[Lou_Reed]

No it does not. It causes the code to go to a error region at the bottom of code lines for frmEmail as shown below:

     Err_cmdSend_Email_Click:
     MsgBox Err.Description
     Resume Exit_cmdSend_Email_Click

So obviously it is not getting info from the columns produced by the SQL code in cboEmployeeName.

Respectfully,


Lou Reed

[pbaldy]

Getting the columns most certainly works in the copy you posted. What does not work is the DLookup(), as mentioned because you're comparing the ID to the name.

[Lou_Reed]

How do you know that PersonnelID has a numeric data type? Do you go all the way back to the table where it was defined? It makes sense that PersonnelID has numeric data type, but just how do you know?

I believe I am asking this to determine the connection type from the MS Access code to the VBA code. that seems to be the type of contention. If that is wrong then you get mismatch error.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

In the VBA code for frmEmail there is a line:

strWho = Me.cbo.EmployeName.Column(1)

The form is looking for a numeric value and the VBA code is looking for text value. Hence when the code is run I get a mismatch error.

The next line of interest is :

varTO = DLookup("[email address]". tblPersonnel", StWhere)

which is wrong. It is quite messy. An easier way to get varTO is:

VatTO = Me.cbo.EmployeName.Column(2)

Now the code is trying to get text which is an email address for the strWho selected in the previously discussed line.

That should do it for getting varTO and strWho.

The row source code for the EmployeeName combo box is:

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

The output when I run the SQL code is shown in the attached file.


Repeat :

It assumes that VBA code the first column is 0 , the second column is 1 and the third column is 2.

Also, it assume that is MS Access 2010 the first column is 1, the second column is 2 and the third column is 3.


Is not this the way to correct the error?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Yes, I looked in the table at the data type. I'm not sure what you're asking now. The mismatch error occurs because you're comparing name (text) to ID (numeric). Either compare the ID field to Column(0) or the name field (less accurate) to Column(1). Or dump the DLookup() and use Column(2) for the email.

[Lou_Reed]

Either compare the ID field to Column(0) or the name field (less accurate) to Column(1).  Or dump the DLookup() and use Column(2) for the email.

If you look my post right before this post that is exactly what I did. I am just concerned about the interface between VBA and MS Access. In MS Access I can go back to the table and look at the field and determine if it is numeric or text. In the VBA code however, variable that I am trying to set equal to the value of the MS Access table field, must of course match.

I am just not sure how to accomplish that. I assume that when you define a variable such as strWho as string then that is the correct way to it will match up with Me

Fullname column is clearly text, so StrWho must match that column or as you say column(1) must be string. The email address is clearly text so to set varTO equal to what is in column(2) must be text. The PersonnelID column is just there, it is of no use to me except in creating the combo box. It is clearly a numeric field so Me.cbo.EmployeName.Column is clearly numeric or a number field.

The VBA code has a variable definition section, but I am not sure if that is where to set the values, strWho and varTO to string. I assume it is since there is no other place for it variable definition.

If I am correct here please tell me.

Respectfully,

Lou Reed

[Lou_Reed]

The db works now with the corrections that you suggested. I am just confused again by the use of VBA variables. Some are strings and some are variants, there are more variable defsbut that seems enough to get the db to work. Just what is the difference between a string and a variant VBA variable type. If a numeric field in a table is set to a VBA variable, what type variable is it set to? If a text value field is set to a VBA variable what type of variable is it set to?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed