Invalid use of Null

Dear All,

I've table (tblScheduleT) represent the Payment Schedule with PaymentNumber, DueAmount and Principal, and also I've created an unbound form with the following fields :-

P1From   (Represent Period 1 from)
P1To       (Represent Period 1 TO)
P1AMT   (Represent Period 1 Amount)

P2From   (Represent Period 2 from)
P2To       (Represent Period 2 TO)
P2AMT   (Represent Period 2 Amount)

P3From   (Represent Period 3 from)
P3To       (Represent Period 3 TO)
P3AMT   (Represent Period 3 Amount)

P4From   (Represent Period 4 from)
P4To       (Represent Period 4 TO)
P4AMT   (Represent Period 4 Amount)

P5From   (Represent Period 5 from)
P5To       (Represent Period 5 TO)
P5AMT   (Represent Period 5 Amount)

I have created the below code to update the tblscheduleT with the new value from the unbound form, to check what ever group of field is fill then to update the record accordingly.

The code is working fine if All fields is fill, But if there is any group of fields not fill (i.e. P5From, P5To, P5AMT) then a message showing (Invalid use of Null)

Any help would be appreciated.

Thanks,

Private Sub CmdUpdate1_Click()

    On Error GoTo Err_CmdUpdate1_Click

Dim CRI As String
Dim strSQL As String
Dim P1AMT As String
Dim P1FM As String
Dim P1TO As String

Dim strSQL2 As String
Dim P2AMT2 As String
Dim P2FM2 As String
Dim P2TO2 As String

Dim strSQL3 As String
Dim P3AMT3 As String
Dim P3FM3 As String
Dim P3TO3 As String

Dim strSQL4 As String
Dim P4AMT4 As String
Dim P4FM4 As String
Dim P4TO4 As String

Dim strSQL5 As String
Dim P5AMT5 As String
Dim P5FM5 As String
Dim P5TO5 As String


CRI = Me.CONT_REF

P1FM = Me.P1_FROM
P1TO = Me.P1_TO
P1AMT = Me.P1_AMT

P2FM2 = Me.P2_FROM
P2TO2 = Me.P2_TO
P2AMT2 = Me.P2_AMT

P3FM3 = Me.P3_FROM
P3TO3 = Me.P3_TO
P3AMT3 = Me.P3_AMT

P4FM4 = Me.P4_FROM
P4TO4 = Me.P4_TO
P4AMT4 = Me.P4_AMT

P5FM5 = Me.P5_FROM
P5TO5 = Me.P5_TO
P5AMT5 = Me.P5_AMT

DoCmd.SetWarnings False


If Not IsNull(Me.P1_FROM) And Not IsNull(Me.P1_TO) And Not IsNull(Me.P1_AMT) Then

strSQL = "UPDATE tblScheduleT SET tblScheduleT.[ST_AmountDue] = " & P1AMT & " ," & _
            "tblScheduleT.ST_Principal = " & P1AMT & " " & _
            "WHERE (((tblScheduleT.[ST_Contract_Ref_ID])= " & Chr$(34) & CRI & Chr$(34) & ") AND ((tblScheduleT.[ST_PaymentNumber])Between " & P1FM & "  and " & P1TO & "));"
DoCmd.RunSQL strSQL

End If


If Not IsNull(Me.P2_FROM) And Not IsNull(Me.P2_TO) And Not IsNull(Me.P2_AMT) Then

strSQL2 = "UPDATE tblScheduleT SET tblScheduleT.[ST_AmountDue] = " & P2AMT2 & " ," & _
            "tblScheduleT.ST_Principal = " & P2AMT2 & " " & _
            "WHERE (((tblScheduleT.[ST_Contract_Ref_ID])= " & Chr$(34) & CRI & Chr$(34) & ") AND ((tblScheduleT.[ST_PaymentNumber])Between " & P2FM2 & "  and " & P2TO2 & "));"

DoCmd.RunSQL strSQL2

End If


If Not IsNull(Me.P3_FROM) And Not IsNull(Me.P3_TO) And Not IsNull(Me.P3_AMT) Then

strSQL3 = "UPDATE tblScheduleT SET tblScheduleT.[ST_AmountDue] = " & P3AMT3 & " ," & _
            "tblScheduleT.ST_Principal = " & P3AMT3 & " " & _
            "WHERE (((tblScheduleT.[ST_Contract_Ref_ID])= " & Chr$(34) & CRI & Chr$(34) & ") AND ((tblScheduleT.[ST_PaymentNumber])Between " & P3FM3 & "  and " & P3TO3 & "));"

DoCmd.RunSQL strSQL3

End If



If Not IsNull(Me.P4_FROM) And Not IsNull(Me.P4_TO) And Not IsNull(Me.P4_AMT) Then

strSQL4 = "UPDATE tblScheduleT SET tblScheduleT.[ST_AmountDue] = " & P4AMT4 & " ," & _
            "tblScheduleT.ST_Principal = " & P4AMT4 & " " & _
            "WHERE (((tblScheduleT.[ST_Contract_Ref_ID])= " & Chr$(34) & CRI & Chr$(34) & ") AND ((tblScheduleT.[ST_PaymentNumber])Between " & P4FM4 & "  and " & P4TO4 & "));"

DoCmd.RunSQL strSQL4

End If



If Not IsNull(Me.P5_FROM) And Not IsNull(Me.P5_TO) And Not IsNull(Me.P5_AMT) Then

strSQL5 = "UPDATE tblScheduleT SET tblScheduleT.[ST_AmountDue] = " & P5AMT5 & " ," & _
            "tblScheduleT.ST_Principal = " & P5AMT5 & " " & _
            "WHERE (((tblScheduleT.[ST_Contract_Ref_ID])= " & Chr$(34) & CRI & Chr$(34) & ") AND ((tblScheduleT.[ST_PaymentNumber])Between " & P5FM5 & "  and " & P5TO5 & "));"

DoCmd.RunSQL strSQL5
Else
MsgBox ("cmpleted")


End If


DoCmd.SetWarnings True

Exit_CmdUpdate1_Click:
    Exit Sub

Err_CmdUpdate1_Click:
    MsgBox Err.Description
    Resume Exit_CmdUpdate1_Click


End Sub

Select all Open in new window