INSERT SQL writing 2 records

[doobybug]

Hi, I have the following code and for some reason when it is executing it is creating a blank record and after the record that I want to be written is inserted. Therefore 2 records are being added every time the save button is pressed. Any ideas why?

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

[June7]

Why are you using INSERT sql to input records? Are form and its controls BOUND to data?

A record is committed to table when: 1. close table/query/form, or 2. move to another record, or 3. run code

You are doing option 3 with the INSERT and then you set fields to Null. If these fields are BOUND then you are creating a record with no data which is committed to the table when 1 or 2 happen. If you didn't set to Null then when 1 or 2 happen, would attempt to commit with the same data.

[doobybug]

Yes I think the fields are bound to data as they have the name of the field in the control source...can I unbound them? Will this solve the problem?

[doobybug]

Problem solved!! Thank you so so much!!

[June7]

But why would you want to use UNBOUND form? That will require a LOT of code to load and save data to/from forms.

Enter data to bound form and controls and it feeds directly to table, no need for INSERT code.

[ssanfu]

Agree with June.....

Also, you have

    Dim Day As Integer
    Dim Month As String
    Dim Year As Integer
    Dim Description As String
.
    Dim page As String

The highlighted words are reserved words and/or built in functions in Access and shouldn't be used as variable names.