?
Solved

Get error Invalid use of Null Runtime error 94

Posted on 2012-09-06
7
Medium Priority
?
896 Views
Last Modified: 2012-09-06
On click this code is suppose to check for duplicates and check for null values before it saves a record .


what happens if you try to save with out the serial number the Message Popsup must contain a value. When you hit Ok. A error message pops up. "Invalid use of null Error 94.
It highlights the part of the code "SID = Me.Serial.Value".

Then if you do have a serial and try to save the data nothing happens the code just want work.

Here is the code:


Private Sub Command14_Click()


If IsNull(Me![Serial]) Then
        If MsgBox("'Vehicle Serial Number Must Contain a value." & Chr(13) & Chr(10) & _
        "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
        "Press 'Cancel' to abort the record.", _
        vbOKCancel, "A Required field is Null") = vbCancel Then
       
        Else
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Serial.Value
    stLinkCriteria = "[Serial]=" & "'" & SID & "'"

    'Check  table for duplicate
    If DCount("Serial", "tblInduction", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Vehicle Serial  Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You Can Not Save This Information.", _
               vbInformation, "Duplicate Information"
        'Go to record of original  Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
        'Go to record of original Student Number
     
    Set rsc = Nothing
Else
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryappendInductionHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryappendInductionChecksHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryDeleteTransportHistoryChecks", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppendTransportInductiionChecks", acViewNormal, acEdit
   
    DoCmd.OpenQuery "qryDeleteTransportInductionHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppendTransportInductionHistory", acViewNormal, acEdit
    MsgBox ("The record is Saved.")

    Command14_Click_Exit:
    Exit Sub

Command14_Click_Err:
    MsgBox Error$
    Resume Command14_Click_Exit
    End If
    End If
    End If
    End Sub
InductionCheckListhelpnullduplic.zip
0
Comment
Question by:gigifarrow
7 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 664 total points
ID: 38372186
Is the serial number text?

If so, use this:

SID = "" & Me.Serial.Value
    stLinkCriteria = "[Serial]=" & "'" & SID & "'"

Open in new window


If it is numeric, use this:

SID = NZ(Me.Serial.Value,0)
    stLinkCriteria = "[Serial]=" & SID

Open in new window

0
 

Author Comment

by:gigifarrow
ID: 38372237
Thank you for your help I have implemented this. But when I enter a serial number (which is text field(  and save. none of the code is  not screening for duplicates and not saving the record.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 668 total points
ID: 38372334
change your code to this


Private Sub Command14_Click()


If IsNull(Me![Serial]) Then
        If MsgBox("'Vehicle Serial Number Must Contain a value." & Chr(13) & Chr(10) & _
        "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
        "Press 'Cancel' to abort the record.", _
        vbOKCancel, "A Required field is Null") = vbCancel Then
 'changes  
           me.undo
               exit sub

        Else
'changes
        me.serial.setfocus
        exit sub
        end if
end If

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Serial.Value
    stLinkCriteria = "[Serial]=" & "'" & SID & "'"

    'Check  table for duplicate
    If DCount("Serial", "tblInduction", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Vehicle Serial  Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You Can Not Save This Information.", _
               vbInformation, "Duplicate Information"
        'Go to record of original  Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
        'Go to record of original Student Number
     
    Set rsc = Nothing
Else
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryappendInductionHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryappendInductionChecksHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryDeleteTransportHistoryChecks", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppendTransportInductiionChecks", acViewNormal, acEdit
   
    DoCmd.OpenQuery "qryDeleteTransportInductionHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppendTransportInductionHistory", acViewNormal, acEdit
    MsgBox ("The record is Saved.")

    Command14_Click_Exit:
    Exit Sub

Command14_Click_Err:
    MsgBox Error$
    Resume Command14_Click_Exit
    End If
'    End If
'    End If
    End Sub
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 5

Expert Comment

by:Gerry Bartley
ID: 38372414
capricorn1's solution will not get rid of the null issue as you will still enter the code segment on cancel

  SID = Me.Serial.Value which is null and will raise an exception

If IsNull(Me![Serial]) Then
        If MsgBox("'Vehicle Serial Number Must Contain a value." & Chr(13) & Chr(10) & _
        "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
        "Press 'Cancel' to abort the record.", _
        vbOKCancel, "A Required field is Null") = vbCancel Then
        ' Add these 2 line
        Me.Recordset.MoveLast
        Exit Sub
        Else
'changes
        Me.Serial.SetFocus
        Exit Sub
End If

Open in new window


EDIT: I see capricorn1 already put it in .......
0
 
LVL 5

Assisted Solution

by:Gerry Bartley
Gerry Bartley earned 668 total points
ID: 38372456
just adding a missing else to capricorn1's code


Private Sub Command14_Click()


If IsNull(Me![Serial]) Then
        If MsgBox("'Vehicle Serial Number Must Contain a value." & Chr(13) & Chr(10) & _
        "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
        "Press 'Cancel' to abort the record.", _
        vbOKCancel, "A Required field is Null") = vbCancel Then
        Me.Undo
       
        Exit Sub
        Else
'changes
        Me.Serial.SetFocus
        Exit Sub
        End If
    Else
       
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Serial.Value
    stLinkCriteria = "[Serial]=" & "'" & SID & "'"

    'Check  table for duplicate
    If DCount("Serial", "tblInduction", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Vehicle Serial  Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You Can Not Save This Information.", _
               vbInformation, "Duplicate Information"
        'Go to record of original  Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
        'Go to record of original Student Number
     
    Set rsc = Nothing
Else
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryappendInductionHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryappendInductionChecksHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryDeleteTransportHistoryChecks", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppendTransportInductiionChecks", acViewNormal, acEdit
   
    DoCmd.OpenQuery "qryDeleteTransportInductionHistory", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppendTransportInductionHistory", acViewNormal, acEdit
    MsgBox ("The record is Saved.")

Command14_Click_Exit:
    Exit Sub

Command14_Click_Err:
    MsgBox Error$
    Resume Command14_Click_Exit
    End If
    End If

    End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38372468
@gbartley
<just adding a missing else to capricorn1's code>

check the code again...
0
 
LVL 5

Expert Comment

by:Gerry Bartley
ID: 38372637
@capricorn1


You're spot on. Nothing wrong with your code.

regards Gerry

@gigifarrow
Ignore my comments
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 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