Code to pass through a table and update each row

R

RichardT

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
 
B

Brendan Reynolds

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");
 
O

Ofer

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
 
6

'69 Camaro

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.
 
Top