update a column while value in other not changes 1

[jhrs]

Hi, I hope somebody can help me, I want to autonumber a column while the value in other one no changes, if it changes, begin again the autonumbering, I want to do this with DAO.
the result would be something like this:

columnA columnB
A 1
A 2
A 3
A 4
B 1
B 2
B 3
C 1
C 2

 

[dyarwood]

Possibly the following may help

Dim ds As DAO.Database
Dim rs As DAO.Recordset
Set db = daoApp.OpenDatabase _
("Your_database_location_and_name&quot
Set rs = db.OpenRecordset("Table_name&quot
rs.MoveFirst
Do While Not rs.EOF
f=Fields(0) 'Gets current value of entry
rs.MoveNext
g=Fields(0) 'Gets value of next entry
If f<>g Then
i=1
Fields(1)=i
Else
i=i+1
Fields(1)=i
End If
rs.MoveNext
Loop

Hope that helps

dyarwood

 

[dyarwood]

Dim ds As DAO.Database
Dim rs As DAO.Recordset
Set db = daoApp.OpenDatabase _
(&quot;Your_database_location_and_name&quot
Set rs = db.OpenRecordset(&quot;Table_name&quot
rs.MoveFirst
rs.Fields(1)=1
rs.MoveNext
Do While Not rs.EOF
f=rsFields(0) 'Gets current value of entry
rs.MovePrevious
g=Fields(0) 'Gets value of previous entry
rs.MoveNext
If f<>g Then
i=1
rs.Fields(1)=i
Else
i=i+1
rs.Fields(1)=i
End If
rs.MoveNext
Loop


Think this one may be a better version. Let me know how it goes.

dyarwood

 

[jhrs]

Hi again, dyarwood, what i'm really trying to do is this (i'm not native english speaker i hope i'm clear) it is not really autonumbering it is making operations with 2 or 3 columns while other columns keeps the same value and do it again if it changes.

col0 col1 col2 col3 col4
A 0 7 5 2 ...ROW1
A 2 8 3 7 ...ROW2
A 7 5 8 4
...
B 0 5 2 3 ROW-N
B 3 10 8 5 ROW-M


In ROW1 and ROW2 col1=0

I'm trying to do es col1 + col2 - col3 = col4
next row: col1 = col14 from row1

and when col1 changes begin again from cero or other number that already exits there.

i have done the calculations but I can´t make it recognizes the change of value in col1 so it calcultes until eof
hope you answer (really...)

 

[dyarwood]

I think I know how to change the code I sent earlier to do this. The following code should do the calculation you need

Dim ds As DAO.Database
Dim rs As DAO.Recordset
Set db = daoApp.OpenDatabase _
(&quot;Your_database_location_and_name&quot
Set rs = db.OpenRecordset(&quot;Table_name&quot
i=0
rs.MoveFirst
rs.Fields(1)=i 'Sets your col1 in the first row as 0
j=rs.Fields(2) 'Gives j the value of col2
k=rs.Fields(3) 'Gives k the value of col3
l=i+j-k 'The value of l is the one for col4
rs.Fields(4)=l 'Sets the value for col4
i=l 'Sets i equal to the value in col4
rs.MoveNext
Do While Not rs.EOF
f=rs.Fields(0) 'Gets current value of entry of col0
rs.MovePrevious
g=Fields(0) 'Gets value of previous entry
rs.MoveNext
If f<>g Then
i=0
j=rs.Fields(2)
k=rs.Fields(3)
l=i+j-k
rs.Fields(4)=l

Else
i=l
rs.Fields(1)=i
j=rs.Fields(2)
k=rs.Fields(3)
l=i+j-k
rs.Fields(4)=l

End If
rs.MoveNext
Loop


I think this should solve the problem. Let me know if it does not and I will have another look.

dyarwood

 

[jhrs]

Qué paja!!!!
&quot;.moveprevious... .movenext&quot; was the answer
hope thanks really could express how i feel for your help