I wrote some Access Basic for Access 2 several years ago - it worked fine
but I'm now (finally!) having to upgrade to Access XP. The queries updated
fine, but my Access Basic seems to have to be rewritten. I've been puzzling
for hours over a calculation which worked fine before ..

Formulas used to look like

MyTable!TrstGrwth = Log(MyTable!TrustVal / Val1)

with the exclamation mark between the table and the field reference. This
doesn't produce any error when it is run, but there is no updating of the
table either!

Any leads on how to update this to VBA would be very much appreciated.

Full code as follows ...


Dim MyWorkspace As WorkSpace, MyDatabase As Database, MyTable As Recordset,
Val1 As Single, Val2 As Integer, Val3 As Single
    Set MyWorkspace = DBEngine.Workspaces(0) ' Get default workspace.
    Set MyDatabase = MyWorkspace.Databases(0) ' Get current database.
    Set MyTable = MyDatabase.OpenRecordset("BaseData", DB_OPEN_TABLE) '
Open table.
    MyWorkspace.BeginTrans ' Start of transaction.
    Val2 = 0
    Do Until MyTable.EOF
        Val2 = Val2 + 1
        If Val2 > 1 Then
            MyTable.Edit ' Enable editing.
            MyTable!TrstGrwth = Log(MyTable!TrustVal / Val1)
            MyTable!FTSEGrwth = Log(MyTable!FTSE_100 / Val3)
            MyTable!XSq = MyTable!FTSEGrwth ^ 2
            MyTable!XY = MyTable!FTSEGrwth * MyTable!TrstGrwth
            MyTable!TrackError = MyTable!TrstGrwth - MyTable!FTSEGrwth
            MyTable.Update ' Save changes.
        Else
            MyTable.Edit ' Enable editing.
            MyTable!TrstGrwth = ""
            MyTable!FTSEGrwth = ""
            MyTable!XSq = ""
            MyTable!XY = ""
            MyTable!TrackError = ""
            MyTable.Update ' Save changes.

        End If
        Val1 = MyTable!TrustVal
        Val3 = MyTable!FTSE_100
        MyTable.MoveNext
    Loop
    MyWorkspace.CommitTrans ' Commit changes.
    MyTable.Close ' Close table.
End Function



Thanks

Andrew

I seem to have resolved this - the programming was fine!