We help IT Professionals succeed at work.
Get Started

Invalid use of Null

148 Views
Last Modified: 2016-02-11
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

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE