Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Saving and updating a record problem

I created a program using vb and access, main data is enter through one form (main form) and this has a link to another table (details table) which is suppose to be enter through another form which open through the main form whenever details of the data is required to enter. But after entering details data for various item, each field gets replaced by the latest entry. What could be the problem, anyone help me.

I use the code below :

opening the record :

Set rsDetails = New Recordset
rsDetails.Open "SELECT * FROM tblDetails where Reg_No='" & frmSociety.txtFields(2) & "' ", db, adOpenStatic, adLockOptimistic

****************
For saving the record :

Dim str
str = "INSERT into tblDetails values('" & txtReg_No.Text & "'," & "'" & txtAims.Text & "','" & txtOB.Text & "'," & "'" & txtMembers.Text & "'," & "'" & txtRemarks.Text & "'," & "'" & txtName.Text & "')"
db.Execute (str)
MsgBox "Society Information save successfully"
db.Close
***************
For updating the record

Update -

Dim str
str = "update tblDetails set Firm_Name = '" & txtName.Text & "', Aims='" & txtAims.Text & "', OB=' " & txtOB.Text & "', Members= '" & txtMembers.Text & "', Remarks= '" & txtRemarks.Text & "' "
db.Execute (str)
MsgBox "Society Information updated successfully!!"

db.Close

Any suggestion or help would be highly appreciated.

Zela

Comments

  • SharbellMouessSharbellMouess Member Posts: 7
    Hi Zela,
    I think the problem lies in the update SQL statement. You are updating a record in the table without giving a condition to specifying which record to update, as a result your query updates all the existing records in the table and give all of them the new values you set. So, to fix the problem, add a 'Where' clause to your SQL statement to specify a particular record using the primary keys.
    For example:
    str = "update tblDetails set Firm_Name = '" & txtName.Text & "', Aims='" & txtAims.Text & "', OB=' " & txtOB.Text & "', Members= '" & txtMembers.Text & "', Remarks= '" & txtRemarks.Text & "' Where PKeyID=" & txtFirmID.Text

    Good luck
  • honest_dudehonest_dude Member Posts: 93

    Thanks a lot buddy, i can make 'em work now with your suggestion and help now.

    Zela
Sign In or Register to comment.