Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

invalid use of null

Posted on 2007-07-26
24
Medium Priority
?
371 Views
Last Modified: 2007-08-12
I am unsure of why this only works HALF the time..

I get the error "invalid use of null" even though there is an e-mail address in the field.
Of course if this happens, it does not add the address to the table, nor is the data cleared afterwards.

It is not consistant whatsoever.. sometimes it works, sometimes it doesn't.
Any suggestions?


Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim TemplateLocation As String


TemplateLocation = DLookup("[TemplateLocation]", "CampaignInfo", "[CampaignName]= Forms!CampaignMenu.CampaignList")
   

   
   Dim checksent As Variant
   Dim myOlApp As Outlook.Application
   Dim MyItem As Outlook.MailItem
   Set myOlApp = CreateObject("Outlook.Application")
   Set MyItem = myOlApp.CreateItemFromTemplate(TemplateLocation)

    MyItem.SentOnBehalfOfName = "OTeam"
    MyItem.To = Me.Text78
    MyItem.Display
    checksent = "true"
    Me.Parent.Controls("Check75").Value = checksent

' This is where access decides if it has to add the current email address to Emails table.
' First case, it already exists in the table, so do nothing

If DCount("*", "Emails", "CustomerID= '" & CustomerID.Value & "' And Email = '" & Text78.Value & "'") > 0 Then
    Me.Text78 = Me.Text78
Else

' Second case, email doesn't exist in the table, so add it.
' Inserting e-mail into e-mail table

    Dim conDatabase2 As New ADODB.Connection
    Dim strSQL2 As String
    Dim CustomerID2 As String
    Dim email2 As String

    CustomerID22 = Me.CustomerID
    email2 = Me.Text78
    Set conDatabase2 = CurrentProject.Connection
    strSQL2 = "INSERT INTO [Emails] (CustomerID, Email) values ('" & CustomerID2 & "', '" & email2 & "')"
    conDatabase2.Execute strSQL2

    conDatabase2.Close

End If


Me.Text78 = ""

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

End Sub
0
Comment
Question by:chicka616
  • 8
  • 6
  • 6
  • +1
22 Comments
 
LVL 3

Expert Comment

by:kaosyeti
ID: 19578564
your dlookup looks a little off in terms of syntax.  try changing it to this:

TemplateLocation = DLookup("[TemplateLocation]", "CampaignInfo", "[CampaignName] = " & chr(34) & Forms!CampaignMenu.CampaignList & chr(34))

this assums that it's text, of course.
0
 
LVL 75
ID: 19578621
If any variable is Dimmed as a string (you have some) and you try to set it to Null (ie, something is null) ... Houston, we have a problem.

Check that ....

mx
0
 
LVL 75
ID: 19578628
In particular:

    Dim strSQL2 As String
    Dim CustomerID2 As String
    Dim email2 As String

Try dimming as Variants ... and see if the problem goes away ?

mx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 19579055
DLookup will return a null if no matches are found.  It's a good idea to check for nulls with this function:

TemplateLocation = NZ(DLookup("[TemplateLocation]", "CampaignInfo", "[CampaignName]= Forms!CampaignMenu.CampaignList"),"No Match")

If the lookup finds no matches, NZ will return a value of your choice, in this case "No Match", which you can handle as wou wish in your code without it causing problems like NULL would.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19579183
So if you wanted to exit out of the function when DLookup finds no matchs, you could do this (in this example, I'm replacing Nulls from DLookup with an empty string):

TemplateLocation = NZ(DLookup("[TemplateLocation]", "CampaignInfo", "[CampaignName]= Forms!CampaignMenu.CampaignList"),"")
If TemplateLocation = "" Then
    msgbox "No matches were found.  Problem with TemplateLocation"
    Exit Sub  '
End IF
''' The rest of your code follows this....

>your dlookup looks a little off in terms of syntax.
Seperating the form reference out with chr(34) may help if CampainList has quotation marks in the data.  However, the syntax originally posted in general is correct.  You can embed form references in the criteria of domain aggregate functions just like you would in a stored query.

0
 
LVL 1

Author Comment

by:chicka616
ID: 19582238
Oops... I should have omitted the template location part... that has nothing to do with the problem... that is working perfectly.
 
I am trying DatabaseMX's solution...  the change to Variant.. I'll let you know how it works out.
Thanks.


0
 
LVL 1

Author Comment

by:chicka616
ID: 19582266
(Sorry... I know it is working fine because it still pulls up the template and passes all the necessary info to the email)
The issue is passing the email address to the Emails table.
0
 
LVL 3

Expert Comment

by:kaosyeti
ID: 19584531
chicka616:

<<I know it is working fine because it still pulls up the template and passes all the necessary info to the email) The issue is passing the email address to the Emails table>>

are you 100% certain about that?  if templatelocation's dlookup results in a null value, then Set MyItem = myOlApp.CreateItemFromTemplate(TemplateLocation) would result in an error.  this can very well be an intermittent issue.  when the error occurs and you opt to debug the vba, if you debug.print templatelocation in the immediate window do you get a result?  if so, is the result exactly what you wanted?

mbizup:

<<Seperating the form reference out with chr(34) may help if CampainList has quotation marks in the data.  However, the syntax originally posted in general is correct. >>

really?  i would have thought that
DLookup("[TemplateLocation]", "CampaignInfo", "[CampaignName]= Forms!CampaignMenu.CampaignList")
would result in an error based on the fact that i was under the impression that in the criteria section, you can't have anything but a number value after the equals sign without concatenating it as a part of the expression.  i may have jumped to the conclusion that it was a string value (i have only been an 'expert' here for about a month and i'm self-taught on access -- so i tend to filter things through my database and it's past issues when i should be more open-minded).  given that, i would have said that

DLookup("[TemplateLocation]", "CampaignInfo", "[CampaignName]= " & Forms!CampaignMenu.CampaignList")

would be the correct syntax.  thanks for the aside to educate me in someone else's post.

0
 
LVL 61

Expert Comment

by:mbizup
ID: 19584901
>really?  i would have thought that...

Take the time to try it!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19585022
btw... that was a new one for me, too.  I knew that you could reference forms in stored queries, and *thought* you could use the same syntax in domain aggregate functions as well, so I went ahead and gave it a shot.  I've picked up on a lot of new "tricks" by testing things posted here that I am uncertain of myself.

>i have only been an 'expert' here for about a month and i'm self-taught on access
Welcome aboard!  This is a *great* way to learn :-)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19585056
chicka616,

With the issues EE had yesterday, one of my posts seems to have gotten lost.  If you are still having the invalid use of null error, you should temporarily remove the On Error statement (comment it out).  When Access's default error box pops up, click Debug.  That will highlight the exact line that is causing ptoblems.  Then hover the mouse over the controls and variables in that line to see their values.  You should be able to track down what is NULL like this.

kaosyeti is making an excellent point about NULL leading to potential problems elsewhere in the code.  It is always a good idea to handle expressions that can produce Nulls properly (even if it is not causing the issue at hand).
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 19585329
"With the issues EE had yesterday"

Pretty serious!  wow.  My guess ... someone hacked and compromised the system?  Have you heard anything Mariam?

chicka616 - did you try this ... per my original post ?
    Dim strSQL2 As String
    Dim CustomerID2 As String
    Dim email2 As String

Try dimming as Variants ... and see if the problem goes away ?


mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19588151
I haven't heard anything.  It's also possible that the site engineers were enjoying a day off :-)
0
 
LVL 1

Author Comment

by:chicka616
ID: 19592642
DatabaseMX,

Your solution lead to the ultimate solution of the problem. It had to do with the form not passing the customer id to the subform correctly... so I just ended up pulling it from the initial form...

Instead of CustomerID2 = Me.CustomerID
I did CustomerID2 = Forms!Form1.CustomerID
Works like a charm...

By changing it to Variant I could see that it wasn't getting the right info... so that really helped me.
So under this circumstance would I somehow award you half the points, because your solution helped me find my solution? or all the points?
Doesn't really matter to me.. I just don't really know how it works.

Thanks!

T
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19592709
This really looks self-solved.  Please take a look at EE guidelines for handling questions you answer yourself:

http://www.experts-exchange.com/help.jsp#hs5
0
 
LVL 75
ID: 19593519
"because your solution helped me find my solution? or all the points?"

works for me

mx
0
 
LVL 1

Author Comment

by:chicka616
ID: 19607875
Trying to see if I can get you half :)
0
 
LVL 75
ID: 19632655
Again:

"because your solution helped me find my solution?"

works for me

chicka616: ??

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19634265
The problem with allowing a null to pass through code by assigning it to a variant is that in general the underlying issue is not solved, just relocated (as kaosyeti said).

 I don't doubt that using a variant helped the author track down the culprit code and am not concerned with the point allocation.  However in terms of accuracy and quality, the author's own answer (a fixed form reference and correcting "CustomerID22" with "CustomerID2") is the solution.
0
 
LVL 1

Author Comment

by:chicka616
ID: 19635208
Actually it wasn't the reference& I dont know how that happened... It wasn't a direct paste... so I guess I accidentally added it. Id have other issues with that one ;)

I had 2 subforms (one within the other)... Parent had the CustomerID which was passed to Subform 1 when something was added to it (new record) then passed to subform 2 when something was added to it (new record). (Messy& I know)

For some reason even though the 1st subform already had something selected, it wasn't passing the customer number to the 2nd subform...( Well.. half the time it worked.)

These are details I didn't even include in the problem as I didn't believe that was the issue...

By changing it to variant (DatabaseMX's solution), I realized this was the case... I saw a null value rather than the CustomerID in the Emails table. It was a HUGE help (mbizup: you are right... this definitely isn't the solution as passing the customerID was the whole reason why I posted, not to pass a null value. However, I wouldnt have known what was going on if I hadnt changed it to variant).

In the end, I just passed the customerID from the parent form to the 2nd subform and it worked like a charm. (CustomerID2 = Forms!Form1.CustomerID)

If I could I would accept multiple solutions, DatabaseMX as well as my own. I am grateful for the suggestion and I would also award all points, I am just trying to ensure it is done correctly in case someone else comes along with the same issues as me.

Please offer feedback& and if it is okay I will award DatabaseMX the full points.
0
 
LVL 1

Author Comment

by:chicka616
ID: 19635221
I guess I could have moved subform2 out of subform1 and put it in the parent form as well... I am positive that would have worked now that I am visualizing it... it would have been less messy too... haha. Oh well! I am learning along the way.

Either solution would have worked.
0
 
LVL 75
ID: 19635255
Hey ... it's no big deal :-)

mx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question