Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: run-time error 94 Invalid use of null

    Access 2007 form - I'm getting a run-time error 94 Invalid use of null on lngID

    Private Sub Form_Current()

    Dim lngID As Long

    lngID = DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'")
    Me.STATLU.Value = DLookup("STATLU", "REGSTAT32511", "ID=" & lngID)


    End Sub

    This code is creating a new record in a subform and copying one field from the previous record.
    It errors every other time, but still triggers.

    I'm not clear as to how Null is coming into play at all?

    Anyone familiar with this?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The DMax() will return Null if it finds no records matching the criteria. The DLookup() will probably error if lngID is Null.
    Paul

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The domain functions return Null if they cannot find a matching record in the named domain.

    You need to wrap your call to DMax in Nz, and set the return_if_null argument to a Long Integer that will never occur in REGSTAT32511. Then you need to test lngID for this value before proceeding to the DLookup call:
    Code:
    Dim lngID As Long
    
    lngID = Nz(DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'"), −2,147,483,648)
    
    If lngID = −2,147,483,648 Then
    
    'Error handling goes here
    
    Else
    
    Me.STATLU.Value = DLookup("STATLU", "REGSTAT32511", "ID=" & lngID)
    
    End If
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by weejas View Post
    The domain functions return Null if they cannot find a matching record in the named domain.

    You need to wrap your call to DMax in Nz, and set the return_if_null argument to a Long Integer that will never occur in REGSTAT32511. Then you need to test lngID for this value before proceeding to the DLookup call:
    Code:
    Dim lngID As Long
    
    lngID = Nz(DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'"), −2,147,483,648)
    
    If lngID = −2,147,483,648 Then
    
    'Error handling goes here
    
    Else
    
    Me.STATLU.Value = DLookup("STATLU", "REGSTAT32511", "ID=" & lngID)
    
    End If
    Thanks for this tip - I'm very green when it comes to Error Handling.
    This is the extent of what I've used:
    Private Sub AddNext_Click()
    On Error GoTo Err_AddNext_Click
    DoCmd.Close acForm, "ADDCHOOSER"
    Forms!ADDACCOUNT22509.Form!ACCOUNT.SetFocus
    DoCmd.GoToRecord , , acNewRec



    Exit_AddNext_Click:
    Exit Sub

    Err_AddNext_Click:
    MsgBox Err.Description
    Resume Exit_AddNext_Click

    End Sub

    I'm guessing this should be simple, since we've used a number that will NEVER match the ID, so what fictional looping would you suggest? Something like:
    Private Sub Form_Current()

    Dim lngID As Long

    lngID = Nz(DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'"), -2, 147, 483, 648)

    If lngID = -2,147,483,648 Then
    On Error GoTo Err_Sub Form_Current
    MsgBox Err.Description
    Resume Sub Form_Current()

    Me.STATLU.Value = DLookup("STATLU", "REGSTAT32511", "ID=" & lngID)

    End If

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm. I think you've conflated a few concepts there, but that's okay.

    Try this:
    Code:
    Private Sub Form_Current()
    
    Dim lngID As Long
    
    On Error GoTo Err_Sub Form_Current
    
    lngID = Nz(DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'"), -2, 147, 483, 648)
    
    If lngID = -2,147,483,648 Then
    
    
    'This is where you need code to prevent an error from occurring
    Msgbox "Cannot find this account!", vbCritical, "Help!"
    Exit Sub
    
    Else
    
    'This is where the code goes that will run the vast majority of the time.
    Me.STATLU.Value = DLookup("STATLU", "REGSTAT32511", "ID=" & lngID)
    
    End If 
    
    Exit Sub  'You need this to make sure that the error handling doesn't fire every time
    
    Err_Sub Form_Current:
    'This is where the code goes that deals with an error that has occurred.
    
    Msgbox "An error has occurred:" & vbCrLf & Err.Number & " - " & Err.Description, vbCritical, "Something needs fixing"
    
    End Sub
    Error handing is where you give your application a graceful way of dealing with runtime errors. Error trapping is where you think about what might go wrong and build in bits of code to cope with it instead of triggering a runtime error.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    I've gotten a Compile Error on the Nz function:
    Wrong Number of arguments or invalid property assignment

    Private Sub Form_Current()

    Dim lngID As Long

    On Error GoTo Err_Sub_Form_Current

    lngID = Nz(DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'"), -2, 147, 483, 648)

    If lngID = "-2, 147, 483, 648" Then
    MsgBox "Cannot find this Account!", vbCritical, "Help!"

    Else

    Me.STATLU.Value = DLookup("STATLU", "TLMOVEREG", "ID=" & lngID)

    End If

    Exit Sub

    Err_Sub_Form_Current
    MsgBox "An error has occured: " & vbCrLf & Err.Number & " - " & Err.Description, vbCritical, "Something needs fixing"


    End Sub

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I've never seen that error on that function before.

    Also, you need to remove the quotes and spaces from around and within the number - otherwise you'll be comparing a number to a string and that will never work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The error is because of the commas in the number; take them out.
    Paul

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I am annoyed that I missed that...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You got the quotes and spaces; that's two out of three.
    Paul

  11. #11
    Join Date
    Jun 2010
    Posts
    186
    I added the quotes because in typing the If it errored at the comma

    I also got an error at: On Error GoTo Err_Sub Form_Current
    Where it expected something, so I tried
    On Error GoTo Err_Sub_Form_Current (the extra underscore between Sub and Form) to get through

    Now I've changed the number to remove commas and spaces and quotes and the code seemed to like that, recognized it somehow and added a #

    Went back in testing it and I am getting past Nz but received:
    Compile Error:
    Sub or Function Not Defined
    on Err_Sub_Form_Current




    Private Sub Form_Current()

    Dim lngID As Long

    On Error GoTo Err_Sub_Form_Current

    lngID = Nz(DMax("ID", "REGSTAT32511", "ACCOUNT = '" & Me.ACCOUNT.Value & "'"), -2147483648#)

    If lngID = -2147483648# Then
    MsgBox "Cannot find this Account!", vbCritical, "Help!"

    Else

    Me.STATLU.Value = DLookup("STATLU", "REGSTAT32511", "ID=" & lngID)

    End If

    Exit Sub

    Err_Sub_Form_Current
    MsgBox "An error has occured: " & vbCrLf & Err.Number & " - " & Err.Description, vbCritical, "Something needs fixing"


    End Sub

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a little thing on error handling:

    Error trapping

    Note the colon required after the name.
    Paul

  13. #13
    Join Date
    Jun 2010
    Posts
    186
    Thank you!

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Glad you got it sorted out.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •