Results 1 to 10 of 10
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Invalid Use of Null

    In this code which started on a different thread the program throws an error:

    Invalid Use of Null:

    The code is shown now
    Code:
    Option Compare Database
    Option Explicit
    Private Sub chkMeeting_Invite_Sent_AfterUpdate()
    'Enable/disable Send Ticket command button
    'if Ticket assigned checkbox is checked/unchecked
        If Me.chkMeeting_Invite_Sent = -1 Then
            Me.cmdSend_Email.Enabled = False
        Else
            Me.cmdSend_Email.Enabled = True
        End If
    End Sub
    Private Sub btnClear_Click()
    '     Me.txtTo = Null
    '     Me.txtBody = Null
         Me.txtAttachment = Null
    End Sub
    Private Sub cmdSend_Email_Click()
        On Error GoTo Err_cmdSend_Email_Click
        
        Dim stWhere As String        '--Criteria for DLookup
        Dim varTO As Variant         '--Address for SendObject
        Dim stText As String         '--E-mail text
        Dim RecDate As Variant       '--Rec date for e-mail text
        Dim stSubject As String      '--Subject line of email
        Dim StWho As String          '--Reference to tblPersonnel
        Dim strBranchhead As String  ' Branchhead making meeting invite
        Dim strSQL As String         '--Create SQL update statement
        Dim stMeetingID As String
        Dim errLoop As Error
        
        '-- Combo of name to send email to
        StWho = Me.cboFullname
        stWhere = "tblusers"
        '--Looks up email address from Personnel Meeting Query
        varTO = DLookup("stEmail", "", stWhere)
        
        stSubject = ":: Meeting Invitation ::"
        RecDate = Me.txtMeetingDate
        '--Branchhead who emails invitation
        strBranchhead = Me.cboBranchheads
        
        stText = "You have neen invited to a meeting." & Chr$(13) & Chr$(13) & _
                 "Meeting: " & stMeetingID & Chr$(13) & _
                 "This mmmeting invitation has been sent to you by: " & strBranchhead & Chr$(13) & _
                 "Received Date:  " & RecDate & Chr$(13) & Chr$(13) & _
                 "This is an automated message. Please do not respond to this e-mail."
                 
                 
     'Write the e-mail content for sending to employee
     DoCmd.SendObject , , acFormatTXT, varTO, , , stSubject, stText, -1
     
     'Set the update statement to disable command button
     'once email has been sent
     strSQL = "UPDATE tblMeetingID, SET tblMeetingID.ysnMeeting_Invitite_SentID = -1 " & _
              "Where tblMeetingID.lngMeetingID = " & Me.txtMeetingID & ";"
              
     On Error GoTo Err_Execute
     CurrentDb.Execute strSQL, dbFailOnError
     On Error GoTo 0
     
     'Requery checkbox to show checked
     'after update statement has ran
     'and disable send mail command button
     Me.chkMeeting_Invite_Sent.Requery
     Me.chkMeeting_Invite_Sent.SetFocus
     Me.cmdSend_Email.Enabled = False
     
         Exit Sub
     
    Err_Execute:
     
         ' Notify user of any errors that result from
         ' executing the query
         If DBEngine.Errors.Count > 0 Then
             For Each errLoop In DBEngine.Errors
             MsgBox "Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description
             Next errLoop
         End If
         
         Resume Next
         
    Exit_cmdSend_Email_Click:
        Exit Sub
         
    Err_cmdSend_Email_Click:
         MsgBox Err.Description
         Resume Exit_cmdSend_Email_Click
         
    End Sub
    Private Sub Form_Current()
    ' Enable Send Ticket command button
    ' if meeting invite sent
    ' checkbox is not checked
         If Me.chkMeeting_Invite_Sent = True Then
             Me.cmdSend_Email.Enabled = False
         Else
             Me.cmdSend_Email.Enabled = True
         End If
     End Sub
              
      Private Sub bntBrowse_Click()
        Dim fileDiag As FileDialog
        Dim file As Variant
        
        Set fileDiag = FileDialog(msoFileDialogFilePicker)
        
        fileDiag.AllowMultiSelect = False
        If fileDiag.Show Then
            For Each file In fileDiag.SelectedItems
                Me.txtAttachment = file
            Next
         End If
    End Sub
    In the Private Sub cmdMailTciket_Click()

    There are actually two

    Exit Sub

    lines. I put the command:

    Exit_cmdSend_Email_Click:

    above the second Exit Sub the one closest to the



    End Sub

    line.

    It threw the error: Invalid Use of Null.

    But it had no line designation as to where it actually occurred in the code.

    So I am unsure what is going on. I posted the code and hopefully, that can show the error.

    I do not see it.

    Any help appreciated. Thanks in advance.

    Respectfully,


    Lou Reed

  2. #2
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Lou, that is not where I suggested you place that line, and you added an extra Exit Sub for some reason. Here is the procedure from your database posted in the previous thread, with the correction:
    Code:
    Private Sub cmdSend_Email_Click()    On Error GoTo Err_cmdSend_Email_Click
        
        Dim stWhere As String        '--Criteria for DLookup
        Dim varTO As Variant         '--Address for SendObject
        Dim stText As String         '--E-mail text
        Dim RecDate As Variant       '--Rec date for e-mail text
        Dim stSubject As String      '--Subject line of email
        Dim StWho As String          '--Reference to tblPersonnel
        Dim strBranchhead As String  ' Branchhead making meeting invite
        Dim strSQL As String         '--Create SQL update statement
        Dim stMeetingID As String
        Dim errLoop As Error
        
        '-- Combo of name to send email to
        StWho = Me.cboFullname
        stWhere = "tblusers"
        '--Looks up email address from Personnel Meeting Query
        varTO = DLookup("stEmail", "", stWhere)
        
        stSubject = ":: Meeting Invitation ::"
        RecDate = Me.txtMeetingDate
        '--Branchhead who emails invitation
        strBranchhead = Me.cboBranchheads
        
        stText = "You have neen invited to a meeting." & Chr$(13) & Chr$(13) & _
                 "Meeting: " & stMeetingID & Chr$(13) & _
                 "This mmmeting invitation has been sent to you by: " & strBranchhead & Chr$(13) & _
                 "Received Date:  " & RecDate & Chr$(13) & Chr$(13) & _
                 "This is an automated message. Please do not respond to this e-mail."
                 
                 
     'Write the e-mail content for sending to employee
     DoCmd.SendObject , , acFormatTXT, varTO, , , stSubject, stText, -1
     
     'Set the update statement to disable command button
     'once email has been sent
     strSQL = "UPDATE tblMeetingID, SET tblMeetingID.ysnMeeting_Invitite_SentID = -1 " & _
              "Where tblMeetingID.lngMeetingID = " & Me.txtMeetingID & ";"
              
     On Error GoTo Err_Execute
     CurrentDb.Execute strSQL, dbFailOnError
     On Error GoTo 0
     
     'Requery checkbox to show checked
     'after update statement has ran
     'and disable send mail command button
     Me.chkMeeting_Invite_Sent.Requery
     Me.chkMeeting_Invite_Sent.SetFocus
     Me.cmdSend_Email.Enabled = False
     
    Err_cmdSend_Email_Click:
        Exit Sub
     
    Err_Execute:
     
         ' Notify user of any errors that result from
         ' executing the query
         If DBEngine.Errors.Count > 0 Then
             For Each errLoop In DBEngine.Errors
             MsgBox "Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description
             Next errLoop
         End If
         
         Resume Next
         
    Err_cmdSend_Email_Click:
         MsgBox Err.Description
         Resume Exit_cmdSend_Email_Click
         
    End Sub
    Make the correction as suggested, then come back if you still get the Null error, and indicate the exact line it occurs on.

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Yes, I did add that additional Exit Sub line. I am copying this from another program that I was given that works. It had two Exit Sub lines and that is why I added the second.

    I will place the line as you suggested.

    Respectfully,


    Lou Reed

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Because you are using error handling (a good thing, BTW), it can be difficult to find where the Invalid use of Null error is. Typically this caused by trying to assign a Null value to a variable declared as a String, Integer, Long, etc - not a Variant.

    To find where this is happening, you can temporarily turn off all error handling.
    With any VBA code module open:
    - select Tools - Options from the menu
    - click the General tab
    - In the Error Trapping box on the right, select the "Break on All Errors" option

    Click OK, close the dialog, and close the module and VBA

    Run your code again - it will stop on the offending line, and you can click Debug to open the code window; the offending line will be highlighted in yellow.

  5. #5
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    This still throws an Invalid Use of Null error.

    I still got an invalid use of null when I put the statement code where you said. I tried to copy/imitate what I saw in another program as I said. The had two Exit SUB statements.

    I removed the first one and put the statement in question right before the Exit Sub statement.

    It was the only one in the program so I guess it would be hard to place it incorrectly.

    I still got an invalid use of null.

    I am attaching the program to this post. I am still wondering why the other program uses two Exit Sub statements.

    We only use one. I will try as you said and find it. That could be difficult.

    Thanks in advance for your help.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  6. #6
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Can you please tell us what line the error occurred on, and how to replicate it?

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I would like to do that, but the compiler does not tell me It only says invalid use of null. Nothing more. It shows no line of code only the error "Invalid use of null".

    I will try some approaches suggested in the thread and see what I find.

    R,


    Lou Reed

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    "Invalid use of Null" is not a compile error - it is a run-time error. That is why you need to temporarily turn off error handling in order to find the offending code. See my post #4 above.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    varTO = DLookup("stEmail", "", stWhere)

    Don't you need a tablename and not "" in that 2nd spot. So varTO might be null and then get error later when passed to function?

  10. #10
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Thanks I will check it.

    R,

    Lou Reed

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Invalid use of null?
    By snipe in forum Programming
    Replies: 12
    Last Post: 05-12-2015, 04:53 PM
  2. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  3. Invalid use of null
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 03-24-2013, 11:05 PM
  4. Invalid use of Null
    By justauser in forum Forms
    Replies: 2
    Last Post: 11-28-2012, 12:33 PM
  5. Invalid Use of Null!?!
    By Kipster1203 in forum Access
    Replies: 4
    Last Post: 05-13-2010, 06:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums