Simple Loop

  • Thread starter Thread starter Tam Lee
  • Start date Start date
T

Tam Lee

this should be a simple loop? and i've done more complicated ones, but it's
been awhile.
I have a table with 3 fields: Assign, Compute, Name (which won't be affected)
I want to loop through if Assign is 1 then Compute should be 1.
next record, if nothing in Assign compute should be 2, next record,
if nothing in Assign, Compute should be 3 etc...
so, i end up with:
Assign Compute
1 1
2
3
4
1 1
2
3
 
You could cycle through a recordset and update necessary fields....


Dim rs as DAO.Recordset
Dim lngCount as Long

Set rs = CurrentDB.OpenRecordset("tablename")
If Not(rs.Recordcount = 0) Then
rs.MoveFirst
While Not rs.EOF
lngCount = lngCount + 1
Select Case rs.Fields("Assign")
Case 1
With rs
.edit
.Fields("Compute") = 1
.update
End With
lngCount = 0
Case Else
with rs
.edit
.Fields("Compute") = lngCount
.update
End With
End Select
rs.MoveNext
Wend
End if

rs.Close
Set rs = Nothing



hth
-jack
 
try:

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = CurrentDb.OpenRecordset("tableData")
lngCount = 1
Do While rst.EOF = False
If IsNull(rst!Assign) = False Then
lngCount = 1
End If
rst.Edit
rst!Compute = lngCount
rst.Update
lngCount = lngCount + 1
rst.MoveNext
Loop
rst.Close
 
Correction:
Select Case rs.Fields("Assign")
Click to expand...

should be

Select Case Nz(rs.Fields("Assign"), 0)

Assuming that it is a number datatype. This will prevent errors from trying
read a Null value.

This seems like a one-to-many relationship with one table instead of two....
 
I hate to argue with Jack and Albert, but both of them opened a recordset
based on the table. Doing that, you cannot be sure of the order in which the
recordset will be returned.

Unless there's a field to indicate the order in which the records should be
returned, your data will not be reliable. There is no way to be sure that
the records will be processed in the order in which you think they should be
processed.
 
Back
Top