Invalid Use of Null

[chuckcoleman]

Hi, this has been driving me nuts for a day now.

I have a form called Revenue Input Form. When a payment is received, the Lot Number is entered along with two other items and the amount of the payment. If the customer has an email address, there is a button on the form that displays "Email" and when you click on it, everything is fine. The email is created acknowledging their payment and it's sent out. If the customer doesn't have an email address, that same button displays "Print" and a paper receipt is created and printed out. The issue is when the customer doesn't have an email address, the first thing I get is "Invalid Use of Null". After acknowledging the error by clicking on "OK", the program continues, and the receipt is printed. How do I prevent the "Invalid Use of Null" message, and more importantly, I don't know what's causing it so I can prevent it.

Any ideas?

Private Sub EmailPaymentX_Click()
On Error GoTo EmailPaymentX_Error

Dim PrintToPDF
Dim DestPath As String '
Dim ShowPDF As Boolean '
Dim strHyperlinkFile As String
Dim strSelectedFile As String
Dim strSelectedFileSubDirectory As String
Dim oApp As Object
Dim oEmail As Object
Dim objOutlook As Object
Dim fileName As String
'Dim CustEmail As String
Dim CustEmail As Variant
Dim HasEmail As Variant
Dim Desc As String
Dim BindX As TempVars
Dim IsPrimary As Boolean
Dim Amt As Currency
Dim ChkNbr As String
Dim CheckIsBlank As String
Dim LotNumberX As String
Dim FNameX As String
Dim LNameX As String
Dim Greeting As String
Dim DToday As String
Dim TotPaid As Currency
Dim OwnerLU As String
Dim FNameLU As String
Dim LNameLU As String
Dim FNameLU2 As String
Dim LNameLU2 As String
Dim LotNbrLU As String

TempVars.RemoveAll
TempVars!BindX = Forms![Revenue Input Form]![BindNbrX].Value

DoCmd.RunCommand acCmdSaveRecord
If Me.ButtonLabelXY = "Print" Then
 GoTo PrintInvoice
Else
 If Me.EmailX <> "" Then
  CustEmail = Me.EmailX
 Else
  CustEmail = 0
 End If

DToday = Format(Date, "mmmm dd, yyyy")
LotNbrLU = Me.LotNbrX
If Not IsNull(DLookup("[Owner]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")) Then
 OwnerLU = DLookup("[Owner]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")
End If
If Not IsNull(DLookup("[FName]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")) Then
 FNameLU = DLookup("[FName]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")
End If
If Not IsNull(DLookup("[LName]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")) Then
 LNameLU = DLookup("[LName]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")
End If

Set oApp = CreateObject("Outlook.Application")
Set oEmail = oApp.CreateItem(0)
DestPath = "C:\HOA\Receipts\"

DoCmd.SetWarnings False

 If IsNull(Me.DateInputX) Then
  MsgBox ("You must have a date in the 'Input Date' column. Please enter the date this entered into the system.")
  Exit Sub
 Else
 If IsNull(Me.AmountX) Then
  DoCmd.Beep
  MsgBox ("You haven't entered an amount for this owner. Please enter an amount in the Amount field.")
  Exit Sub
 Else
 End If
 End If

 HasEmail = CustEmail
 
  DestPath = "C:\HOA\Receipts\"
  strSelectedFile = "C:\HOA\Receipts\" & TempVars!BindX & "-" & [Owner]
  strSelectedFileSubDirectory = "\Receipts\" & TempVars!BindX & "-" & [Owner] & ".pdf"
  strHyperlinkFile = "C:\HOA\Receipts\" & "#" & strSelectedFile
  ShowPDF = False
  fileName = Application.CurrentProject.Path & strSelectedFileSubDirectory
  DoCmd.SetWarnings True

  If Not IsNull(Me.CheckNbrX) Then
    CheckIsBlank = " on check number " & Me.CheckNbrX & ". "
   Else
    CheckIsBlank = ". "
   End If
  
   LotNumberX = Me.LotNbrX
   If Len(Nz(FNameLU)) = 0 Then
    FNameLU2 = ""
   Else
    FNameLU2 = FNameLU & " "
   End If
   If Len(Nz(LNameLU)) = 9 Then
    LNameLU2 = ""
   Else
    LNameLU2 = LNameLU
   End If
   If Len(FNameLU2) = 0 Then
    Greeting = LNameLU2
   Else
    Greeting = FNameLU2
   End If

If Me.SentX = True Then
 Me.SentX = False
 DoCmd.RunCommand acCmdSaveRecord
End If
 TotPaid = DLookup("[TotalPaid]", "Total paid for dues ack", "Lot =  LotNbrX")
  
 With oEmail
 .Recipients.Add HasEmail
 .Subject = DLookup("SubName", "Admin Table", "LimitNbr=1") & " Payment Acknowledgement"
 .Body = vbCrLf & DToday & vbCrLf & vbCrLf & vbCrLf & OwnerLU & vbCrLf & DLookup("[Mailing Address]", "[Master Table]", "[Lot] = LotNbrX") & vbCrLf & DLookup _
  ("City", "[Master Table]", "[Lot] = LotNbrX") & ", " & DLookup("State", "[Master Table]", "[Lot] = LotNbrX") & " " _
  & DLookup("ZIP", "[Master Table]", "[Lot] = LotNbrX") & vbCrLf & vbCrLf _
  & vbCrLf & vbCrLf & "Hi " & Greeting & "," & vbCrLf & vbCrLf & "This email is to let you know we received your payment of " _
  & Format([TotPaid], "Currency") & CheckIsBlank & " Your payment was posted to your account for your property" _
  & " at " _
  & DLookup("[lot Address]", "[Master Table]", "[Lot] = LotNbrX") & " and was for the year " _
  & DatePart("yyyy", Me.AssYr) & ".  As always, we appreciate your support." _
  & vbCrLf _
  & vbCrLf & "If you have any questions, don't hesitate to contact me." & vbCrLf _
  & vbCrLf & "Sincerely," & vbCrLf _
  & vbCrLf & DLookup("OfcFName", "Admin Table", "LimitNbr=1") & " " & DLookup("OfcLName", "Admin Table", "LimitNbr=1") & vbCrLf & vbCrLf
  ' .Display True
    .Send
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "Update Total Paid for Dues ack Sent"
   DoCmd.Close acQuery, "Update Total Paid for Dues ack Sent"
   DoCmd.SetWarnings True
     DoCmd.Beep
     MsgBox ("Your email has been sent.")
 End With

DoCmd.GoToRecord , , acNext
End If
Exit Sub
PrintInvoice:
   DoCmd.OpenReport "Report for Printing receipt", acViewNormal
   Me.Requery
    Exit Sub
  
EmailPaymentX_Exit:
    Exit Sub

EmailPaymentX_Error:
    MsgBox Err.Description
    Resume EmailPaymentX_Exit

End Sub

 

[Gasman]

Look at the NZ() function.
Walk thorugh your code or just look where the error occurs.
You are doing two DlookUps for each item? why not just surround the first one with NZ()?

 

[theDBguy]

Which line is giving you that error?

 

[NauticalGent]

If Me.EmailX <> "" Then

I am assuming this is the culprit? If so try If Nz(Me.EmailX,"") <> "" Then

 

[Pat Hartman]

If Me.EmailX <> ""

This is not how you check for null. The "" = a Zero Length String. You need to use IsNull() or something like:

If Me.EmailX & "" <> ""

When you concatenate a ZLS to a null value, the result is a ZLS. That lets you check for both with one If statement.

 

[chuckcoleman]

First reply before I examine the other suggestions to look at. When I put a MsgBox immediately after the DIM statements and then set a break point there, I immediately get the Invalid Use of Null error.

I'll now look at the other suggestions.

 

[chuckcoleman]

OK, I tried NauticalGent's suggestion and Pat Hartman's suggestion. Neither suggestion worked. Here's what's weird. When I put the MsgBox immediately below the DIM statements that says, "Hi Chuck", when I run the code by clicking on the button the first thing I get is "Invalid Use of Null" and the next thing I get is the MsgBox.

 

[Gasman]

Step through your code with F8

 

[chuckcoleman]

Gasman, I could probably use some guidance here. In the Editor I clicked on the gray vertical bar immediately to the left of my "On Error GoTo..." line. A red dot was placed there. When I click on the "Print" button in the form I get Invalid Use of Null. I click on OK which brings the Editor to the forefront. The On Error GoTo.. line is highlighted in Yellow. When I hit F8 it takes me to the MsgBox immediately below my DIM statements. The MsgBox is highlighed in Yellow. When I click on OK in the MsgBox the TempVars.RemoveAll is now highlighted in Yellow. Hitting F8 again the next TempVars!BindX line is highlighted in Yellow. Hitting F8 again takes me to "If Me.ButonlabelXY = "Print" Then, and that line is highlighed in Yellow. Hitting F8 again takes me to "GoTo PrintInvoice" and that is highlighted in Yellow. Hitting F8 again takes me to a MsgBox immediately below PrintInvoice: The last time I hit F8 it takes me to Exit Sub.

So, I don't see where this is telling me anything, unless I simply don't understand.

 

[Gasman]

F8 will take you through the code one line at a time.
However you have to check each path.
So either you now have a valid value where there used to be Null, or you are not following the same path that was giving you Null previously.

Put the breakpoint (red dot) on the tempvars removeall and go from there.

 

[chuckcoleman]

When I set the break point on the "On Error GoTo line", which is even before the DIM statements, why do I get the Invalid Use of Null immediately.

 

[Gasman]

When I set the break point on the "On Error GoTo line", which is even before the DIM statements, why do I get the Invalid Use of Null immediately.

No idea. I set it on the first line of executing code. Not sure if that can be On Error, never set it on that line, ever.

 

[chuckcoleman]

I moved the break point to a MsgBox immediately after the DIM statements and no change in what I see in the Editor and the Invalid Use of Null continues. Frustrating!

 

[Gasman]

Upload enough of the DB so we can see the issue.
The error is happening before you get to the breakpoint? Are you even in the correct block of code?

 

[Mike Krailo]

Is there an actual error number associated with the invalid use of null shown? If not, this could be a form error and not necessarily something with your button code. Does your overall code even compile?

Divide and conquer. Comment out all of your button code and verify there is no error. Enable parts of your code systematically until you discover the code that is causing the problem. If this method is not fruitful, then you will need to look at possible form errors. You can use the On Error event to trap form errors but you need to know the error number. Usually this would be error 94. So you can try and set up error handling in the On Error event to see if this is actually happening at the form level somehow.

Edit: I just tried to compile your code and it immediately hit on Me.ButtonLabelXY line. This is not referencing the button caption property as it should. This is not your main issue, but something to address. I'm assuming this is a button, but maybe it's just a text box.

   DoCmd.RunCommand acCmdSaveRecord
   If Me.ButtonLabelXY = "Print" Then
      GoTo PrintInvoice
   Else
         ...
   End If
 
   ' Should be:
 
   DoCmd.RunCommand acCmdSaveRecord
   If Me.ButtonLabelXY.Caption = "Print" Then
      GoTo PrintInvoice
   Else
         ...
   End If

Looking further down your code, you have code intended to verify user input in the wrong event. All of that verification code should go into the Before Update event so you can cancel the save if incorrect data is input or force user to fill out data without leaving it blank.

 

[LarryE]

'Dim CustEmail As String Dim CustEmail As Variant

I noticed you have commented out Dim CustEmail as String and used Dim CustEmail as Variant instead. Could that cause your issue?

 

[Isaac]

There are so many opportunities to improve this code I'll just throw one out there. Your code is running 10x slower than it needs to run - maybe 100x slower.

Stuff like this:
If Not IsNull(DLookup("[LName]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")) Then
LNameLU = DLookup("[LName]", "[Email for Receipt]", "[LotNbr] = '" & Nz(LotNbrLU) & "'")
End If

Is running notoriously slow dlookups twice, unnecessarily.

Declare a variable, assign it something, then test it if you want. Don't run the test numerous times

 

[Isaac]

also, it appears you may be trying to use setwarnings as a way to avoid seeing errors earlier, which would lead you to exactly where you are - a very frustrating invalid use of null that happens later than it needs to, and thus, obfuscates the actual cause and leads you to be unable to see the actual cause.

but I think maybe it's

fileName = Application.CurrentProject.Path & strSelectedFileSubDirectory

 

[Pat Hartman]

why do I get the Invalid Use of Null immediately.

If the code is not compiled, Access must compile it before it starts executing it. If you are getting the error before you execute any line of code then this is a compile error.

Try opening the code module and compiling it? What line shows the error?

 

[ebs17]

I don't know what's causing

Private Sub EmailPaymentX_Click()
'On Error GoTo EmailPaymentX_Error

Comment out the error handling.
Then, if there is an error, the culprit line is marked.