Can't save data

[abu taher]

for save I write this........

 With rs
        .Open "Select * from Dad", con, adOpenDynamic, adLockOptimistic
        .AddNew
        !SrNo = cmbbillno
        '!SrNo = GetNewNo("Dad")
        !DadItems = cmbIName
        !DadSize = cmbISize
        !Dad = txtDadIQty
        !Dadby = cmbOrTkBy.Text
        !DadDate = DadDate
        !deliverydate = deldate
        !partyname = cmbPartyName
        !amount = txtamount.Text
        !due = txtdue.Text
        !Receive = txtreceive.Text
        .Update
        .Close
        MsgBox "Information is Saved", vbInformation, Me.Caption
    End With

no problem to save data. but I want to save due in dad table and due table. so I write this code...

With rs
            .Open "Select * from Dad", con, adOpenDynamic, adLockOptimistic
            .AddNew
            !SrNo = cmbbillno
            '!SrNo = GetNewNo("Dad")
            !DadItems = cmbIName
            !DadSize = cmbISize
            !Dad = txtDadIQty
            !Dadby = cmbOrTkBy.Text
            !DadDate = DadDate
            !deliverydate = deldate
            !partyname = cmbPartyName
            !amount = txtamount.Text
            !due = txtdue.Text
            !Receive = txtreceive.Text
            .Update
            .Close
            MsgBox "Information is Saved", vbInformation, Me.Caption
        End With



         With rs1
        .Open "Select * from due where partyname = '" & Me.cmbPartyName & "'", con, adOpenKeyset, adLockOptimistic

        If .Fields("due") <= -1 Then
        .AddNew
        !due = txtdue.Text
        End If
        .Fields("due") = rs1.Fields("due") + Val(Me.txtdue)
        .Update
        .Close
         End With

problem is: in dad table it was saved. but in due table it wasn't create new data. it only update the exiting data. so what i need to do for save new data in due table?

e.g. long time after so i forget many thing.

[abelingaw]

Try moving line 29 (End If) before line 33 (End With).

[abu taher]

not work.
I need: when I enter data then the data save in both table and if the data aldready in due table then it will update it.

[abu taher]

now I write this

 With rs1
        .Open "Select * from due", con, adOpenKeyset, adLockOptimistic
        If .Fields("partyname") = " & Me.cmbPartyName & " Then
        .Fields("due") = rs1.Fields("due") + Val(Me.txtdue)

        Else
        .AddNew
        !partyname = cmbPartyName
        !due = txtdue.Text
        End If
         .Update
        .Close

         End With

it save new data. but a new problem: duplicate data was not update. it create new data same name.

[abelingaw]

          With rs1

           .Open "Select * from due", con, adOpenKeyset, adLockOptimistic

            If .Fields("partyname") = " & Me.cmbPartyName & " Then
                    .Fields("due") = rs1.Fields("due") + Val(Me.txtdue)
                    .Update 'updates the existing record on your field
            'try using a msgbox here to confirm if the old record is updated

            Else

                .AddNew
                    !partyname = cmbPartyName
                    !due = txtdue.Text      
                    .Update
            'use a msgbox here to confirm that a new record was created            

            End If

           .Close
        End With

`

Note that on the IF clause, there is no .AddNew method since there is already a record, so it'll only update the current.

On the ELSE clause, there is the .AddNew to create a new record and .Update to save it.

[abu taher]

Sorry it only create new data........ not update

[abelingaw]

From your prev codes, it seems that you are searching for a current record and modify that specific record with a new value.

If that's the case, the you got to modify your SQL statement.

[abu taher]

yes. and if the record not found then entry as a new record.

[abu taher]

just forget all thing.... Now I write this for update and it works.

 With rs1
.Open "Select * from duereport where partyname = '" & Me.cmbPartyName & "'", con, adOpenKeyset, adLockOptimistic
.Fields("due") = rs1.Fields("due") + Val(Me.txtdue)
.Update
.Close
End With

Now tell me how I can input new record if the record not entry before.

[abu taher]

any body can help me.

[abelingaw]

Example:

rs.Open "Select * from tablename where criteria = '" & control name & "'", db, adOpenDynamic, adLockOptimistic

    If rs.EOF = False Then
        MsgBox "Record already exist.", vbExclamation, ""
        Exit Sub
    End If
    rs.Close

    'Your code to save new record follows

Much effective if your criteria would be your PRIMARY KEY since there are some records which have the same names etc.

[abu taher]

Now I write this

With rs1
sSQL = "Select * From duereport Where partyname ='" & cmbPartyName.Text & "'"
rs1.Open sSQL, con, adOpenDynamic, adLockOptimistic
If rs1.EOF = False Then
rs1.Fields("due") = rs1.Fields("due") + Val(Me.txtdue)
rs1.Update
End If

If rs1.EOF = True Then
rs1.Fields("partyname") = cmbPartyName.Text
rs1.Fields("due") = cmbPartyName.Text
End If
   End With

In new record: show this massage: run time error 3201. "Either EOF or BOF is true or the current record has been deleted....." and selected the line: rs1.Fields("partyname") = cmbPartyName.Text
I use movenext but not solve

[abelingaw]

The code I provided would solve your problem, no need to use another IF clause there.

Use the code, then your code for saving (NO IF) follows.

[abu taher]

I write this and solved it

With rs1
sSQL = "Select * From duereport Where partyname ='" & cmbPartyName.Text & "'"
rs1.Open sSQL, con, adOpenDynamic, adLockOptimistic
Do While rs1.EOF = False
rs1.Fields("due").Value = rs1.Fields("due").Value + Val(Me.txtdue)
rs1.Update
rs1.MoveNext
Loop
If rs1.EOF = True And rs1.BOF = True Then
rs1.AddNew
rs1.Fields("partyname").Value = cmbPartyName.Text
rs1.Fields("due").Value = txtdue.Text
rs1.Update
End If
   End With