[RESOLVED] Invalid Use of Null

[manofsteel00]

I'm getting an invalid use of null error when calling my addZeroOH function. I have checked to see if there is a null and a message box never pops up; however, I do have a msgbox statement right before the call to see what the parameters look like and the MANUID parameter is missing. Therefore, when the addZeroOH function is called I get a Invalid Use of Null error. Furthermore, I have the MANUID and HITI parameters as optional parameters to try to avoid this. Any help would be greatly appreciated.

'ADDZEROOH()*******************************************************************
Private Sub addZeroOH(PICKSL As String, PROD As Long, desc As String, _
                      Optional ByVal manuid As String = "N/A", _
                      Optional ByVal HITI As String = "N/A")
    Dim sql As String
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\CycleCount.mdb"
    newConnection.Open
    Set newRecordSet = New ADODB.recordSet
    
    sql = "Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK," & _
        "[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('" & _
        PICKSL & "','',''," & PROD & ",'" & desc & "','','" & manuid & "','" _
        & HITI & "',''," & 0 & ",'')"
Debug.Print "INSERT INTO MAIN"
Debug.Print sql
    
    newConnection.Execute sql
    
    sql = "Insert into [ALTER] (USERNAME,DATEOFCHANGE," & _
        "TYPEOFCHANGE,PICKSL,SLOT,RES,PROD,[DESC],PACK," & _
        "[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES (" & _
        "'p6b0438',#" & Now() & "#," & "'ZERO OH ADDED','" & _
        PICKSL & "','',''," & PROD & ",'" & desc & "','','" & manuid & "','" _
        & HITI & "',''," & 0 & ",'')"
Debug.Print "INSERT INTO ALTER"
Debug.Print sql
    
    newConnection.Execute sql
    
    'newRecordSet.Close
    'newConnection.Close
    Set newRecordSet = Nothing
    Set newConnection = Nothing
End Sub
'ENDOF*ADDZEROOH()*************************************************************
 
'LOOPTHRUZEROOH()**************************************************************
Private Sub loopThruZeroOH()
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\Z028.mdb"
    connection.Open
    Set recordSet = New ADODB.recordSet
    recordSet.Open "PICKSL", connection, adOpenKeyset, adLockPessimistic, _
        adCmdTable
        
    If (Not recordSet.EOF) Then
        recordSet.MoveFirst
    End If
    Do Until recordSet.EOF
        recordSet.Fields("PICKSL") = Replace(recordSet.Fields("PICKSL"), "'", "")
        recordSet.Fields("PROD") = Replace(recordSet.Fields("PROD"), "'", "")
        recordSet.Fields("DESC") = Replace(recordSet.Fields("DESC"), "'", "")
        'recordSet.Fields("MANUID") = Replace(recordSet.Fields("MANUID"), "'", "")
        recordSet.Fields("HITI") = Replace(recordSet.Fields("HITI"), "'", "")
        'recordSet.Fields("OH") = Replace(recordSet.Fields("OH"), "'", "")
           
        Set newConnection = New ADODB.connection
        newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source= G:\Finance\Inventory Control\CycleCount\DB\CycleCount.mdb"
        newConnection.Open
        Set newRecordSet = New ADODB.recordSet
        newRecordSet.Open "MAIN", newConnection, adOpenKeyset, adLockPessimistic, _
            adCmdTable
        
        Dim foundInMain As Boolean
        foundInMain = False
        
        newRecordSet.MoveFirst
        Do Until newRecordSet.EOF
            If (newRecordSet.Fields("PICKSL") = recordSet.Fields("PICKSL")) Then
                foundInMain = True
                newRecordSet.Fields("PALLET QTY") = 0
            End If
            newRecordSet.MoveNext
        Loop
        
        If (foundInMain = True) Then
            newRecordSet.Close
            
            Dim sql As String
            sql = "Insert into [ALTER] (USERNAME,DATEOFCHANGE," & _
                  "TYPEOFCHANGE,PICKSL,SLOT,RES,PROD,[DESC],PACK," & _
                  "[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES (" & _
                  "'p6b0438',#" & Now() & "#," & "'ZERO OH UPDATED','" & _
                  recordSet.Fields("PICKSL") & "','',''," & recordSet.Fields("PROD") _
                  & ",'" & recordSet.Fields("desc") & "','','" & recordSet.Fields("manuid") & "','" _
                  & recordSet.Fields("HITI") & "',''," & 0 & ",'')"
 Debug.Print "INSERT INTO ALTER ZERO"
 Debug.Print sql
            newConnection.Close
            Set newRecordSet = Nothing
            Set newConnection = Nothing
        Else
            newRecordSet.Close
            newConnection.Close
            Set newRecordSet = Nothing
            Set newConnection = Nothing
                        
If (recordSet.Fields("PICKSL") = "") Then
    MsgBox ("PICKSL is null")
ElseIf (recordSet.Fields("PROD") = "") Then
    MsgBox ("PROD is null")
ElseIf (recordSet.Fields("DESC") = "") Then
    MsgBox ("DESC is null")
ElseIf (recordSet.Fields("MANUID") = "") Then
    MsgBox ("MANUID is null")
ElseIf (recordSet.Fields("HITI") = "") Then
    MsgBox ("HITI is null")
End If
MsgBox (recordSet.Fields("PICKSL") & ", " _
      & recordSet.Fields("PROD") & ", " _
      & recordSet.Fields("DESC") & ", " _
      & recordSet.Fields("MANUID") & ", " _
      & recordSet.Fields("HITI"))
/////Next line gives error message/////////////////////////
            Call addZeroOH(recordSet.Fields("PICKSL"), _
                           recordSet.Fields("PROD"), _
                           recordSet.Fields("DESC"), _
                           recordSet.Fields("MANUID"), _
                           recordSet.Fields("HITI"))
        End If
        
        recordSet.MoveNext
        DoEvents
    Loop
 
End Sub
'ENDOF*LOOPTHRUZEROOH()********************************************************

[john24]

use IsNull not "", "" actually has a value that is not null

[Comintern]

Appending a vbNullString to the end of any field you pull will prevent this:

Call addZeroOH(Recordset.Fields("PICKSL") & vbNullString, _
               Recordset.Fields("PROD") & vbNullString, _
               Recordset.Fields("DESC") & vbNullString, _
               Recordset.Fields("MANUID") & vbNullString, _
               Recordset.Fields("HITI") & vbNullString)

[Marcogol]

use IsNull not "", "" actually has a value that is not null

re isnull not ""... where do i put it? i also recive this message

[Marcogol]

hello?

[Comintern]

He meant to replace this:

If Value = "" Then

with this:

If IsNull(Value) Then