Can anyone tell me.How should i update. i am trying to to a new record if the Supplier_id Doesn't exist, Then i need to do a Select Statement.if that Supplier_id is in the database already,then i want to update method.i havewritten a code .Here is the code what i have written.when i update existing data.i got error fields cannot
be updated.

Private Sub Command2_Click()
Dim success As Boolean
Set con = New ADODB.Connection
success = OpenConnection(con)
   If success = False Then
      MsgBox ("Cannot Open Connection")
   End If
X = MsgBox("Do you want to save ", vbYesNo, "Message")
Set rs = New ADODB.Recordset
rs.Open "select * from supplier where supplier_id=" & Text1, con, adOpenDynamic, adLockOptimistic
'rs.Open "select * from employee_record where employee_id=" & Text1.Text, con, adOpenDynamic, adLockOptimistic
rs.Fields("Supplier_id") = Val(Text1.Text)
rs.Fields("Supplier_name") = Text2.Text
rs.Fields("contact_person") = Text3.Text
rs.Fields("contact_no") = Text4.Text
rs.Fields("office_address") = Text6.Text
rs.Fields("emails") = Text7.Text
rs.Fields("website") = Text8.Text
rs.Fields("Fax_no") = Text5.Text
rs.Fields("item_type") = Combo1.Text
rs.Update
rs.Close
End Sub

addnew or edit method of the recordset must be called before the update method.

try as the following code

rs.addnew
rs.Fields("Supplier_id") = Val(Text1.Text)
rs.Fields("Supplier_name") = Text2.Text
rs.Fields("contact_person") = Text3.Text
rs.Fields("contact_no") = Text4.Text
rs.Fields("office_address") = Text6.Text
rs.Fields("emails") = Text7.Text
rs.Fields("website") = Text8.Text
rs.Fields("Fax_no") = Text5.Text
rs.Fields("item_type") = Combo1.Text
rs.Update

Still getting same error .Field cannot be updated.Here is the code
what i have written.Kindly find the attachment also.error generating
from Bold line.when try to save.

Private Sub Command2_Click()
Dim success As Boolean
Set con = New ADODB.Connection
success = OpenConnection(con)
   If success = False Then
      MsgBox ("Cannot Open Connection")
       Exit Sub
   End If
Set rs = New ADODB.Recordset
rs.Open "select * from supplier where supplier_id=" & Text1.Text, con, adOpenDynamic, adLockOptimistic
If rs.BOF And rs.EOF Then rs.AddNew
[B] rs.Fields("Supplier_id") = Text1.Text[/B]
 rs.Fields("Supplier_name") = Text2.Text
 rs.Fields("contact_person") = Text3.Text
 rs.Fields("contact_no") = Text4.Text
 rs.Fields("office_address") = Text6.Text
 rs.Fields("emails") = Text7.Text
 rs.Fields("website") = Text8.Text
 rs.Fields("Fax_no") = Text5.Text
 rs.Fields("item_type") = Combo1.Text
 rs.Update
 rs.Close
End Sub

do as what Debasisdas told you, after you open the recordset, then you use rs.addnew..take out the If statement...

before you open the recordset put some error trapping whether the user input some data ..or else you'll add an empty data to your database...

This article has been dead for over six months. Start a new discussion instead.