Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Code to pass through a table and update each row

Discussion in 'Information Technology' started by RichardT, Nov 7, 2005.

  1. RichardT

    RichardT
    Expand Collapse
    Guest

    I would like to write some simple code to run through an Access table and
    update each record with "Odd", "Even", "Odd", "Even"....etc until it reaches
    the end of the file.

    Does anyone have a sample I can use?

    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    Introducing The Spn Code Of Forum Etiquette Announcements Feb 20, 2016
    UK Sikh 'code of silence' on sexual grooming Breaking News Sep 2, 2013
    Can a Dress Code Bar Ethnic and Religious Attire Based on Customer Preferences in the US? Questions and Answers May 3, 2011
    Pacific Code Exempts Religious Drivers (Fiji) Breaking News Feb 16, 2011
    Controversial Gurdwaras 'Breaking the Sikh Code' on Meat and Alcohol Hard Talk Dec 2, 2010

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    You could do it by looping through a recordset, or possibly using an update
    query. If using a recordset, you could use either a DAO or an ADO recordset,
    here's an example using an ADO recordset, it could easily be adapted to use
    DAO ...

    Public Sub OddEven()

    Dim rst As ADODB.Recordset
    Dim boolEven As Boolean

    Set rst = New ADODB.Recordset
    With rst
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "SELECT TestID, TestText FROM tblTest ORDER BY TestID"
    Do Until .EOF
    If boolEven Then
    .Fields("TestText") = "Even"
    boolEven = False
    Else
    .Fields("TestText") = "Odd"
    boolEven = True
    End If
    .Update
    .MoveNext
    Loop
    .Close
    End With

    End Sub

    Here's an example using an Update query. This example assumes that the field
    "TestID" is numeric, sequential, and consecutive ...

    UPDATE tblTest SET tblTest.TestText = IIf([TestID] Mod 2=0,"Even","Odd");

    --
    Brendan Reynolds


    "RichardT" <RichardT@discussions.microsoft.com> wrote in message
    news:29FCBE32-5716-4953-99B2-B8C6F142A4C5@microsoft.com...
    >I would like to write some simple code to run through an Access table and
    > update each record with "Odd", "Even", "Odd", "Even"....etc until it
    > reaches
    > the end of the file.
    >
    > Does anyone have a sample I can use?
    >
    > Thanks
    >
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    If I understood you corectly, then try this, but please back up your data
    first, there is no return

    Function FunctionName()
    Dim MyDb As DAO.Database, MyRec As DAO.Recordset, i As Integer
    Set MyDb = CurrentDb
    'Dont forget to sort the data in appropriate order
    Set MyRec = MyDb.OpenRecordset("Select * From TableName Order By FieldName")
    i = 1
    While Not MyRec.EOF
    MyRec.Edit
    If i = 1 Then
    MyRec!FieldName= "Odd"
    i = 2
    Else
    MyRec!FieldName= "Even"
    i = 1
    End If
    MyRec.Update
    MyRec.MoveNext
    Wend
    End Function
    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "RichardT" wrote:

    > I would like to write some simple code to run through an Access table and
    > update each record with "Odd", "Even", "Odd", "Even"....etc until it reaches
    > the end of the file.
    >
    > Does anyone have a sample I can use?
    >
    > Thanks
    >
     
  5. RichardT

    RichardT
    Expand Collapse
    Guest

    Thanks guys. Very helpful......


    "RichardT" wrote:

    > I would like to write some simple code to run through an Access table and
    > update each record with "Odd", "Even", "Odd", "Even"....etc until it reaches
    > the end of the file.
    >
    > Does anyone have a sample I can use?
    >
    > Thanks
    >
     
  6. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Richard.

    If you have a primary key on the table or a unique index, then you could use
    two queries to do the update for you. It doesn't matter if this column is
    numeric or not or even non-sequential. Try:

    First query:

    SELECT ID,
    IIF((((SELECT COUNT(*)
    FROM tblMyTable AS Cnt
    WHERE Cnt.ID < tblMyTable.ID) + 1) MOD 2) = 1, "Odd", "Even") AS OddOrEven
    INTO tblNumbering
    FROM tblMyTable;

    Second query:

    UPDATE tblMyTable AS T INNER JOIN tblNumbering AS N ON T.ID = N.ID
    SET SomeValue = OddOrEven

    .. . . where the table is named tblMyTable, ID is the primary key or unique
    index, and SomeValue is the name of the field that needs "Even" or "Odd."

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "RichardT" wrote:

    > I would like to write some simple code to run through an Access table and
    > update each record with "Odd", "Even", "Odd", "Even"....etc until it reaches
    > the end of the file.
    >
    > Does anyone have a sample I can use?
    >
    > Thanks
    >
     

Share This Page