Code in Excel - adjust for Access

[Guest]

This is our rubberband macro(actually two macros here; one is called FindNext
and is called from the first macro) . We use it in Excel to against the
“tool†data. It “starts†at IAP_Benchmark of 0, and does the code, generating
the factors you see here.
I have played around trying to get this code to work against the same data
Iin an Access database. Any suggestions?

Log_Dist IAP_Benchmark Factor
-15.91
-2.62
-1.40
0.00 0 1.075
1.40
4.39
6.51 7 0.987
8.01
19.68 20 1.012
28.92
36.99
38.46 39 1




On Error GoTo err_Handler

Dim currentBenchmark As Long
Dim newBenchmark As Long
Dim currentFootage As Double
Dim newFootage As Double
Dim deltaBenchmark As Double
Dim deltaFootage As Double
Dim newFactor As Double
Dim factorCell

currentBenchmark = ActiveCell.Value
currentFootage = ActiveCell.Offset(0, -1).Value

Do
'Get new factor cell
factorCell = ActiveCell.Offset(0, 1).Address
ActiveCell.Offset(1).Activate
FindNext
newBenchmark = ActiveCell.Value
newFootage = ActiveCell.Offset(0, -1).Value

deltaBenchmark = newBenchmark - currentBenchmark
deltaFootage = newFootage - currentFootage
If deltaFootage > 0 Then
newFactor = deltaBenchmark / deltaFootage
Else
Range(factorCell).Value = 1
Exit Sub
End If

' Place factor in factor cell
Range(factorCell).Value = newFactor

currentBenchmark = newBenchmark
currentFootage = newFootage

Loop Until ActiveCell.Offset(1, -1).Value = 0

ActivateCell.Offset(0, 1).Value = 1


err_exit:
Exit Sub
err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume err_exit

End Sub


Sub FindNext()

Do While ActiveCell.Offset(0, -1).Value > 0 And ActiveCell.Value = 0
ActiveCell.Offset(1).Select
Loop

End Sub

 

[Douglas J Steele]

While the interface may look similar, Access and Excel are very different
products. Techniques such as what you're using in Excel that rely on
"previous row" don't translate into Access.

Perhaps you can explain what it is you're trying to do in words, and someone
can help you achieve that functionality in Access.