Code:
Private Sub btnSave_Click() Dim refNo As String
Dim Volume As Integer
Dim Title As String
Dim ContractNo As Integer
Dim PlaceOfCreationNo As Integer
Dim Day As Integer
Dim Month As String
Dim Year As Integer
Dim Description As String
Dim subjTerms As String
Dim TypeOfContractNo As Integer
Dim page As String
Dim lan1 As String
Dim lan2 As String
Dim lan3 As String
Dim remarks As String
Dim sql As String
If (IsNull([Form_tblCaseInfo subform].txtContractNo) Or IsNull([Form_tblCaseInfo subform].txtPage) Or IsNull([Form_tblCaseInfo subform].cmbTypeOfContract.Column(0)) Or IsNull([Form_frmCaseInfo].cmbNotary.Column(0)) Or IsNull([Form_frmCaseInfo].cmbVolume)) Then
MsgBox "Notary, Volume, Contract No, Type of Contract and Page/Folio cannot be left blank"
Else
refNo = [Form_frmCaseInfo].cmbNotary.Column(0)
Volume = [Form_frmCaseInfo].cmbVolume.Value
ContractNo = Me.txtContractNo
If (Not IsNull(Me.txtTitle)) Then
Title = Me.txtTitle
Else
Title = ""
End If
If (Not IsNull(Me.txtDay)) Then
Day = Me.txtDay
Else
Day = 0
End If
If (Not IsNull(Me.txtMonth)) Then
Month = Me.txtMonth
Else
Month = ""
End If
If (Not IsNull(Me.txtYear)) Then
Year = Me.txtYear
Else
Year = 0
End If
If (Not IsNull(Me.txtPage)) Then
page = Me.txtPage
End If
If (Not IsNull(Me.cmbPlaceOfCreation.Column(0))) Then
PlaceOfCreationNo = Me.cmbPlaceOfCreation.Column(0)
Else
PlaceOfCreationNo = 1
End If
If (Not IsNull(Me.cmbTypeOfContract.Column(0))) Then
TypeOfContractNo = Me.cmbTypeOfContract.Column(0)
End If
If (Not IsNull(Me.txtDescription)) Then
Description = Me.txtDescription
Else
Description = ""
End If
If (Not IsNull(Me.txtSubjectTerms)) Then
subjTerms = Me.txtSubjectTerms
Else
subjTerms = ""
End If
If (Not IsNull(Me.cmbLang1.Column(0))) Then
lan1 = Me.cmbLang1.Column(0)
Else
lan1 = 4
End If
If (Not IsNull(Me.cmbLang2.Column(0))) Then
lan2 = Me.cmbLang2.Column(0)
Else
lan2 = 4
End If
If (Not IsNull(Me.cmbLang3.Column(0))) Then
lan3 = Me.cmbLang3.Column(0)
Else
lan3 = 4
End If
If (Not IsNull(Me.txtRemarks)) Then
remarks = Me.txtRemarks
Else
remarks = ""
End If
If Not IsNull(DLookup("[CaseInfoNo]", "[tblCaseInfo]", "[NotaryRefNo]='" & refNo & "' And [Volume] = " & Volume & " And [Page/Folio]='" & page & "' And [ContractNo]=" & ContractNo & "")) Then
MsgBox "Duplicate!"
Else
If (filePath = Null) Then
sql = "INSERT INTO tblCaseInfo([NotaryRefNo], [Volume], [Title], [ContractNo], [Day], [Mnth], [Yr], [Description], [Subject Terms], [Page/Folio], [Language1], [Language2], [Language3], [PlaceOfCreationNo], [TypeOfContractNo], [Remarks])" & _
" VALUES('" & refNo & "'," & Volume & ", '" & Title & "'," & ContractNo & ", " & Day & ", '" & Month & "', " & Year & ", '" & Description & "', '" & subjTerms & "', '" & page & "', " & lan1 & ", " & lan2 & ", " & lan3 & ", " & PlaceOfCreationNo & ", " & TypeOfContractNo & ", '" & remarks & "')"
Else
sql = "INSERT INTO tblCaseInfo([NotaryRefNo], [Volume], [Title], [ContractNo], [Day], [Mnth], [Yr], [Description], [Subject Terms], [Page/Folio], [Language1], [Language2], [Language3], [PlaceOfCreationNo], [TypeOfContractNo], [Scan], [Remarks])" & _
" VALUES('" & refNo & "'," & Volume & ", '" & Title & "'," & ContractNo & ", " & Day & ", '" & Month & "', " & Year & ", '" & Description & "', '" & subjTerms & "', '" & page & "', " & lan1 & ", " & lan2 & ", " & lan3 & ", " & PlaceOfCreationNo & ", " & TypeOfContractNo & ", '" & filePath & "', '" & remarks & "')"
End If
On Error GoTo ErrorMessage
CurrentDb.Execute sql, dbFailOnError
MsgBox "Record saved"
End If
[Form_frmCaseInfo].cmbNotary = Null
[Form_frmCaseInfo].cmbVolume = Null
Me.txtTitle = Null
Me.txtContractNo = Null
Me.txtDay = Null
Me.txtMonth = Null
Me.txtYear = Null
Me.txtDescription = Null
Me.txtSubjectTerms = Null
Me.txtPage = Null
Me.cmbLang1 = Null
Me.cmbLang2 = Null
Me.cmbLang3 = Null
Me.cmbPlaceOfCreation.Value = Null
Me.cmbTypeOfContract.Value = Null
Me.txtRemarks = Null
filePath = ""
End If
Exit Sub
ErrorMessage:
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") "
MsgBox strMsg
MsgBox "Saving unsuccesful"
End Sub