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

Open in new window

TAB-000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use the Nz() function to handle null values, i.e,

CRI = Nz(Me.CONT_REF,"")  ' for string

xxx=nz(me,PXX,0)      'for number
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TAB-000Author Commented:
Thanks a millions for quick answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.