How to update the record to database through recordset?

11-09-2004, 01:40 AM
Hello to everybody

Wel say my table is having n number of records, now i want to update 5th reord, which is displayed on my form

Well then i click the update button for updating the record. Following code behind the screen

Private Sub cmdSave_Click()
Dim rs As New ADODB.Recordset
If (ValidateData) Then
rs.Open "EMD_Master", GeoCon, adOpenDynamic, adLockOptimistic
With rs
If Add = True Then
.Fields(1) = txtEMDRefNo.Text
.Fields(2) = cmbTenderRef.Text
.Fields(3) = GetBankRefNo
.Fields(4) = Val(txtamt.Text)
MsgBox "Record Added Successfully"
End If
If Edit = True Then
.Fields(1) = cmbEmdRefNo.Text
.Fields(2) = cmbTenderRef.Text
.Fields(3) = GetBankRefNo
.Fields(4) = Val(txtamt.Text)
MsgBox "Record Updated Successfully"
End If
End With
End If
End Sub

Well addition is going on fine but the updation just changes the first record in the query.

What has to be done.

Shall i make a query to select the particluar record, which i want to update and then rest update portion as in Edit one given above


11-09-2004, 05:48 AM
I dont recomment using your recordset in doing DML Commands (Insert, Delete and Update).
You have your GeoCon right, this is an ADO Connection Object. Use this instead in dealing with DML Commands.

To start with use the BeginTrans Method, then the Execute method and finally use the CommitTrans Method if you want to commit your transaction or the RollbackTrans if you are just kidding. :D

GeoCon.Execute "UPDATE MYTABLE SET Field1 = 0 WHERE Field2 = 'a'"
GeoCon.Execute "DELETE FROM MYTABLE WHERE Field1 = 10"
GeoCon.Execute "INSERT INTO MYTABLE (Field1,Field2) VALUES (100,'B')"

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum