How do I get this to work?

[Yasas de Silva]

Hi
I have a MS Access database which has some 3000 records.
This is road segment database. Each record is a road segment with a length
in km. Length is given in a field [LONG]. I want to do a chainage for the
entire data base. So that each record get another field as [Chainage] which
says how far from the starting point the segment sits.
To update this chainage fieild from top to bottom of the db I wrote the
following procedure but it doesn't work. Can anybody help me?
[Start Chaninage] is where user gives any starting chainage if applicable.

Sub ChainageUpdate()

Dim MyDb As Database, MySet As Recordset, X As Double, Y As Double

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("Road Condition")

MySet.MoveFirst
Do Until MySet.EOF
If MySet![Start Chaninage] <> Null Then
MySet.Edit
MySet![Chainage] = MySet![Start Chaninage]
MySet.Update
Else
MySet.MovePrevious
X = MySet![LONG]
Y = MySet![Chainage]
MySet.MoveNext
MySet.Edit
MySet![Chainage] = X + Y
MySet.Update
X = 0
Y = 0
End If
MySet.MoveNext
Loop
MySet.Close
MyDb.Close

End Sub

Regards

Yasas

 

[Tom Lake]

Hi
I have a MS Access database which has some 3000 records.
This is road segment database. Each record is a road segment with a length
in km. Length is given in a field [LONG]. I want to do a chainage for the
entire data base. So that each record get another field as [Chainage]
which
says how far from the starting point the segment sits.
To update this chainage fieild from top to bottom of the db I wrote the
following procedure but it doesn't work. Can anybody help me?
[Start Chaninage] is where user gives any starting chainage if applicable.

Try replacing the If statement with the following:

If Not IsNull(MySet![Start Chaninage]) Then

Tom Lake