Using the seek method to update a record

[kymjay]

Hi, I am fairly new to vb and I am trying to update a record when I select the save button. It keeps telling me that I can't use the seek method and that the cursor location will not work while the dataset is open. Can someone please tell me how to do it the right way? I am so confused....

Private Sub cmdEdit_Click()
Dim total As Byte

Dim strDate As Variant
strDate = Null

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseServer
rs.Open "tblParts", adoAMS.ConnectionString, adOpenKeyset, adLockOptimistic


rs.Index = "primary key"
rs.Seek 2

rs("part_no").Value = IIf(txtPartNo = "", vbNullString, txtPartNo.Text)
rs("date_rec").Value = IIf(txtDateRec = "", strDate, txtDateRec.Text)
rs("quantity").Value = IIf(txtQty = "", Null, txtQty.Text)
rs("description").Value = IIf(txtDescription = "", vbNullString, txtDescription.Text)
rs("units").Value = IIf(txtUnits = "", vbNullString, txtUnits.Text)
rs("application").Value = IIf(txtApplication = "", vbNullString, txtApplication.Text)
rs("quote_source").Value = IIf(txtQuoteSource = "", vbNullString, txtQuoteSource.Text)
rs("list_price").Value = IIf(txtListPrice = "", Null, txtListPrice.Text)
rs("ata_code").Value = IIf(txtATA = "", vbNullString, txtATA.Text)

If ckSerialPart.Value = 1 Then
rs("serial_part").Value = "Yes"
Else
rs("serial_part").Value = "No"
End If

rs("manufacturer_part_no").Value = IIf(txtManufacturer = "", vbNullString, txtManufacturer.Text)
rs("nha").Value = IIf(txtNHA = "", vbNullString, txtNHA.Text)
rs("min_order_qty").Value = IIf(txtMinOrder = "", Null, txtMinOrder.Text)
rs("min_new_qty").Value = IIf(txtMinNew = "", Null, txtMinNew.Text)
rs("min_onhand_qty").Value = IIf(txtOnhand = "", Null, txtOnhand.Text)
rs("min_other_qty").Value = IIf(txtOther = "", Null, txtOther.Text)
rs("comments").Value = IIf(txtComments = "", vbNullString, txtComments.Text)
rs("condition_code").Value = IIf(txtCondition = "", vbNullString, txtCondition.Text)
rs("qty_onhand").Value = IIf(txtQtyOnhand = "", Null, txtQtyOnhand.Text)

If Val(txtQty.Text) > 0 Then
total = Abs(Val(txtQty.Text)) + Abs(Val(txtQtyOnhand.Text))
Else
total = Abs(Val(txtQtyOnhand.Text)) - Abs(Val(txtQty.Text))
End If
rs("qty_onhand").Value = total

rs("unit_cost").Value = IIf(txtUnitCost = "", Null, txtUnitCost.Text)
rs("stock_location").Value = IIf(txtLocation = "", vbNullString, txtLocation.Text)
rs("consignment_code").Value = IIf(txtCode = "", vbNullString, txtCode.Text)
rs("selling_price").Value = IIf(txtSellingPrice = "", Null, txtSellingPrice.Text)
rs("serial_number").Value = IIf(txtSerial = "", vbNullString, txtSerial.Text)
rs("vendor").Value = IIf(txtVendor = "", vbNullString, txtVendor.Text)
rs("due_date").Value = IIf(txtDueDate = "", strDate, txtDueDate.Text)
rs("shelf").Value = IIf(txtShelf = "", vbNullString, txtShelf.Text)
rs("purchase_order").Value = IIf(txtPurchaseOrder = "", vbNullString, txtPurchaseOrder.Text)

If ckCore.Value = 1 Then
rs("core_part").Value = "Yes"
Else
rs("core_part").Value = "No"
End If


rs.Update
rs.Close

Set rs = Nothing

MsgBox "The current record has been saved successfully"

ClearFields

txtPartNo.SetFocus

 

[bjd4jc]

use the .Find Method with ADO

rs.Find "FieldName=" & YourValue

If not rs.EOF Then
    'Record has been found
End If

A better solution would be to use ADO Parameters with an UPDATE statment. I try to avoid updating a record via a recordset when at all possible. You can do a keyword search on this forum for examples on how to use ADO parameters. I believe there might even be a FAQ or two on the subject.