Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    45

    Unanswered: change the value of field with ADO recordset

    Hallo,
    I have a yes/no field and I would like to change the value of it from no(0) to yes (-1) through looping the recordset. I'm using this code but it doesn't work:

    Count = 0
    Do Until Count = 100
    rs_exp_org.AddNew
    'copy the value from a recordset to the other
    rs_exp_org!Org_ID = rs_query!Org_ID
    'here I try to modify the value of the field (curently = 0) to -1
    rs_query!Org_Exported = -1
    rs_query.MoveNext
    rs_exp_org.Update
    Count = Count + 1
    Loop

    Does someone knows how to do it? Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    An ADO Example:

    Code:
    Function ReassignItem(ByVal NewItemID As Long) As Integer
        On Error GoTo Err_RI
        
        Dim TmpItemID As Long
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        TmpItemID = Me![RequestedItems].Form.ItemIDTxt.Value
        SQLString = "SELECT * FROM " & AvailableItemsTbl & " WHERE (ItemID=" & NewItemID & ");"
        MyRecSet.Open SQLString, FormsConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RequestedItemsTbl & " WHERE (ItemID=" & Me![RequestedItems].Form.ItemIDTxt.Value & ");"
            TrgRecSet.Open SQLString, FormsConnect
            If TrgRecSet.BOF = False Then
                ' Item ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Item Code
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(3).Value & ""
                ' Common Code
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(1).Value & ""
                ' Description
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(2).Value & ""
                ' Sequence #
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(4).Value
                ' UOM ID
                TrgRecSet.Fields(14).Value = MyRecSet.Fields(5).Value
                ' UOM Code
                TrgRecSet.Fields(15).Value = MyRecSet.Fields(6).Value & ""
                ' Unit Cost
                TrgRecSet.Fields(8).Value = MyRecSet.Fields(7).Value
                ' Cost Book
                TrgRecSet.Fields(10).Value = TrgRecSet.Fields(8).Value * TrgRecSet.Fields(6).Value
                ' HazardID
                TrgRecSet.Fields(16).Value = MyRecSet.Fields(8).Value
                ' FID #
                TrgRecSet.Fields(17).Value = MyRecSet.Fields(9).Value & ""
                ' Tax Code
                TrgRecSet.Fields(18).Value = True
                ' Account Classification
                 TrgRecSet.Fields(22).Value = MyRecSet.Fields(11).Value
               TrgRecSet.Update
            End If
            TrgRecSet.Close
        End If
        MyRecSet.Close
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •